Copy/Paste Worksheet Column to Another without overwriting

  • I would like to copy info from one worksheet (Column) and paste to another worksheet (Column) without overwriting previous data. Thank you in advance for your assistance. I have a worksheet that the managers fill out when they move assets from one location to another. I've created three macro's. One to copy this info on worksheet number 1 to another tab for the year. One to convert to PDF and open and e-mail in outlook to send to our transfer department (Works Great) and one to clear the page (Easy). My problem is I cannot get it to move from column B1:B13 to C and so on if that column is filled. I used column A for all the headings. I've attached the excel worksheet just in case.



    CODE HERE

    Sub Copy()
    '
    ' Copy Macro
    '
    '
    Sheets("Transfer Sheet").Select
    Range("B2:B14").Select
    Selection.Copy
    Sheets("FY16").Select
    Range("B1:B13").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=-6
    End Sub

  • Re: Copy/Paste Worksheet Column to Another without overwriting


    Try this:

    Code
    1. Sub Copy()
    2. Dim lc As Long
    3. With Worksheets("FY16")
    4. lc = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
    5. Sheets("Transfer Sheet").Range("B2:B14").Copy .Cells(1, lc)
    6. .Columns(lc).AutoFit
    7. End With
    8. End Sub

    Bruce :cool:

  • Re: Copy/Paste Worksheet Column to Another without overwriting


    Skywriter: Worked great. Thank you... I have been beating my head against the wall all morning. I hope you have a great weekend!


    Best Wishes~
    Rich





    Quote from skywriter;767423

    Try this:

    Code
    1. Sub Copy()
    2. Dim lc As Long
    3. With Worksheets("FY16")
    4. lc = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
    5. Sheets("Transfer Sheet").Range("B2:B14").Copy .Cells(1, lc)
    6. .Columns(lc).AutoFit
    7. End With
    8. End Sub