Go through column list and paste to another tab-CodePudding

I need my code to go through a column (found in "Compare" tab), find the first non-blank value, and paste the values to another tab ("P&L"; this tab will already have a list, but in a row format). Moreover, I'll need these values to be pasted 2 cells apart from each other. Below is what I have come up with:

Sub CopyandPaste()

    Dim comp As Worksheet, colRange As Range, rowNum As Long
    Dim PL As Worksheet
    
    Set comp = ActiveWorkbook.Worksheets("Compare")
    Set PL = ActiveWorkbook.Worksheets("P&L")
    Set colRange = PL.Cells(5, Columns.Count).End(xlToLeft)
    

    'Get first non-blank cell in Compare tab
    With comp.Columns("A")
        .Find(what:="*", after:=.Cells(1, 1), LookIn:=xlFormulas).Select
    End With

    'Go through column A list in Compare tab and paste in P&L tab
    Do Until IsEmpty(ActiveCell)
        Selection.Copy
        colRange.Offset(0, 2).PasteSpecial Paste:=xlPasteValues
        comp.Range(ActiveCell.Offset(1, 0)).Select
    Loop

End Sub

The code is works until comp.Range(ActiveCell.Offset(2, 0)).Select. I need the code to advance to the next cell down the "Compare" tab list and paste it to the "P&L" tab.

Compare Tab

P&L Tab - Before

P&L Tab - After

Thank you in advanced!

CodePudding user response：

Allright. I guess this is the thing you are looking for. Let me walk through it:

Sub CopyandPaste()

    Dim comp, PL As Worksheet
    Dim rowNum, lngLastColumn, lngLastRow, lngFirstRow As Long
 
    Set comp = ActiveWorkbook.Worksheets("compare")
    Set PL = ActiveWorkbook.Worksheets("PL")
    
    'So first we want to know what is the last column of the compare sheet. This will return a number (long) which we can use in our Cells statements further on:
    
    lngLastColumn = PL.Cells(5, Columns.Count).End(xlToLeft).Column

    'The first blank cell is the last blank cell when you look up from the bottom. So we go from the absolute bottom (rows.count,1).end(xlUp) up and we want the last empty cell:
    
    lngFirstRow = 1
    lngLastRow = comp.Cells(Rows.Count, 1).End(xlUp).Row

    'Now we can loop through the 'new' items one at a time, and we can offset directely. So we loop through with a for loop. In that for loop we get the value of the the 'new' cell and get it into PL row 5 and the last column   2 (offset). Than we get a new last column and so on:
    
    For intCounter = 1 To lngLastRow
    
        PL.Cells(5, lngLastColumn   2) = comp.Cells(intCounter, 1)
        lngLastColumn = PL.Cells(5, Columns.Count).End(xlToLeft).Column
    
    Next intCounter

End Sub

Is this what you mean?