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

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

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

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

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

Copy from various sheets and create a master list.
 

Worked great.
Thanx.