Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

cut & paste whole rows from sheet to another based on cell value

0

hi

I  face  a problem  when cut  the  whole  rows  from sheet2 to  sheet1   based  on cell value "FINISH"  in COL P in sheet2   ,  it  doesn't  cut  the whole  rows which supposses ending   to COL AA   , but  the  code  finishes  to COL P  where   contains  the  value" FINISH"

Sub CutRows()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWS As Worksheet
    Set desWS = Sheets("SHEET1")
    Set srcWS = Sheets("SHEET2")
    With srcWS
        .Cells(1, 1).CurrentRegion.AutoFilter 16, "FINISH "

        .AutoFilter.Range.Offset(1).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

I   use this  code   I  hope  to  get  assistance  to  fix  it 

thanks  in advance 

Answer
Discuss

Answers

0

That is because your code specifies the range to copy and delete as AutoFilter.Range and that range ends at column P. So, if the code actually works the way you want all you need to do is change how you specify the range.

The code below has two purposes. First it shows you how to check the location of a range you are about to use. That is useful in many, many instances. (I use something like it every day.) Second, it shows you how to modify the range.

Sub CutRows()
    ' complete the declarations before the first line of code
    Dim desWS   As Worksheet, srcWS As Worksheet
    Dim Rng     As Range            ' for testing only

    Application.ScreenUpdating = False      ' this is the first line of code
    Set desWS = Sheets("SHEET1")
    Set srcWS = Sheets("SHEET2")
    With srcWS
        .Cells(1, 1).CurrentRegion.AutoFilter 16, "FINISH "

        Set Rng = .AutoFilter.Range.Offset(1)
        MsgBox "Specified range is " & Rng.Address
        Set Rng = Rng.Resize(, Rng.Columns.Count + 6)
        MsgBox "Modified range is " & Rng.Address

        .AutoFilter.Range.Offset(1).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

After the first two lines of my additions you have two instances of the same range. One is .AutoFilter.Range.Offset(1), the other is Rng. You can use either one to subsequently copy and delete, and that is actually good practice. This isn't a very stark case but often errors occur because the object one does things with wasn't actually created. In such cases it's better to assign the object to its dedicated variable (Rng in this case) and then use that instance in the ensuing code. This is a borderline case and you may decide according to your taste.

Discuss

Discussion

it gives error  'type mismatch' in this  line 
        Set Rng = .AutoFilter.Range.Offset(1).Address

and  select  this  word Address 
Kalil (rep: 36) Jun 14, '21 at 4:17 am
Of course. Sorry about that. I've corrected the code in my answer above, removing the "Address" from the range's definition.
Variatus (rep: 4889) Jun 14, '21 at 5:43 am
it  still  copies   until   COL P    not   to AA 
Kalil (rep: 36) Jun 14, '21 at 6:28 am
Show your modified code.
Variatus (rep: 4889) Jun 14, '21 at 6:30 am
nothing  happens  see  the  updated file 
Kalil (rep: 36) Jun 14, '21 at 6:34 am
You are still using the same range to copy from and delete. My 4 lines are intended to show how to set the range and how to test if it was set correctly. They don't actually change your code below where I inserted them but that's where the range to copy from and delete is being set.
Variatus (rep: 4889) Jun 14, '21 at 6:46 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login