Adding the Same Data to my newly inserted lines

[jjm3066]

I have the following macro to enter in X amount of lines after Xth row, which works perfect:

Option Explicit
Sub InsertRows()
Dim AddRws As Long
Dim Spacing As Long
Dim Rw As Long
Spacing = Application.InputBox("Add empty rows after every X row, what is the value of X?", "Spacing", 1, Type:=1)
If Spacing = 0 Then Exit Sub
AddRws = Application.InputBox("Add how many rows to insert?", "Insert", 13, Type:=1)
If AddRws = 0 Then Exit Sub
Rw = Spacing + 1
Do
Rows(Rw).Resize(AddRws).Insert xlShiftDown
Rw = Rw + Spacing + AddRws
Loop Until Range("A" & Rw) = ""
End Sub


Now how do I tell it to paste the same data on the those new lines, so i dont have to manually copy and paste 200 times?

 

[Rick Rothstein]

Assuming you are talking about copying the data in Column into the newly created blanks, then this modification to your code should do what you asked for...

Sub InsertRows()
  Dim AddRws As Long
  Dim Spacing As Long
  Dim Rw As Long
  Spacing = Application.InputBox("Add empty rows after every X row, what is the value of X?", "Spacing", 1, Type:=1)
  If Spacing = 0 Then Exit Sub
  AddRws = Application.InputBox("Add how many rows to insert?", "Insert", 13, Type:=1)
  If AddRws = 0 Then Exit Sub
  Rw = Spacing + 1
  Do
    Rows(Rw).Resize(AddRws).Insert xlShiftDown
[COLOR=darkred]    With Cells(Rw, "A").Resize(AddRws)
      .FormulaR1C1 = "=R[-1]C"
      .Value = .Value
    End With
[/COLOR]    Rw = Rw + Spacing + AddRws
  Loop Until Range("A" & Rw) = ""
End Sub

 

[jjm3066]

Thank you for your reply. If my data was: C5:E17... where would I enter that in your formula? not wuite sure where it needs to know what I want to place in the blanks?

 

[Rick Rothstein]

Try changing this line...

[COLOR=#8b0000]    With Cells(Rw, "A").Resize(AddRws)
[/COLOR]

to this....

[COLOR=#8b0000]    With Cells(Rw, "C").Resize(AddRws, 3)
[/COLOR]

 

[jjm3066]

didnt work.. i dont think it know where to lookup my data that i want to place in the blank rows.

 

[Rick Rothstein]

Then I am having trouble visually where your data actually is... can you show us a before and after sample of your data? You don't have to use real data, I'm just trying to get an idea of structural layout.

 

[jjm3066]

OK sure thing.

Worksheet # 1

Column A Column B

Row 1 <Store Name> < Store Number>

This runs to Row 100

Worksheet #2

Column A Column B
Row 1 <SKU> <Item Description>

This runs to row 14

When I run the macro first listed in the thread, it runs Workbook A and places 13 blank lines in between each store name. I would like to take data on Worksheet #2 and place it in the 13 newly created blank lines, so I dont have to manually place underneath all 100 stores.

 

[jjm3066]

Well it didnt post right. Worksheet A has data in Column A and B, as well as Worksheet B has data in column A and B.

 

[Rick Rothstein]

Hmm, I'm not sure I remember you mentioning that your data was on a second worksheet... my code did not assume that. Still, I'm sorry, but your layout is still not clear to me. In this last message, you are saying you have data(?) in the first 14 rows of Sheet B in Columns A and B; however, earlier you said you had your data in C5:E17 (without mentioning a worksheet)... note this is one 13 rows of information. Is the same 13 rows of data in C5:E17 supposed to go into each of the the 13 blank rows that were inserted (Columns C thru E I'm guess)? How does this figure in with the 14 rows of whatever is in Columns A and B on Sheet B? Is there anyway you can show actual columns and data in them from each sheet and then show a picture of actual columns and data that should result after the macro runs? (A picture is worth a 1000 words.)