Macro to copy from different (dynamic) workbooks

[igeron]

Hi All,

I have an excel template which have to be filled with the data from another (Last year's) workbook. I have a macro which copy data from the excel sheet, only if I state the name and adress of the last year's excel file within the macro.

My question is;
can I create a macro so that in the beggining of the macro run, it will ask me the resource/last year file and I can browse it. Then the macro will pick all the data from the chousen file.

Thanks
Igal

 

[VoG]

Try like this

Sub SelectFile()
Dim sFile As Variant
sFile = Application.GetOpenFilename("Files (*.xls),*.xls", , "Select A File")
If sFile <> "False" Then Workbooks.Open (sFile)
End Sub

 

[igeron]

Thank you very much for the answer,

However I managed to fail on the copying part as well

The code should be as follow, but failing

Sub LYDATA()
Application.ScreenUpdating = False
 
Dim sFile As Variant, wbs As Workbook, wbc As Workbook
Set wbc = ActiveWorkbook
sFile = Application.GetOpenFilename("Files (*.xls),*.xls", , "Select A File")
If sFile = "False" Then Exit Sub
Set wbs = Workbooks.Open(sFile)
 
wbc.Worksheets("sheet1").Range("A1") = wbs.Worksheets("sheet2").Range("c2").value

Application.ScreenUpdating = True
        
End Sub

Thanks
Igal

 

[VoG]

This is a very similar problem. Does it help?

http://www.mrexcel.com/forum/showthread.php?t=504131

 

[igeron]

I changed the macro into this:

However When I run, I receive Run-time error '9': Subscript out of range

Sub LYSDATA1() 
Dim sFile As Variant, wb As Workbook
sFile = Application.GetOpenFilename("Files (*.xls),*.xls", , "Select A File")
If sFile = "False" Then Exit Sub
Set wb = Workbooks.Open(sFile)
wb.Worksheets("Sheet1").Range("A1:A10").Copy Destination:=ThisWorkbook.Worksheets("DATA").Range("A1")
wb.Close savechanges:=False 
'here is the error
 
End Sub

 

[VoG]

The error is on

wb.Close savechanges:=False

?

 

[igeron]

on this one, sorry

wb.Worksheets("Sheet1").Range("A1:A10").Copy Destination:=ThisWorkbook.Worksheets("DATA").Range("A1")

 

[VoG]

That means that either or both the workbook that you opened doesn't have a Sheet1 or the workbook with the code doesn't have a sheet DATA.

 

[igeron]

Actually I have both

This did the trick

Sub LYSDATA1()
Application.ScreenUpdating = False
 
Dim sFile As Variant, wb As Workbook, wbc As Workbook
Set wbc = Application.ActiveWorkbook
sFile = Application.GetOpenFilename("Files (*.xls),*.xls", , "Select A File")
If sFile = "False" Then Exit Sub
Set wb = Workbooks.Open(sFile)
wb.Sheets("Sheet1").Select
Range("A1:A10").Copy
wbc.Activate
Sheets("Data").Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
wb.Close savechanges:=False
wbc.Activate
Application.ScreenUpdating = True
End Sub

Thanks
Igal