Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro modification
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 :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro modification
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) -- Regards Ron de Bruin http://www.rondebruin.nl "HJ" wrote in message ... 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 :) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro modification
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 -- Regards, Tom Ogilvy "HJ" wrote in message ... 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 :) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro modification
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 -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... 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) -- Regards Ron de Bruin http://www.rondebruin.nl "HJ" wrote in message ... 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 :) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro modification
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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... 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) -- Regards Ron de Bruin http://www.rondebruin.nl "HJ" wrote in message ... 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 :) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro modification
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 -- Regards Ron de Bruin http://www.rondebruin.nl "HJ" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... 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) -- Regards Ron de Bruin http://www.rondebruin.nl "HJ" wrote in message ... 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 :) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro modification
Worked perfect!!! Thanks very much to both you and Tom!
"Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "HJ" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... 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) -- Regards Ron de Bruin http://www.rondebruin.nl "HJ" wrote in message ... 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 :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Modification Help | Excel Discussion (Misc queries) | |||
macro - modification | Excel Discussion (Misc queries) | |||
Macro Modification | Excel Worksheet Functions | |||
Macro modification | Excel Discussion (Misc queries) | |||
Need Macro Modification | Excel Programming |