Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Copy/Paste Data into one wksht, skipp one row before past
Hi,
I'm working w/about 100 tabs that contain data. I have a macro that will copy and paste the data and have tested it on three tabs (recorded macro). I also have a macro to delete empty rows between data. I could continue to record the macro for all of the tabs, but as you can imagine, that may take a while. The range of the copy/paste data is exactly the same for each sheet. Some sheets will have up to 52 rows populated across colums M:AN, but some will only have 4 rows populated, as all null values are returned as blank (intentionally). All of the sheets have actual names (not "Sheet 1", etc.). I would like to have all of the data pasted to one sheet, named "Copy.Paste". Is there any way to have the macro run for each sheet, pasting data underneath the previous entry, leaving only one completely empty row between each set of data? One other thing, the amount of sheets may increase or decrease over time, so I would need the macro to run for all of the sheets up until the "Copy.Paste" sheet. Is this possible? I appreciate any help you can provide. Thanks, Shoney |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Copy/Paste Data into one wksht, skipp one row before past
I'm confused, but maybe...
Dim wks as worksheet dim RngToCopy as range dim DestCell as range 'first paste goes here. Set DestCell = worksheets("copy.paste").range("a1") for each wks in activeworkbook.worksheets if lcase(wks.name) = lcase("copy.paste") then 'skip it else set rngtocopy = wks.range("M1:AN52") rngtocopy.copy _ destination:=destcell set destcell = destcell.offset(rngtocopy.rows.count,0) end if next wks ======== Untested, uncompiled. Watch for typos. Shoney wrote: Hi, I'm working w/about 100 tabs that contain data. I have a macro that will copy and paste the data and have tested it on three tabs (recorded macro). I also have a macro to delete empty rows between data. I could continue to record the macro for all of the tabs, but as you can imagine, that may take a while. The range of the copy/paste data is exactly the same for each sheet. Some sheets will have up to 52 rows populated across colums M:AN, but some will only have 4 rows populated, as all null values are returned as blank (intentionally). All of the sheets have actual names (not "Sheet 1", etc.). I would like to have all of the data pasted to one sheet, named "Copy.Paste". Is there any way to have the macro run for each sheet, pasting data underneath the previous entry, leaving only one completely empty row between each set of data? One other thing, the amount of sheets may increase or decrease over time, so I would need the macro to run for all of the sheets up until the "Copy.Paste" sheet. Is this possible? I appreciate any help you can provide. Thanks, Shoney -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare two wk sheets with common data using copy paste macro | Excel Worksheet Functions | |||
Can I copy page set ups in Excel 2003 from one wksht to another | Excel Worksheet Functions | |||
How to create a macro which performs a copy-and-paste task every 5mins after data is refreshed from web page? | Excel Worksheet Functions | |||
Macro: Insert, copy and past data from sheet | Excel Discussion (Misc queries) | |||
How do you copy and past data from one spreadsheet to another? | Excel Discussion (Misc queries) |