Open Copy Close

  • Thread starter Thread starter ianripping
  • Start date Start date
I

ianripping

I have these two Macro's at present: -

Sub ListFiles()
F = Dir("C:\*.XLS")
Do While Len(F) > 0
ActiveCell.Formula = F
ActiveCell.Offset(1, 0).Select
F = Dir()
Loop
End Sub

Sub CopyInfo()
Workbooks.Open Filename:=Range("A1").Value
Range("A:B").Copy Destination:=Workbooks("Book1").Range("B:C")
Workbooks.Close Filename:=Range("A1").Value
MsgBox ("Completed Copying")

End Sub

As you can see I want the Macro to open the file name in A1 then copy
the information, then paste it into Book1.xls, then cose the Filename
in A1.

So far it opens the workbook, but won't close it. I dont think I can
use the close command here, any ideas?
 
Hi
Try this

Sub CopyInfo()
Dim wbOne as Workbook
Set wbOne = Workbooks.Open Filename:=Range("A1").Value
Range("A:B").Copy Destination:=Workbooks("Book1").Range("B:C")
wbOne.Close
MsgBox ("Completed Copying")
End Sub

Also ActiveWorkbook.close should work
 
Sorry, stupid mistake on my part!!!
Set wbOne = Workbooks.Open(Filename:=Range("A1").Value)
should sort it
;-)
 
OK I've got it its like this

Workbooks.Open Filename:=Range("A1").Value
Range("A:A").Select
Selection.Copy
ActiveWorkbook.Close SaveChanges:=False
Windows("Book1.xls").Activate
Range("C:C").Select
ActiveSheet.Paste
MsgBox ("Completed Copying")

BUT! Excel asks do you want to copy this large amount of info blah
blah

Then do you want to paste this info even though the target cell isn't
as big blah blah


Is there a way to turn off these prompts?
 
I haven't been able to recreate your problem but you could try inserting
this line before the copy command
Application.DisplayAlerts = False
Set back to True at the end of the routine
;-)
 
Back
Top