Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy from various sheets and create a master list.
Looking to perfect a macro....
I have 6 sheets, each named Monday - Saturday. Each one has a specific web query on them begining in cell a1 (specifically a1 to G). I need my macro to copy the data from each sheet and paste it on the "Sorter Sheet" creating one list for the week. In plain words.... (1) Update all web queries in book. (2) Copy all data from Monday and paste starting with A1 on 'sorter sheet'. (3) Copy all data from Tuesday and paste on next available row on 'sorter sheet'. (4)Copy all data from Wednesday and paste on next available row on 'sorter sheet'. (5) ...and so on until Saturday sheet. Any help will be greatly appreciated. Thanx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy from various sheets and create a master list.
Sub CopyData() Dim va s Variant, i as Long, sh as worksheet Dim rng as Range, rng1 as Range dim sh1 as Worksheet v = Array("Monday","Tuesday","Wednesday","Thursday","F riday","Saturday") for i = lbound(v) to ubound(v) set sh = worksheets(v(i)) sh.querytables(1).Refresh BackgroundQuery:=False Next set sh1 = Worksheets("shorter") sh1.Cells.Clearcontents for i = lbound(v) to ubound(v) set sh = worksheets(v(i)) set rng = sh.Range("A1").currentRegion set rng1 = sh1.Cells(rows.count,1).End(xlup) if not isempty(rng1) then set rng1 = rng1(2) rng.copy rng1.Pastespecial xlValues next End sub -- Regards, Tom Ogilvy "J.W. Aldridge" wrote: Looking to perfect a macro.... I have 6 sheets, each named Monday - Saturday. Each one has a specific web query on them begining in cell a1 (specifically a1 to G). I need my macro to copy the data from each sheet and paste it on the "Sorter Sheet" creating one list for the week. In plain words.... (1) Update all web queries in book. (2) Copy all data from Monday and paste starting with A1 on 'sorter sheet'. (3) Copy all data from Tuesday and paste on next available row on 'sorter sheet'. (4)Copy all data from Wednesday and paste on next available row on 'sorter sheet'. (5) ...and so on until Saturday sheet. Any help will be greatly appreciated. Thanx |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy from various sheets and create a master list.
Thanx Tom. However, getting error on the following: Dim va s Variant, i as Long, sh as worksheet |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy from various sheets and create a master list.
Dim v as Variant, i as Long, sh as worksheet
"J.W. Aldridge" wrote: Thanx Tom. However, getting error on the following: Dim va s Variant, i as Long, sh as worksheet -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy from various sheets and create a master list.
Dim v as Variant, i as Long, sh as worksheet
-- Regards, Tom Ogilvy "J.W. Aldridge" wrote: Thanx Tom. However, getting error on the following: Dim va s Variant, i as Long, sh as worksheet |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy from various sheets and create a master list.
Worked great.
Thanx. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create A Master List From Several Lists | Excel Worksheet Functions | |||
Create a copy with all data linked to the master | Excel Discussion (Misc queries) | |||
create a master list from other sheets | Excel Worksheet Functions | |||
I have a master spreadsheet, how do I create a copy? | Excel Worksheet Functions | |||
How do I create a set of sheets that will update from a master? | Excel Discussion (Misc queries) |