Macro modification

[Guest]

Tom Ogilvy provided me with this great macro in order to copy certain rows
from a spreadsheet into a new master tab. In summary, I am copying rows
7,9,10,11 then skiping 16 rows and copying 23,25,26,27, skiping 16 rows etc
down the entire sheet. For some reason, the macro is not copying row 11, 27,
etc. and I'm not sure how to modify to capture that row. Can someone explain
to me the section of the macro sh.Cells(i + 2, 1).Resize(3,1))? I presume
that is the line I would need to adjust to pick up that additional row.

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

Thanks in advance for your help. This is definitely a learning experience
for me!

Regards,
HJ

 

[Ron de Bruin]

This is working

Set rng = Union(sh.Cells(i, 1), _
sh.Cells(i + 2, 1).Resize(4, 1))

If i is 7 then this is the cell in row 7
sh.Cells(i, 1),

Then it use this line
sh.Cells(i + 2, 1) to go to row 9 (i = 7 +2)

Then Tom use resize to make the range 3 rows bigger
Resize(4, 1) ( it will count the start row also, that's why the 4)

 

[Tom Ogilvy]

Actually, it is copying the rows, it just overwrites the last one. Here is
the correction

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)(2)
i = i + 16
Loop
End Sub

 

[Ron de Bruin]

My Example is wrong ( it is working ) but I miss the typo in Tom's code

I have used Resize(4, 1)) to copy one more row
The macro delete this last row each time because of the typo.

Use Tom's changed example

 

[Guest]

Thanks to both Tom and Ron for helping me out with this one. I want to make
sure I understand the code since I will need to change it for other
worksheets where I will be doing something similar.

In another worksheet, I need to copy rows 14, 23, 24, 25, 26, and 30, then
skip
28 rows and copy 42, 51, 52, 53, 54, 58, etc.

The part where you mentioned the last row being deleted threw me for a loop
and I'm not sure how I would modify to accomplish the above example. Any
suggestions?

 

[Ron de Bruin]

Try this
See the VBA help for Union

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 = 14
Do While Not IsEmpty(sh.Cells(i, 1))
Set rng = Union(sh.Cells(i, 1), _
sh.Cells(i + 9, 1).Resize(4, 1), sh.Cells(i + 16, 1))
rng.EntireRow.Copy Destination:= _
Worksheets("Master").Cells(Rows.Count, 1).End(xlUp)(2)
i = i + 28
Loop
End Sub

 

[Guest]

Worked perfect!!! Thanks very much to both you and Tom!