Macro Copy & Paste and If functions??
Closed
plipz
-
11 Mar 2011 à 11:31
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 11 Mar 2011 à 11:40
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 11 Mar 2011 à 11:40
Related:
- Macro Copy & Paste and If functions??
- How to copy paste youtube link on android - Guide
- Excel online macro - Guide
- Copy paste e with accent - Guide
- Excel run macro on open - Guide
- Excel functions in french - Guide
1 response
rizvisa1
Posts
4478
Registration date
Thursday 28 January 2010
Status
Contributor
Last seen
5 May 2022
766
11 Mar 2011 à 11:40
11 Mar 2011 à 11:40
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.
