Inserting new rows based on cell value, and copying data to the new rows

[Yann74]

Hi all,

I've been searching a solution to my issue on the board but could not find a working solution that covers my needs.
I'd really appreciate some help to come up with a VBA code that does the following:

I have an Excel file with multiple sheets. In a specific sheet called "xyz" I have data from columns A to BW. Row 1 is for data labels.
The number of rows is variable depending on the source data.

I'd need code to check if column C contains a number or if it's empty.
If it's empty, then proceed to next row.
If it contains a number, then the code should insert the corresponding number of rows below, while copying (and keeping the format) columns G to BW from the origin row to the newly inserted ones.
The loop should process all rows iteratively until there is no data in column A.

For example:
Cell C2 contains "2". Code should insert 2 new rows under row 2 (so 3 and 4) and should copy G2:BW2 to G3:BW3 and G4:BW4 .

Cell C3 (now C5 after the previous rows have been inserted) contains "3". Code should insert 3 new rows under row 5 (so 6, 7 and 8) and should copy G5:BW5 to G6:BW6, G7:BW7 and G8:BW8.

Cell C9 and the the entire row 9 have no data, so the loop should stop.

Many thanks in advance for your kind help!
Yann.

 

[Peter_SSs]

Welcome to the MrExcel board!

Give this a try with a copy of your workbook.

Sub Inert_rows()
  Dim r As Long
  
  For r = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    With Cells(r, 3)
      If IsNumeric(.Value) And Not IsEmpty(.Value) Then
        Rows(r + 1).Resize(.Value).Insert
        Range(Replace("G#:BW#", "#", r)).Copy Destination:=Range("G" & r + 1).Resize(.Value)
      End If
    End With
  Next r
End Sub

 

[Yann74]

Hi Peter,

Many thanks for your quick answer!
Your code works great, to one exception, and it's probably my fault for not being specific enough.
Some of my values between columns G and BW are formulas, so when doing "resize" (which is like manually dragging cells down I believe), it drags the formulas, which generates incorrect data in the new rows.
How would you go about replacing "Resize" by copy and paste values only?

Thanks again,
Yann.

 

[Peter_SSs]

paste values only

.. before you said

and keeping the format

.. so I have included values and formats in the paste below.

Sub Inert_rows_v2()
  Dim r As Long
  
  Application.ScreenUpdating = False
  For r = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    With Cells(r, 3)
      If IsNumeric(.Value) And Not IsEmpty(.Value) Then
        Rows(r + 1).Resize(.Value).Insert
        Range(Replace("G#:BW#", "#", r)).Copy
        Range("G" & r + 1).Resize(.Value).PasteSpecial Paste:=xlPasteValues
        Range("G" & r + 1).Resize(.Value).PasteSpecial Paste:=xlPasteFormats
      End If
    End With
  Next r
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

 

[Yann74]

Hi Peter,

Sorry for making it more complicated for you. ?

I've tested the new code and it works flawlessly!

Big huge thanks to you for your time and expertise!
I have occasionally used these forums to find code that would help with my excel projects, but it's the first time I ask for help directly, and you've been more than helpful. Thank you.

Kind Regards,
Yann.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

 

[badboybebe]

Hi Peter
I have the same question like Yann. But I cant not use your code. Please help me guide to use it.
Many thanks

 

[Peter_SSs]

Hi Peter
I have the same question like Yann. But I cant not use your code. Please help me guide to use it.
Many thanks

Welcome to the MrExcel board!

One way to implement a macro..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

 

[badboybebe]

994400-SPK2110200001.xlsm
	B	C	D	E
22	Ctns	Item	Description	Size
23	10	111001001105	Jefferson JfyBlk/ShlWht M10	M10
24	6	111001001105	Jefferson JfyBlk/ShlWht M11	M11
25	2	111001001105	Jefferson JfyBlk/ShlWht M12	M12
Sheet1

Thanks for you help. I have run and it error in debug message
Let's me specific my situation. In my mini-sheet, Ctns (B) is the column shows how many row need to be copied, other column are data. Please help me any way to copy then numbered each row.
Many thanks

 

[Peter_SSs]

Hmm, seems like your question is not quite the same as Yann74's after all.

Please post another mini-sheet showing your expected results for the sample data above so that I can see just what they are and where they should be.