LinkBack URL
About LinkBacks
I need a macro that will select every row in sheet 1 that has a value >1 in column H and paste every one of those entire rows in sheet 2 in the next available row. Thank you!
I need a macro that will select every row in sheet 1 that has a value >1 in column H and paste every one of those entire rows in sheet 2 in the next available row. Thank you!
Try this:
Please Login or Register to view this content.
Maybe:
Please Login or Register to view this content.
Another option, if you have a lot of data and don't want to loop through thousands of rows copying and pasting each, would be to use an advanced filter. Normally Advanced Filter won't let you copy the filtered data to a different worksheet, but through VBA it will.
Let's say your data is in columns A:M, with headers in row 1. If the header in H1 is Length, then in an empty cell to the right of your data (like P1) type Length. In P2 type >1
This will be used as the CriteriaRange for the Advanced Filter. On Sheet2, select cells A1:M1 and assign a name to that range, like "FilteredData". You can then use the following code to almost instantly filter the sought after data and have it appear on Sheet2:
Note: This will overwrite anything in Sheet2 A1:Mxx. If you routinely append data to an already existing list on Sheet2, you could still do this but would need a couple extra lines of code to get the last used row.Please Login or Register to view this content.
That is virtually amazing and perfect! However, my data in row 1 can not be copied over and my headers are in row 2. What change would i need to make? Thanks!
Should be able to change
toPlease Login or Register to view this content.
Make sure the named range ("FilteredData") is A2:M2 on Sheet2, rather than A1:M1.Please Login or Register to view this content.
Thank you sooooooo much!
One last thing, since my sheet 1 will have hundreds, thousands of lines, how can I get it to end up on a cell that has one of the selected dates in it? The reason is, since I have it set to turn the auto filter off, it finishes clear at the top in row 2 (I have that line froze). I do not want to have to scroll down hundreds,thousands of lines to get back to where I was at. I hope that makes sense?!
Or it could just find the last cell in column a that has data in it.
If you want to select a cell on the sheet with the original data, you could just re-use the variable last and go to that cell.
That will select the last used cell in column A (based on the previously stored value of "last".Please Login or Register to view this content.
That is not working. It gives an run-time error. I think it is cuz it is just coming back from sheet 2 and does not have value for last for sheet 1. ??
I don't know your worksheet names or layouts, so simply Activate the sheet you want and select the "last" cell in whichever column you want. If the variable "last" was calculated on a different sheet than the one you want to end up looking at, re-calculate "last" at the end of the code for the new sheet.
Please Login or Register to view this content.
you are amazing! thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks