VBA Code to Cut and Paste into Another Worksheet

[nhbartos]

Hi folks,

I am trying to modify this code to work as follows:

When "Amelia Rollins" appears in column A of worksheet "Step1", cut range A:D (same row #)
Paste range A:D into Worksheet "Amelia", row 6, down.

Sub MoveStep1()    Dim i As Variant
    Dim endrow As Integer
    Dim Step1 As Worksheet, Amelia As Worksheet


    Set Step1 = ActiveWorkbook.Sheets("step1")
    Set Amelia = ActiveWorkbook.Sheets("Amelia")


    endrow = ASR.Range("A" & ASR.Rows.Count).End(xlUp).Row


    For A = 2 To endrow
        If ASR.Cells(A, "Amelia Rollins").Value = "Amelia Rollins" Then
           ASR.Cells(A, "Amelia Robbins").EntireRow.Cut Destination:=amelia.Range("A" & Step1.Rows.Count).End(xlUp).Offset(1)
        End If
    Next
End Sub

 

[JackDanIce]

What is the variable ASR? At a guess try:

Sub MoveStep1()

    Dim x       As Long
    
    Application.ScreenUpdating = False
    
    With Sheets("step1")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).row
        With .Cells(1, 1).Resize(x, 4)
            .AutoFilter
            .AutoFilter Field:=1, Criteria1:=1
            .Offset(1).Resize(x - 1).SpecialCells(xlCellTypeVisible).Copy
        End With
        Sheets("Amelia").Cells(.Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        .AutoFilterMode = False
    End With
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
        
End Sub

 

[nhbartos]

Hi JackDanIce,

ASR? No clue. I found this code and have been trying to modify it to work for me.

Thanks for the code update.

I pasted your code in module 1 and ran it, but got an error on line 13:
.Offset(1).Resize(x - 1).SpecialCells(xlCellTypeVisible).Copy

Whatever it did really messed up the "Step1" tab.
The row numbers showed 1, then 437, with all of the other rows hidden or squeezed in.

To repeat...

In the "step1" tab, if "Amelia rollins" shows up in any row, column A, from A6 down, CUT row range A:D only.
Paste the range into A:D on tab "amelia" from A6 down.

Example:

If "amelia rollins" appears in A6, A10, A50 of tab "Step1":

Cut row ranges A6:D6, A10:D10, and A50:D50 from tab "step1".
Paste into tab "amelia" starting in A6.

In the "amelia" tab I have other data in columns E to L, so these cannot be affected by the paste.

Hope this helps clarify.

Best.

 

[nhbartos]

Ok...so I have been thinking further on this and messed up what I needed.

This is what I need:On the "Billing" tab:

If "amelia rollins" shows up in any row of column B, from B6 down, CUT all ranges A:D only.
Paste the ranges into A:D on tab "Billing 2" from A6 down. Copied ranges should populate from top, down.

* I have data in columns E:L, so it cannot be a paste of entire row. Only the A:D range.



Thanks!