VBA Copy Range and Paste to The First Blank Row on Another

[ir121973]

Hi, I wonder whether someone may be able to help me please.

I've put together the following code which performs the following:

• Search column C on the IDEAS sheet, and from row 8, if the cell is populated, copy the row from columns B:S then
• Paste to the first blank row, again starting in row 8 on the "All Data" sheet in columns B:S.

Sub CopyRows()
    
    Dim LastRow As Long
    
    Application.ScreenUpdating = False
    
    Sheets("IDEAS").Select
    Set destRng = Sheets("All Data").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
    
    LastRow = Range("C" & Rows.Count).End(xlUp).Row
    Range("B8:S" & LastRow).Copy Destination:=destRng
    
    With Sheets("All Data")
        Columns("B:S").AutoFit
    End With
End Sub

The information is copied and paste to the first available blank row, but the problem I have is that for some reason the data is being paste to column C rather than column B.

Many thanks and kind regards

Chris

 

[dmt32]

Hi,
see if this change to your code helps.

Sub CopyRows()
    
    Dim LastRow As Long
    Dim destRng As Range
    
    Application.ScreenUpdating = False
    
    With Sheets("All Data")
    Set destRng = .Range("B" & .Cells(.Rows.Count, "B").End(xlUp).Row + 1)
    
    LastRow = Sheets("IDEAS").Range("C" & Rows.Count).End(xlUp).Row
    Sheets("IDEAS").Range("B8:S" & LastRow).Copy Destination:=destRng
    
        .Columns("B:S").AutoFit
    End With
    Application.ScreenUpdating = True
End Sub

Dave

 

[ir121973]

Hi @dmt32, thank you for taking the time to reply to my post and for the code which works perfectly.

All the best and kind regards

Chris