How do I copy data from multiple worksheets on one workbook to specific worksheets on a different workbook

KiwiRyu

New Member
Joined
Jan 31, 2013
Messages
1
Hi All, </SPAN></SPAN>

I have 2 workbooks “Source” and “Destination”. “Source” contains 4 worksheets. I need to copy the data contained on each of those worksheets to “Destination”. Once the data is in “Destination”, it will be mapped to other worksheets within “Destination”. I need a piece of code that will allow me to start in “Destination”, navigate to “Source”, copy the data on each of the 4 worksheets in “Source”, and then paste it to 4 worksheets in “Destination”. To further complicate matters, the name and location of both “Source” and “Destination” will change every time I run the code.</SPAN></SPAN>

I have the following which enables me to navigate to “Source”, and copy the 4 worksheets it contains, but it inserts those worksheets as NEW worksheets in “Destination”, so my mapping does not work, and it only seems to work if I note a specific name for “Destination”, rather than it being the workbook I have run the code from.</SPAN></SPAN>

Can anyone help?</SPAN></SPAN>

Sub Button20_Click()</SPAN></SPAN>

Dim wkbCrntWorkBook As Workbook</SPAN></SPAN>
Dim wkbSourceBook As Workbook</SPAN></SPAN>

Dim rngSourceRange As Range</SPAN></SPAN>
Dim rngDestination As Range</SPAN></SPAN>

Set wkbCrntWorkBook = ActiveWorkbook</SPAN></SPAN>

With Application.FileDialog(msoFileDialogOpen)</SPAN></SPAN>
.Filters.Clear</SPAN></SPAN>
.Filters.Add "Excel 2002-03", "*.xls", 1</SPAN></SPAN>
.Filters.Add "Excel 2007", "*.xlsx; *.xlsm; *.xlsa", 2</SPAN></SPAN>
.AllowMultiSelect = False</SPAN></SPAN>
.Show</SPAN></SPAN>

If .SelectedItems.Count > 0 Then</SPAN></SPAN>
Workbooks.Open .SelectedItems(1)</SPAN></SPAN>

Sheets(Array("Sheet 1", " Sheet 2", " Sheet 3", " Sheet 4")).Select</SPAN></SPAN>
Sheets(Array("Sheet 1", " Sheet 2", " Sheet 3", " Sheet 4")).Copy Before:= _</SPAN></SPAN>
Workbooks("Destination.xlsm").Sheets(1)</SPAN></SPAN>
End If</SPAN></SPAN>
End With</SPAN></SPAN>

End Sub</SPAN></SPAN>
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Watch MrExcel Video

Forum statistics

Threads
1,129,445
Messages
5,636,318
Members
416,912
Latest member
danluk12

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top