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

    Macro to open workbook and copy data to appropriate sheet

    I have a workbook called BR8 raw data output (source workbook) and balance recon.xlsm (destination workbook)

    I have written code which allows me to select which file to open (in this case BR8 raw data output ) and to copy the data from sheet (1) in source workbook and paste this to sheet "output" , which is sheet (2) in the destination workbook (balanced recon.xlsm)

    When running the macro only the headings are pasted into sheet "output" in the destination workbook

    It would be appreciated if someone could assist me in correcting the code

    See my code below

    I have also attached sample workbooks


    Code:
     Sub Open_OutputRep()
     
    ChDir ("C:\My Documents")
    A:
    
      Dim nb As Workbook, ts As Worksheet, A As Variant
        Dim rngDestination As Range
         With Sheets("Output")
    
           A = Application.GetOpenFilename
        If A = False Or IsEmpty(A) Then Exit Sub
        
          
        
       With Workbooks.Open(Filename:=A, Local:=True)
       
            With .Sheets(1)
                .Range("a1", .Range("Q" & Rows.Count).End(xlUp)).Copy _
                    Destination:=ThisWorkbook.Sheets("Output").Range("A" & Rows.Count).End(xlUp).Offset(1)
            End With
            .Close SaveChanges:=False
        End With
       answer = MsgBox("Does another file needs to be selected?", vbYesNo + vbQuestion, "Hello")
    If answer = vbYes Then
    GoTo A:
    End If
     Application.ScreenUpdating = True
      End With
      
    End Sub
    Attached Files Attached Files
    Last edited by HowardC; 2016-06-12 at 23:50.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Howard

    I've updated the code in the attached file [Balanced-recon-zeddy.xlsm]
    (my posted file is a little smaller than the one you posted).

    I've tested it with your sample file [BR8 Raw Data Output.xlsm] as posted.

    In my file, I've added a tiny button (in cell [A1] ) that you can click, to run the Open_OutputRep routine.

    The main reason you were not getting any data with your code is because you were using column Q to determine your copy range.
    Since your column Q is empty in the source file, you won't get anything.

    I may be away for a while, so if you have any further issues, post back here for some excellent support from our Lounge members.

    zeddy
    Attached Files Attached Files

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    I have been through your code and it makes sense-thanks for amending this. I see where I went wrong. Thanks for pointing this out

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Howard

    ..its wasn't my intention to point out errors. I hope this didn't offend you!
    I prefer to say I was explaining why it didn't work as expected.

    zeddy

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    I always appreciate your Input. I've learnt a great deal from you and the other Loungers on this site who have given up their time to help others

    Howard

Posting Permissions

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