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

setanta

New member
Joined
May 7, 2013
Messages
11
Reaction score
0
Points
0
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

New member
Joined
Feb 20, 2014
Messages
60
Reaction score
0
Points
0
Location
Italy
Excel Version(s)
2010
Code:
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

New member
Joined
May 7, 2013
Messages
11
Reaction score
0
Points
0
[Sub Open_File_CopyRange_to_Sheet()
SelFile = "C:\Users\joebloggs\Documents\2000.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[/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.
 

patel

New member
Joined
Feb 20, 2014
Messages
60
Reaction score
0
Points
0
Location
Italy
Excel Version(s)
2010
Code:
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

New member
Joined
May 7, 2013
Messages
11
Reaction score
0
Points
0
Thanks for the reply Patel, however this macro does not allow a source and a target in 2 different workbooks, please advise.
 

patel

New member
Joined
Feb 20, 2014
Messages
60
Reaction score
0
Points
0
Location
Italy
Excel Version(s)
2010
i'm sorry, but I can not understand your goal
 

setanta

New member
Joined
May 7, 2013
Messages
11
Reaction score
0
Points
0
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/0B2t8pupBEXg2WWhFcy1mUDB1Smc. Thanks
 

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
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/show...from-multiple-workbooks&highlight=consolidate

in the example code above simply add the following.


Code:
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.
 
Last edited:

patel

New member
Joined
Feb 20, 2014
Messages
60
Reaction score
0
Points
0
Location
Italy
Excel Version(s)
2010
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
 

Attachments

  • TargetBook.xlsm
    15.2 KB · Views: 33

setanta

New member
Joined
May 7, 2013
Messages
11
Reaction score
0
Points
0
Excellent, thanks Simi and Patel. If I wanted to update the code so that it allowed me to enter the filename for the SourceBook instead of hard coding this eg in a pop up text box or something?, how would I go about doing this? Then save the workbook down with the same file name and put the date after it. Also once the data is posted would it possible to remove the duplicates based on column A for example. Many thanks in advance
 
Last edited:

setanta

New member
Joined
May 7, 2013
Messages
11
Reaction score
0
Points
0
Here is some code that works for me opening the filepath so if someone could lead me in the right direction to combine this code along with the copy and paste to another workbook it would be much appreciated. Dim path As String

With Application.FileDialog(msoFileDialogOpen)
.Show
If .SelectedItems.Count = 1 Then
path = .SelectedItems(1)
End If
End With

If path <> "" Then
Open path For Output As #n
End If
 
Top