Excel Macro Help

Copper Contributor

I have a spreadsheet that I am looking to build a macro to help with formatting. 

Column A is the identify cell, that is an ID #. 

If there is a data in Column G, then I need to add a row below, enter in the data from Columns A-C, into the new row, Copy/Paste the data from Column G-I, into Columns D-F. 

There could be data in Column G-I, that needs to be into a new row, having the same data from A-C being copied, but also Column J-L, and Column M-O.

Not all the time will be there be data in G-I, there might only be data in Column J-L, or only in Column M-O, or all three. 

I am not sure how to build my macro to show that if there is data in those columns, to add a row below and to move the data below the first part. Attached is a sample of the layout of the report. 
Any help would be appreciated. 

Thank you,

Dawn Fuhrman

2 Replies
best response confirmed by dfuhrman840 (Copper Contributor)


If there is sensitive data (names, addresses and phone numbers of real people) in your file then please delete the file from your post.

Sub rearrange()

Dim i, j, k As Long

k = Range("A" & rows.Count).End(xlUp).Row * 4

For i = 2 To k

For j = 13 To 7 Step -3
If Cells(i, j).Value <> "" Then

Cells(i + 1, 1).EntireRow.Insert
Range(Cells(i + 1, 4), Cells(i + 1, 6)).Value = Range(Cells(i, j), Cells(i, j + 2)).Value
Range(Cells(i + 1, 2), Cells(i + 1, 3)).Value = Range(Cells(i, 2), Cells(i, 3)).Value

'next 3 lines are for formatting of column A, this might cause the code run significantly longer
'you can delete these 3 lines to return e.g. Plan ID 1253 instead of 001253

Cells(i, 1).Copy
Cells(i + 1, 1).PasteSpecial xlValues
Cells(i + 1, 1).PasteSpecial xlFormats


End If

Next j

Next i

Application.CutCopyMode = False

End Sub

You can try this code. Lines 19 to 21 of the code are for the formatting in column A. This might cause the code to run significantly longer. If it's ok to return e.g. Plan ID 1253 instead of 001253 you can comment out these lines.

You are amazing! This is exactly what I was looking for. Thank you! It is fake data.