Macro assistance needed

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

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]

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