Macro Copy & Paste and If functions??
Closed
plipz
-
Mar 11, 2011 at 11:31 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 11, 2011 at 11:40 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 11, 2011 at 11:40 AM
Related:
- Macro Copy & Paste and If functions??
- Spell number in excel without macro - Guide
- Accessor and mutator functions c++ - Guide
- Macro excel download - Download - Spreadsheets
- What are the key functions of the supportassist for home pcs? - Download - Cleaning and optimization
- Automatically run a macro when opening a worksheet - Guide
1 response
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 11, 2011 at 11:40 AM
Mar 11, 2011 at 11:40 AM
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.
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.
