locked
Copying Excel columns and appending all to 1 sheet RRS feed

  • Question

  • Hi Folks -

    I posted earlier today but my requirements changed slightly so this is my final requirements and I hope you can assist.  I have a Master "Utility" excel file that I need to update with "Sheet1","Sheet2", and "Sheet3" content from another excel file. 

    I need to take Columns E, AA & V starting in Row1 from all (3) sheets and paste into Sheet "HC_Input" Row 2 in my Master "Utility" excel file.  However, the caveat is, col E from source goes in col A in target, col AA from source goes into col B in target and col V in source goes in col E in target goes in B, and V goes in E. 

    Or my other option is to just paste in Col A-C, and then insert two columns after B?

    I'm having trouble appending each sheet to "HC_Input", I need help cherry picking columns as well and appending them after the last row.

    Thanks so much for you help!

    Thursday, June 22, 2017 11:54 PM

All replies

  • Try a macro like this, stored in your Master Utility file:

    Sub TestMacro()
        Dim wkBK As Workbook
        Dim shtT As Worksheet
        Dim shtS As Worksheet
        Dim strName As String
        Dim lngR1 As Long
        Dim lngR2 As Long
        
        Set shtT = ThisWorkbook.Worksheets("HC_Input")
        
        'Clear out HC_Input
        shtT.Range("A2:E" & shtT.Rows.Count).ClearContents
        
        strName = Application.GetOpenFilename("Excel Files (*.xlsx; *.xlsm),*.xlsx; *.xlsm", , "Pick the source file")
        
        Set wkBK = Workbooks.Open(strName)
        
        For Each shtS In wkBK.Worksheets(Array("Sheet1", "Sheet2", "Sheet3"))
            lngR1 = shtT.Cells(shtT.Rows.Count, "A").End(xlUp).Row + 1
            lngR2 = shtS.Cells(shtS.Rows.Count, "A").End(xlUp).Row
            shtS.Range("E1:E" & lngR2).Copy shtT.Range("A" & lngR1)
            shtS.Range("AA1:AA" & lngR2).Copy shtT.Range("B" & lngR1)
            shtS.Range("V1:V" & lngR2).Copy shtT.Range("E" & lngR1)
        Next shtS
        
        wkBK.Close False
        
    End Sub


    Tuesday, June 27, 2017 8:39 PM
  • I will try this, thank you very much for your help!!!
    Wednesday, June 28, 2017 10:41 PM
  • Hello,

    Does the code shared by Bernie work for you?

    If it helps resolve your issue, i suggest you mark it as answer to close this thread. If your issue persist, please feel free to let us know.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 4, 2017 2:05 AM