Macro Copy & Paste and If functions??

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,






I am a COMPLETE BEGINNER when it comes to macros. I get the basic idea though.

I have a number of tabs set up. I want a macro to search through every Column D in every tab and each time it comes across a number "1" in Column D to copy the whole of the row which the number occurs into another new tab.

Then do the same operation for the number "2" up to five.

I think this is possibly very easy but I've got no idea really where to start. Any help / code is much appreciated. I'm using Excel07 if it makes any difference.

Thank you!!!!

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
use macro recorder to record your actions as a macro ( you have to enable the developer tab in excel macro, the button would be there)

start the macro
select all cells
sort on column D
apply custorm filter for >=1 and <=5
copy all the rows, and paste it to the target sheet
stop the macro recorder
this gives you a template that you can expand to finish the work

the additonal work includes

you have to loop thru each sheet execpt the one where you are pasting the data

for each sheet in sheets
if sheet.name <> "pastesheet")
then
' do the macro thing
end if
next

other thing to remember is that when it comes to pasting, each time the start row would be changing. so you need to find out what is the start row. Looks at this
https://ccm.net/forum/affich-535387-merge-rows-if-it-meets-criteria-in-excel

your lines of interest are
Set Cell = Sheets("Sheet2").Cells.Find("*", Sheets("Sheet2").Cells(1, 1), , xlWhole, xlByRows, xlPrevious)
If Cell Is Nothing _
Then
lTgtRow = 1
Else
lTgtRow = Cell.Row + 1
End If

this is finding what row is available for pasting the data.