VBA Code to Open Workbook and copy data into destination workbook

Topic

New Reply

I have a workbook which I have downloaded and have tried to write code to open the source workbook and then to copy the data from sheet1 (not necessarily named sheet1) in the source workbook into the destination workbook in the sheet next to the sheet named “Pivot Table”

The code allows me to select the source workbook, however the folowing 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

http://www.mrexcel.com/forum/showthread/?649804-VBA-Code-to-copy-data-and-automate-tasks&p=3223099#post3223099

Reply | Quote

Replies

Howard,

Here is what I think you want to do?
Workbook 1 contains the macro to do the work.
Macro prompts for workbook 2 which contains the Sheet to be copied.
Sheet is to be copied to workbook 3 {BR1 Vat Report Macro.xlsm}.
31572-CopySheet
If that is correct here is the code to do it.

Code:

Option Explicit

Sub Open_Workbook()

   Dim wkbkMacros     As Workbook   'See note below
   Dim wkbkSource     As Workbook
   Dim wsSource       As Worksheet
   Dim wkbkDest       As Workbook
   Dim zOpenFileName  As String


   zOpenFileName = Application.GetOpenFilename
   
   If zOpenFileName = "" Then Exit Sub

   Application.ScreenUpdating = False

   Set wkbkMacros = ActiveWorkbook    '*** Note you don't really need this ***
   
   Set wkbkSource = Workbooks.Open(zOpenFileName)              'Source Workbook
   Set wsSource = wkbkSource.Sheets("Sheet1")                  'Source Sheet to copy
   Set wkbkDest = Workbooks.Open("BR1 Vat Report Macro.xlsm")  'Destination Workbook
   
   
   wsSource.Copy After:=wkbkDest.Sheets("Pivot Table")
   
   'Optional clean up code
   Application.DisplayAlerts = False
   With wkbkDest
       .Save
       .Close
   End With
   wkbkSource.Close
   
End Sub

:cheers:

Note: This assumes that the The Source workbook is in your current directory, usually the Excel default directory as defined in Options.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

Thanks for the reply. The Macro is in the workbook BR1 Vat Report Macro.xlsm

1) I need the data in the sheet next to the sheet named “Pivot Table” to be cleared and then allow me to open and select a workbook (in this instance BR1 raw data,but name will change each month, hence the reason for macro to open file and to allow user to select the desired workbook)

2) Once the source workbook has been open, the data on sheet1 (this may contain another name, but it must be the first sheet) must be copied into the sheet next to the Pivot table on the destination workbook (BR1 Vat Report Macro.xlsm)

It would be appreciated if you would amend your code accordingly

Reply | Quote

If it is “sheet1 (not necessarily named sheet1)” then why not use something like:
nb.Sheets(1)….

Steve

Reply | Quote

Hi Steve

Thanks for the reply. I have made the change to my code, but it still does not work. I have reoplied to RG, who has kindly provided code which needs to be tweaked.

I have attached sample data of the destination workbook containing the macro

Reply | Quote

Hi Steve

I have re-tested the code below and it works fine

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(1).Copy After:=Workbooks(“BR1 Vat Report Macro.xlsm”).Sheets(“Pivot Table”)
End Sub

Reply | Quote