Hello everyone,
I'm sure this is a very basic question, but I'm new at this. I'm trying to prompt the user to select an Excel workbook, then copy and paste a range of cells from that workbook into the active one. This is my attempt below:
Sub CommandButton1_Click()
Dim WS As Worksheet
Dim wb As Workbook, wb2 As Workbook
Dim vFile As Variant
'Set source workbook
Set wb = ActiveWorkbook
'Open the target workbook
vFile = Application.GetOpenFilename("Excel-files,*.xls*", _
1, "Select One File To Open", , False)
'if the user didn't select a file, exit sub
If TypeName(vFile) = "Boolean" Then Exit Sub
Workbooks.Open vFile
'Set selectedworkbook
Set wb2 = ActiveWorkbook
'Select cells to copy
wb2.Worksheets("Output").Range("A10:C15").Select
Selection.Copy
'Go back to original workbook you want to paste into
wb.Activate
'Paste starting at the last empty row
wb.Worksheets("Master").Range("A10:C15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True
'Close and save the workbook you copied from
wb2.Save
wb2.Close
End Sub
The dialog box successfully opens, and the selected file successfully opens, and then I get a run-time error 91 on the following line:
y.Sheets("Autoreport1.xlsm").Range("A10:C14").Value = x.Sheets("strFileToOpen").Range("A10:C14")
Again, this is probably something really stupid but all the same I need help. Thanks!
Bookmarks