I am a newbie to macros and was wondering if someone could help me with some
code to accomplish what I think is fairly simple to most of you. I'm trying
to create a master list by piecing together information from various sheets
off of an existing workbook.

What I would like to do is write a macro that would look at Sheet 1 and copy
rows 7,9,10,11 then I need to skip 16 rows and copy rows 23,25,26,27 then
skip 16 rows and copy rows 39,41,42,43 etc. I've tried autofilter but it
doesn't work well with the structure of the existing worksheet.

Does anyone have any suggestions? Thanks in advance.

HJ

Dim i as long, rng as range
i = 7
do while not isempty(cells(i,1))
set rng = union(cells(i,1),cells(i+2).Resize(3))
rng.Entirerow.copy destination:= _
Worksheets(2).Cells(rows.count,1).End(xlup)
i = i + 16
Loop

--
Regards
Tom Ogilvy


"HJ" wrote in message
...
I am a newbie to macros and was wondering if someone could help me with
some
code to accomplish what I think is fairly simple to most of you. I'm
trying
to create a master list by piecing together information from various
sheets
off of an existing workbook.

What I would like to do is write a macro that would look at Sheet 1 and
copy
rows 7,9,10,11 then I need to skip 16 rows and copy rows 23,25,26,27 then
skip 16 rows and copy rows 39,41,42,43 etc. I've tried autofilter but it
doesn't work well with the structure of the existing worksheet.

Does anyone have any suggestions? Thanks in advance.

HJ

Hi Tom,

Thanks for the reply and please bear with me here...Can you tell me how to
modify the macro to copy from a tab called Input-Sales then to create a new
tab called Master where the copied data would go?
"Tom Ogilvy" wrote:

Dim i as long, rng as range
i = 7
do while not isempty(cells(i,1))
set rng = union(cells(i,1),cells(i+2).Resize(3))
rng.Entirerow.copy destination:= _
Worksheets(2).Cells(rows.count,1).End(xlup)
i = i + 16
Loop

--
Regards
Tom Ogilvy


"HJ" wrote in message
...
I am a newbie to macros and was wondering if someone could help me with
some
code to accomplish what I think is fairly simple to most of you. I'm
trying
to create a master list by piecing together information from various
sheets
off of an existing workbook.

What I would like to do is write a macro that would look at Sheet 1 and
copy
rows 7,9,10,11 then I need to skip 16 rows and copy rows 23,25,26,27 then
skip 16 rows and copy rows 39,41,42,43 etc. I've tried autofilter but it
doesn't work well with the structure of the existing worksheet.

Does anyone have any suggestions? Thanks in advance.

HJ

Sub CopyData()
Dim i As Long, rng As Range, sh As Worksheet
Worksheets.Add(After:=Worksheets( _
Worksheets.Count)).Name = "Master"
Set sh = Worksheets("Input-Sales")
i = 7
Do While Not IsEmpty(sh.Cells(i, 1))
Set rng = Union(sh.Cells(i, 1), _
sh.Cells(i + 2, 1).Resize(3, 1))
rng.EntireRow.Copy Destination:= _
Worksheets("Master").Cells(Rows.Count, 1).End(xlUp)
i = i + 16
Loop
End Sub



--
Regards,
Tom Ogilvy



"HJ" wrote in message
...
Hi Tom,

Thanks for the reply and please bear with me here...Can you tell me how to
modify the macro to copy from a tab called Input-Sales then to create a
new
tab called Master where the copied data would go?
"Tom Ogilvy" wrote:

Dim i as long, rng as range
i = 7
do while not isempty(cells(i,1))
set rng = union(cells(i,1),cells(i+2).Resize(3))
rng.Entirerow.copy destination:= _
Worksheets(2).Cells(rows.count,1).End(xlup)
i = i + 16
Loop

--
Regards
Tom Ogilvy


"HJ" wrote in message
...
I am a newbie to macros and was wondering if someone could help me
with
some
code to accomplish what I think is fairly simple to most of you. I'm
trying
to create a master list by piecing together information from various
sheets
off of an existing workbook.

What I would like to do is write a macro that would look at Sheet 1
and
copy
rows 7,9,10,11 then I need to skip 16 rows and copy rows 23,25,26,27
then
skip 16 rows and copy rows 39,41,42,43 etc. I've tried autofilter but
it
doesn't work well with the structure of the existing worksheet.

Does anyone have any suggestions? Thanks in advance.

HJ