Macro to copy a range of data from one sheet to another workbook

[setanta]

Hi, I am new to VBA and was wondering if someone could help me out please? I have a request to copy a range of data from one sheet in the source workbook to a sheet in the target workbook and save it down with a date stamp and version number. I've looked online but can't get any of the VBA codes to work for me without errors such as "subscript out of range" and runtime 400 errors. Please help. Thanks a lot. Here is an example of what I would like, a 3G connection won't allow me to upload excel workbooks as examples :-/.SourceBook.xls
tab name:Source
Data1 Data2 Data3 Data4
A2 B2 C2 D2
A3 B3 C3 D3
A4 B4 C4 D4
A5 B5 C5 D5
A6 B6 C6 D6
A7 B7 C7 D7
A8 B8 C8 D8
A9 B9 C9 D9

BEFORE RUNNING MACRO
TargetBook.xls
tab name:Target
Data1 Data2 Data3 Data4
AFTER RUNNING MACRO
TargetBook.xls
tab name:Target
Data1 Data2 Data3 Data4
A2 B2 C2 D2
A3 B3 C3 D3
A4 B4 C4 D4
A5 B5 C5 D5
A6 B6 C6 D6
A7 B7 C7 D7
A8 B8 C8 D8
A9 B9 C9 D9

[patel]

Sub Open_File_CopyRange_to_Sheet()
SelFile = "C:\test\TargetBook.xls"
Set Rng = Sheets("Source").Range("A2:D9")
Workbooks.Open Filename:=SelFile
Set wb1 = ActiveWorkbook
Rng.Copy
wb1.Sheets("Target").Range("A2").PasteSpecial
'wb1.Close
End Sub

[setanta]

[Sub Open_File_CopyRange_to_Sheet()
SelFile = "C:\Users\joebloggs\Documents\2000.Test\TargetBook.xls"
Set Rng = Sheets("Source").Range("A29")
Workbooks.Open Filename:=SelFile
Set wb1 = ActiveWorkbook
Rng.Copy
wb1.Sheets("Target").Range("A2").PasteSpecial
'wb1.Close
End Sub[/CODE][/QUOTE]
Thanks Patel for some reason I can't get your code to work for me, any ideas please? Also I want to copy the data to a new workbook named target.

[setanta]

Please see a link to the file https://drive.google.com/folderview?...ng.Essentially I want to provide links in the code to copy from one to another i.e. source=sourcebook.xls, target=targetbook.xls

[patel]

Sub Open_File_CopyRange_to_Sheet()
SelFile = "C:\Users\michfitzgerald\Documents\2000.Test\SourceBook.xlsx"
Set wb1 = ActiveWorkbook
Workbooks.Open Filename:=SelFile
Sheets("Source").Range("A2:D9").Copy
wb1.Sheets("Target").Range("A2").PasteSpecial
End Sub

[setanta]

Thanks for the reply Patel, however this macro does not allow a source and a target in 2 different workbooks, please advise.

[patel]

i'm sorry, but I can not understand your goal

[setanta]

Hi Patel, sorry maybe my explanation wasn't that clear. I've updated the attached excel files. Essentially I don't want to copy data between sheets. I want to copy data between the source and target excel workbooks. I have removed the target tab from the sourcebook.xls and the source tab from the targetbook.xls. I have attached a ppt slide to the google drive to show what I mean. https://drive.google.com/#folders/0B...WhFcy1mUDB1Smc. Thanks

[Simi]

you can do this by adding another workbook variable in the code.
You can look at this other thread that Ken Puls help'd me out with.
http://www.excelguru.ca/forums/showt...ht=consolidate

in the example code above simply add the following.

Sub Open_File_CopyRange_to_Sheet()
SelFile = "C:\Users\michfitzgerald\Documents\2000.Test\SourceBook.xlsx"
Set wb1 = ActiveWorkbook

'*** add the line below
set wb2 = workbooks.open("your target file path")
'***

 Workbooks.Open Filename:=SelFileSheets("Source").Range("A2:D9").Copy

'*** change this to wb2
wb2.Sheets("Target").Range("A2").PasteSpecial
'***
End Sub

I'm not sure how this will work with opening 2 workbooks without closing one.

[patel]

the code I attached is good, but you have to paste it only in TargetBook and in a standard module, not in ThisWorkbook module, see attached