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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Watch MrExcel Video

Forum statistics

Threads
1,130,426
Messages
5,642,055
Members
417,252
Latest member
selbysam

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