May I ask if I have 2 sheets that of "Disposed" items that required to be moved, what would the VBA like?
Please find the attached Excel. I want the rows in Sheet 3 and Sheet 1 with "Disposed", being transferred to Sheet 2, and having the disposed items removed from Sheet 3 and 1.
I expected the red labelled "Disposed" rows in sheet 3 and 1 to be moved to sheet 2, followed by deleting the disposed items in sheet 3 and 1.
Thanks!
Ken
Hello Ken,
Not a lot different from your last thread, only this time we reference all the source sheets:
I hope that this helps.
Please Login or Register to view this content.
Cheerio,
vcoolio.
Thanks again. If you don't mind, can you explain briefly what do the following items mean?
Option Explicit
Sub TestKen()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
If ws.Name <> "Sheet2" Then
With ws.[A1].CurrentRegion
.AutoFilter 3, "Disposed"
.Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
.Offset(1).EntireRow.Delete
.AutoFilter
End With
End If
Next ws
Application.ScreenUpdating = True
End Sub
Hello Ken,
With this line of code:-
CurrentRegion refers to your dataset beginning at cell A1. This can also be written as:-Please Login or Register to view this content.
orPlease Login or Register to view this content.
With this line of code:-Please Login or Register to view this content.
The 3 stands for Column C, which, in this case, means the filter is placed on Column C filtering for the criteria "Disposed".Please Login or Register to view this content.
The End(3)(2) in this line of code:-
are simply the enumerations for xlUp which is the (3) and offset(1) which is the (2).Please Login or Register to view this content.
It is the same as writing:
orPlease Login or Register to view this content.
So the rows are counted from the bottom of the sheet (the very last row, Column A) upwards to the last used row of data then offset one row down from the last row to avoid overwriting previous rows of data.Please Login or Register to view this content.
I hope that this helps.
Cheerio,
vcoolio.
Last edited by vcoolio; 07-24-2021 at 12:00 AM.
Thank you so much for your time for explaining this! It all makes sense!
You're welcome Ken. I'm glad to have been able to assist.
Cheerio,
vcoolio.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks