Dim wkbkdestination As Workbook
Dim destsheet As Worksheet
For Each ThisWorkSheet In wkbkorigin.Worksheets
'this throws subscript out of range if there is not a sheet in the destination
'workbook that has the same name as the current sheet in the origin workbook.
Set destsheet = wkbkdestination.Worksheets(ThisWorkSheet.Name)
Basically, I iterate through every sheet in the origin workbook by looping through it, then I set the destsheet property in the destination workbook to the sheet with the same name as the iteration's current iteration.
How do I find out if that sheet is real? the following:
If wkbkdestination.Worksheets(ThisWorkSheet.Name) Then