Macro to open specific Workbook and copy data

[howard]

I have the following code below to open a file and copy data from sheet "Summary Group" but no data is pasted into the current workbook

It would be appreciated if someone could assist me

 Sub Open_Imported_Data()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.CutCopyMode = False
End With


ChDir "C:\Sales Reports"

Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Sales Reports\BR1 Group Sales Ver 1.1.xlsm"
Sheets(3).Range("A1:H" & Rows.Count).End(xlUp).Copy
Sheets("Imported Data").PasteSpecial Paste:=xlPasteValues
Sheets("Imported Data").PasteSpecial Paste:=xlPasteFormats



With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.CutCopyMode = False
End With
End Sub

 

[Joe4]

The issue is that once you open up the new workbook, that becomes the active workbook. So you need to select/activate the first workbook again so you can paste the data to it.
Whenever I am doing something like this, I like to use 2 workbook variables. Set the first one right away in your code, and the second one right after you open the new workbook.
Now you have a good way to differeniate the two workbooks and can easily move back and forth between them.

So I would update your code like this:

Sub Open_Imported_Data()

    Dim wb1 As Workbook
    Dim wb2 As Workbook
    
'   Set current workbook to wb1
    Set wb1 = ActiveWorkbook

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .CutCopyMode = False
    End With

    ChDir "C:\Sales Reports"

    Workbooks.Open Filename:= _
        "C:\Sales Reports\BR1 Group Sales Ver 1.1.xlsm"
        
'   Set new workbook just opened up to wb2
    Set wb2 = ActiveWorkbook
        
    Sheets(3).Range("A1:H" & Rows.Count).End(xlUp).Copy

'   Activate wb1 to paste to
    wb1.Activate
    Sheets("Imported Data").PasteSpecial Paste:=xlPasteValues
    Sheets("Imported Data").PasteSpecial Paste:=xlPasteFormats

    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .CutCopyMode = False
    End With
    
End Sub

So you can see you can easily activate one of the workbooks with a command like:

wb1.Activate

You can also easily close the workbook you just opened like this:

wb2.Close

 

[DanteAmor]

Try this

Sub Open_Imported_Data()
  With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .CutCopyMode = False
  End With
  
  Workbooks.Open Filename:="C:\Sales Reports\BR1 Group Sales Ver 1.1.xlsm"
  Sheets(3).Range("A1:H" & Sheets(3).Range("A" & Rows.Count).End(xlUp).Row).Copy
  With ThisWorkbook.Sheets("Imported Data").Range("A1")
    .PasteSpecial Paste:=xlPasteValues
    .PasteSpecial Paste:=xlPasteFormats
  End With
  
  With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .CutCopyMode = False
  End With
End Sub

 

[howard]

Thanks for help and your valuable input

I now get a run time error "application defined or object-defined error" and the code below is highlighted

 Sheets("Imported Data").PasteSpecial Paste:=xlPasteValues

 

[Joe4]

I was assuming that the rest of your code was good, but it looks like that may not be the case.
Did you try Dante's code? It looks like he re-wrote those sections of your code.

 

[DanteAmor]

I now get a run time error "application defined or object-defined error" and the code below is highlighted

Did you try Dante's code? It looks like he re-wrote those sections of your code.

Thanks Joe, so I checked all the code and found several details that I corrected in post #3.

 

[howard]

Hi Dante


thanks for the code


It works perfectly

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.