VBA Copy Paste between workbooks identical sheets, Open via dialog box

Question

I've seen answers to this questions that are variations of what I'm looking for but when I attempt to modify the code to my situation, I keep getting error messages.

I have two workbooks, a Master "Template" and a monthly report called "YTDJune2015". Each has 15 sheets which are the same for both, and I want to copy data from the monthly report into the Template which has formulas to calculate additional data for each of the 15 unique Sheets. I want to use the "Open" dialog box as a way to choose the source workbook because the report is updated monthly. I keep getting an error "Object Required" after I select the source document in the open dialog box, and can't figure out how to allow it to open the source workbook. The range part of the code may be incorrect as well but I haven't been able to get past the opening of the source document so I can't check it. I want it to loop through every sheet in the source workbook and copy the same range, then paste into the destination workbook. So far the code I have is:

Sub UpdateWorkbook()

  Dim wbSource As Workbook, wbDest As Workbook
  Dim ws As Worksheet, rng As Range

  Application.ScreenUpdating = False

  Set wbSource = Application.GetOpenFilename(FileFilter:="Excel Filter (*.xlsx), *.Xlsx", Title:="Open File(s)", MultiSelect:=False)
  Set wbDest = Workbooks.Open("Template.xlsm")

  For Each ws In wbSource.Sheets

  For Each rng In ws.Range("C8:AB117").Areas

        wbDest.Sheets(ws.Name).Range(rng.Address).Value = rng.Value

        Next rng

  Next ws

  wbSource.Close SaveChanges:=False

  Application.ScreenUpdating = True

End Sub

From msdn Displays the standard Open dialog box and gets a file name from the user without actually opening any files. I'd suggest FSO. — findwindow
Is there a way to use something similar to open the file in order to extract data? Basically I want to avoid using the filepath to open so the file can be sent to multiple users who will always have different filepaths on their computers. — A.Morrissey
Try this instead: Dim sFile as String | sFile = Application.GetOpenFilename ... | Set wbSource = Workbooks.Open(sFile) pipes represent line breaks in code. And what is different about the filepaths for each user? It is just the drive letter? — Scott Holtzman
It may be different if they don't have the file saved in the same folder as I do on my computer. So I want them to basically be able to create their own file path that is correct on their computer. And that code went through but there was no data copied and pasted. I'm not sure if I actually put the copy and paste command in there (I got this code from another question on the site), so how would I insert it so it copies and pastes from all 15 worksheets? Maybe a loop function? — A.Morrissey

Andrew Wynn Andrew Wynn · Accepted Answer · 2015-11-30T23:59:03

The commenters above are correct about GetOpenFilename, it doesnt return a Workbook object, it returns the string that is the path to the file that you selected, hence "Object Required".

To fix this, I would recommend Dimming a string to contain the file name, then setting the workbook object to the new variable name, as is suggested in the comments.

The thing I'd like to add is the last part, you can use paste special values in the code:

Sub UpdateWorkbook()

  Dim wbSource As Workbook, wbDest As Workbook
  Dim ws As Worksheet, rng As Range
  Dim sFile as String

  Application.ScreenUpdating = False

  sFile = Application.GetOpenFilename(FileFilter:="Excel Filter (*.xlsx), *.Xlsx", Title:="Open File(s)", MultiSelect:=False)
  Set wbSource = Workbooks.Open(sFile)
  Set wbDest = Workbooks.Open("Template.xlsm")

  For Each ws In wbSource.Sheets

      For Each rng In ws.Range("C8:AB117").Areas

          rng.copy
          wbDest.Sheets(ws.Name).Range(rng.Address).PasteSpecial xlPasteValues

      Next rng

  Next ws
  Application.CutCopyMode = False
  wbSource.Close SaveChanges:=False

  Application.ScreenUpdating = True

End Sub

However, you may want to change xlPasteValues to xlPasteValuesAndNumberFormats

VBA Copy Paste between workbooks identical sheets, Open via dialog box

2 Answers