Copy and Paste Rows that start from a certain column for example column AA

Question

I am using the below code to automate copy and pasting to another sheet. However i do not want to copy the entire row- only the row from column AA. Please help.

I have tried using .range instead of .rows but this is bringing unwanted results. .range("AA5:AZ5") - this pastes the contents far more times than required and also means i have to define the end of the data each time

a = Worksheets("Reference Sheet").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To a
If Worksheets("Reference Sheet").Cells(i, 40).Value = "Private Standard" Then
Worksheets("Reference Sheet").Rows(i).Copy
Worksheets("Private Sheet").Activate
b = Worksheets("Private Sheet").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Private Sheet").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Reference Sheet").Activate

I would have a read on how to use variables in the first place, but most importantly [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) , or `.Activate`. If noone takes ahead of me, I'll see if I can help with answer as well. — FAB, Sep 05 '19 at 11:35

z32a7ul · Accepted Answer · 2019-09-06T11:38:41.023

0

I would rewrite it this way:

Public Sub TestSub()
    Dim a As Long: a = Worksheets("Reference Sheet").Cells(Rows.Count, 1).End(xlUp).Row
    Dim i As Long: For i = 2 To a
        If Worksheets("Reference Sheet").Cells(i, 40).Value = "Private Standard" Then
            Worksheets("Reference Sheet").Cells(i, "AA").Resize(1, 26).Copy
            Dim b As Long: b = Worksheets("Private Sheet").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("Private Sheet").Cells(b + 1, 1).Resize(1, 26).PasteSpecial
        End If
    Next
End Sub

edited Sep 06 '19 at 11:38

answered Sep 05 '19 at 11:42

z32a7ul

3,695
3
21
45

Hi, when using this code i get a runtime error 438 - Object doesnt support this property or method – Alex Sep 06 '19 at 08:37
Worksheets("Private Sheet").Cells(b + 1, 1).Resize(1, 26).Paste This line – Alex Sep 06 '19 at 11:02
I see, Range has no .Paste, only .PasteSpecial, I updated the code – z32a7ul Sep 06 '19 at 11:39

score 0 · Answer 2 · answered Sep 05 '19 at 11:53

See if this helps, comments in code:

Option Explicit

Sub something()

Dim wsReference As Worksheet: Set wsReference = Worksheets("Reference Sheet")
Dim wsPrivate As Worksheet: Set wsPrivate = Worksheets("Private Sheet")

Dim lRowRef As Long, lRowPrv As Long
Dim lColRef As Long
Dim i As Long

lRowRef = wsReference.Cells(Rows.Count, 1).End(xlUp).Row
lColRef = wsReference.Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To lRowRef
    If wsReference.Cells(i, 40).Value = "Private Standard" Then        
        lRowPrv = wsPrivate.Cells(Rows.Count, 1).End(xlUp).Row + 1

        With wsReference
            .Range(.Cells(i, 27), .Cells(i, lColRef)).Copy _
                Destination:=wsPrivate.Range(wsPrivate.Cells(lRowPrv, 1), wsPrivate.Cells(lRowPrv, lColRef - 27))
        End With
    End If
Next i
End Sub

Copy and Paste Rows that start from a certain column for example column AA

2 Answers2