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

[KiwiRyu]

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>