Copy till first non-blank row and paste on first blank row

patsdavixen

New Member
Joined
Mar 5, 2013
Messages
32
I'm working on this macro that copies data on sheet1 from A2:AI2 till the last non-blank row in the same range i.e. A:AI and paste it on sheet2 in the first blank row. However, my code keeps picking up A1:AI1 from sheet1 as well and pastes it on sheet2 just before the data that I actually want to paste. Here's my code:
Code:
Sub Copy()
    Range("A2").Select
    lastCol = ActiveSheet.Range("A2:AI2").End(xlToRight).Column
    lastrow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
    ActiveSheet.Range("A2:AI2", ActiveSheet.Cells(lastrow, lastCol)).Copy
    ActiveSheet.Next.Select
    If Application.WorksheetFunction.CountA("A:A") = 0 Then
    [A1].Select
    Else
    On Error Resume Next
    Columns(1).SpecialCells(xlCellTypeBlanks)(1, 1).Select
    If Err <> 0 Then
    On Error GoTo 0
    [A65536].End(xlUp)(2, 1).Select
    End If
    On Error GoTo 0
    End If
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End Sub

Please help me fix it

Thanks,
Pat
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
but in this where you facing problem ?

what is not going on proper way ?
 
Upvote 0
Hi kevatarvind,

The problem is that the macro should copy only from A2:AI2 downwards. It should not capture A1:AI1.. The range is variable but Row 1 will never be a part of the range as it is a header in my worksheet.
 
Upvote 0
Hi Pat,

Here's some code you can try.

Code:
Sub MyCopy()
    Dim lastCol As Long, lastRow As Long

    
    With Sheets("Sheet1")
        lastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("A2", .Cells(lastRow, lastCol)).Copy
    End With

    
    With Sheets("Sheet2")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        If lastRow = 1 And .Cells(1) = "" Then lastRow = 0
        .Cells(lastRow + 1, "A").PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With

    Application.CutCopyMode = False
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,222
Messages
6,177,219
Members
452,765
Latest member
Erka Gizli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top