Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    VBA Code to Open Workbook and copy data into destination workbook

    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/showthr...99#post3223099

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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}.
    CopySheet.PNG
    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


    Note: This assumes that the The Source workbook is in your current directory, usually the Excel default directory as defined in Options.
    Last edited by RetiredGeek; 2012-07-27 at 15:55.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If it is "sheet1 (not necessarily named sheet1)" then why not use something like:
    nb.Sheets(1)....

    Steve

  4. #4
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    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
    Attached Files Attached Files

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    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

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •