Macro to Copy, Paste data from another workbook

Jiriyanu

New Member
Joined
Dec 11, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

I'm needing some help on this macro I've been developing for a template. The purpose of the macro is to:
  1. Let the user choose a file to extract data from
  2. Open workbook (if necessary)
  3. Copy the data range
  4. Paste data range
  5. Close workbook (if necessary)
Not sure if my code is supposed to even be this long, this is my first time trying my hand at this.

VBA Code:
' GG Macro()
' To select report to upload for GG sheet for Section
 
    'Setup your 2 workbooks
    Set masterBook = ActiveWorkbook
    With otherbook = Application.GetOpenFilename(Title:="Choose file", MultiSelect:=False)
    End With
    
    If Filename <> False Then
     ' User pressed Cancel
    MsgBox "Please select a file"
End If
    
    'Setup Range from 70.5 or 72 file and copy
    Set otherBook2 = Workbooks.Open(otherbook)
    Sheets("Paste and Import").Select
    ActiveWindow.ScrollColumn = 2
    Range("W2:AA67").Select
    Selection.Copy
    
    'PasteSpecial to paste values, formulas, formats, etc.
    masterBook.Activate
    Sheets("Dist List 1").Select
    Range("A5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=48
    
    Call CloseWorkbook

End Sub

Sub CloseWorkbook()
'Close a workbook

  otherBook2.Close SaveChanges:=False
  
End Sub

I'm having trouble with this line:

VBA Code:
Set otherBook2 = Workbooks.Open(otherbook)

It's saying that it couldn't find it with the runtime error '1004'. I know that my issue is turning this string into a workbook so that it will actually open whatever file the user opens.


Any help is appreciated. Thanks guys!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi @Jiriyanu, welcome to MrExcel.

I've made some changes to your code, now it probably will do what you want. Note the lines with those comments
Rich (BB code):
' <<< comment
I would advise you to declare the variables you use in advance. Doing so prevents a lot of errors or undesirable behavior.
Use the Option Explicit statement in your modules to remind you to do so. How to enforce this statement in the VBE can be read over here (riptutorial).
Happy coding!

VBA Code:
Sub GG()
' GG Macro()
' To select report to upload for GG sheet for Section
 
    'Setup your 2 workbooks
    Set masterBook = ActiveWorkbook
    
    sFileName = Application.GetOpenFilename(Title:="Choose file", MultiSelect:=False)
        
    If sFileName = False Then
        ' User pressed Cancel
        MsgBox "Please select a file"
        Exit Sub                        ' <<< stop executing this code
    End If
    
    'Setup Range from 70.5 or 72 file and copy
    Set otherbook2 = Workbooks.Open(sFileName)
    Sheets("Paste and Import").Select
    Range("W2:AA67").Copy               ' <<< selection of ranges can be avoided
    
    'PasteSpecial to paste values, formulas, formats, etc.
    masterBook.Activate
    Sheets("Dist List 1").Select
    Range("A5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'    Call CloseWorkbook
    otherbook2.Close SaveChanges:=False
End Sub

Sub CloseWorkbook()
'Close a workbook

  otherbook2.Close SaveChanges:=False   ' <<< this will not work;  Object variable "otherBook2" is out of scope
  
End Sub
 
Upvote 0
Solution
Hi @Jiriyanu, welcome to MrExcel.

I've made some changes to your code, now it probably will do what you want. Note the lines with those comments
Rich (BB code):
' <<< comment
I would advise you to declare the variables you use in advance. Doing so prevents a lot of errors or undesirable behavior.
Use the Option Explicit statement in your modules to remind you to do so. How to enforce this statement in the VBE can be read over here (riptutorial).
Happy coding!

VBA Code:
Sub GG()
' GG Macro()
' To select report to upload for GG sheet for Section

    'Setup your 2 workbooks
    Set masterBook = ActiveWorkbook
   
    sFileName = Application.GetOpenFilename(Title:="Choose file", MultiSelect:=False)
       
    If sFileName = False Then
        ' User pressed Cancel
        MsgBox "Please select a file"
        Exit Sub                        ' <<< stop executing this code
    End If
   
    'Setup Range from 70.5 or 72 file and copy
    Set otherbook2 = Workbooks.Open(sFileName)
    Sheets("Paste and Import").Select
    Range("W2:AA67").Copy               ' <<< selection of ranges can be avoided
   
    'PasteSpecial to paste values, formulas, formats, etc.
    masterBook.Activate
    Sheets("Dist List 1").Select
    Range("A5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'    Call CloseWorkbook
    otherbook2.Close SaveChanges:=False
End Sub

Sub CloseWorkbook()
'Close a workbook

  otherbook2.Close SaveChanges:=False   ' <<< this will not work;  Object variable "otherBook2" is out of scope
 
End Sub
Thanks GWteB!

That worked just fine.
 
Upvote 0
You are welcome and thanks for letting me know.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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
Back
Top