VBA Code to copy data and automate tasks

Maybe:

Hi John

Thanks for the help. I have used your code and adapted it to some code I have previously use. When activating the macro, I allow the user to select a source workbook, but the following code is highlighted


nb.Sheets("Sheet1").Copy After:=Workbooks("BR1 Vat Report Macro.xlsm").Sheets("Pivot Table")


See full code below

Sub Open_Workbook()

Dim nb As Workbook, tw As Workbook, ts As Worksheet
a = Application.GetOpenFilename
If a = False Or IsEmpty(a) Then Exit Sub
With Application
.ScreenUpdating = False
End With
Set tw = ThisWorkbook
Set ts = tw.ActiveSheet
Set nb = Workbooks.Open(a)
nb.Sheets("Sheet1").Copy After:=Workbooks("BR1 Vat Report Macro.xlsm").Sheets("Pivot Table")

End Sub

Your assistance in correcting this will be most appreciated

Hi John

Thanks for the help. I have used your code and adapted it to some code I have previously use. When activating the macro, I allow the user to select a source workbook, but the following code is highlighted


nb.Sheets("Sheet1").Copy After:=Workbooks("BR1 Vat Report Macro.xlsm").Sheets("Pivot Table")


See full code below

Sub Open_Workbook()

Dim nb As Workbook, tw As Workbook, ts As Worksheet
a = Application.GetOpenFilename
If a = False Or IsEmpty(a) Then Exit Sub
With Application
.ScreenUpdating = False
End With
Set tw = ThisWorkbook
Set ts = tw.ActiveSheet
Set nb = Workbooks.Open(a)
nb.Sheets("Sheet1").Copy After:=Workbooks("BR1 Vat Report Macro.xlsm").Sheets("Pivot Table")

End Sub

Your assistance in correcting this will be most appreciated

What error are you getting?

Hi John

Thanks for the reply. I am getting "run time error 9 subscript out of range" 7 the following code is highlighted

nb.Sheets("Sheet1").Copy After:=Workbooks("BR1 Vat Report Macro.xlsm").Sheets("Pivot Table")

It means excel can't find something in the code.

Maybe you don't have a - nb.Sheets("Sheet1") ??

or you don't have a - Workbooks("BR1 Vat Report Macro.xlsm"). - This workbook needs to be open.

or you don't have a - Sheets("Pivot Table").

Check the spelling of the workbook and sheet names, and make sure there are no errant spaces.

Hi John

Thanks for the reply. I did not have sheets("sheet1")

I have amended the code and it now works perfectly

You're welcome. Glad to help out, and thanks for the feedback.