Excel to copy paste row based on cell values in columns

[rayray]

Hi,
I am new to VBA and trying to automate some of my day to day work. So any help would be greatly appreciated.
I have searched the forums for what I need but nothing was matching what I need.
I have an excel sheet with several rows and in each row there are some months with values others without. I would like excel to copy the row and insert it underneath with month values separated.
For example this is the data that I have
[TABLE="class: grid, width: 1043"]
<TBODY>[TR]
[TD]Client #</SPAN>[/TD]
[TD]Name</SPAN>[/TD]
[TD]Sales order</SPAN>[/TD]
[TD]Item </SPAN>[/TD]
[TD]Jan</SPAN>[/TD]
[TD]Feb</SPAN>[/TD]
[TD]Mar</SPAN>[/TD]
[TD]Apr</SPAN>[/TD]
[TD]May</SPAN>[/TD]
[TD]Jun</SPAN>[/TD]
[TD]Jul</SPAN>[/TD]
[TD]Aug</SPAN>[/TD]
[TD]Sep</SPAN>[/TD]
[TD]Oct</SPAN>[/TD]
[TD]Nov</SPAN>[/TD]
[TD]Dec</SPAN>[/TD]
[/TR]
[TR]
[TD] 1,234 </SPAN>[/TD]
[TD]CC</SPAN>[/TD]
[TD]XX</SPAN>[/TD]
[TD]XXXX</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] 100.00 </SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] 100.00 </SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</TBODY>[/TABLE]

In this case I would like excel to copy this line again and separate Apr and July (more if there) to look like this: Can anyone please help? Thank you
[TABLE="width: 1043"]
<TBODY>[TR]
[TD]Client #</SPAN>[/TD]
[TD]Name</SPAN>[/TD]
[TD]Sales order</SPAN>[/TD]
[TD]Item </SPAN>[/TD]
[TD]Jan</SPAN>[/TD]
[TD]Feb</SPAN>[/TD]
[TD]Mar</SPAN>[/TD]
[TD]Apr</SPAN>[/TD]
[TD]May</SPAN>[/TD]
[TD]Jun</SPAN>[/TD]
[TD]Jul</SPAN>[/TD]
[TD]Aug</SPAN>[/TD]
[TD]Sep</SPAN>[/TD]
[TD]Oct</SPAN>[/TD]
[TD]Nov</SPAN>[/TD]
[TD]Dec</SPAN>[/TD]
[/TR]
[TR]
[TD] 1,234 </SPAN>[/TD]
[TD]CC</SPAN>[/TD]
[TD]XX</SPAN>[/TD]
[TD]XXXX</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] 100.00 </SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] 1,234 </SPAN>[/TD]
[TD]CC</SPAN>[/TD]
[TD]XX</SPAN>[/TD]
[TD]XXXX</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] 100.00 </SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL span=13></COLGROUP>[/TABLE]
[TABLE="width: 1043"]
<TBODY>[TR]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[hiker95]

rayray,

Slight formatting problem. Be back in a little while.

 

[hiker95]

rayray,

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Client #	Name	Sales order	Item	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	1234	CC	XX	XXXX				100.00			100.00
3	1235	JJ	XY	XXXY		200.00				200.00				200.00
4
5
6
7
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Client #	Name	Sales order	Item	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	1234	CC	XX	XXXX				100.00
3	1234	CC	XX	XXXX							100.00
4	1235	JJ	XY	XXXY		200.00
5	1235	JJ	XY	XXXY						200.00
6	1235	JJ	XY	XXXY										200.00
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
'hiker95, 03/04/2013
' http://www.mrexcel.com/forum/excel-questions/689295-excel-copy-paste-row-based-cell-values-columns.html
Dim a As Variant, b As Variant, i As Long, ii As Long, n As Long, c As Long
With Sheets("Sheet1").Cells(1).CurrentRegion
  a = .Value
  n = Application.Count(.Range(.Cells(2, 5), .Cells(UBound(a, 1), UBound(a, 2))))
  ReDim b(1 To UBound(a, 1) * n, 1 To UBound(a, 2))
  For c = 1 To UBound(a, 2)
    b(1, c) = a(1, c)
  Next c
  ii = 1
  For i = 2 To UBound(a, 1)
    For c = 5 To UBound(a, 2)
      If a(i, c) <> "" Then
        ii = ii + 1
        b(ii, 1) = a(i, 1)
        b(ii, 2) = a(i, 2)
        b(ii, 3) = a(i, 3)
        b(ii, 4) = a(i, 4)
        b(ii, c) = a(i, c)
      End If
    Next c
  Next i
  .Cells(1).CurrentRegion.ClearContents
  .Cells(1).Resize(UBound(b, 1), UBound(b, 2)) = b
  .Range(.Cells(2, 5), .Cells(UBound(b, 1), UBound(b, 2))).NumberFormat = "#,##0.00"
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

 

[rayray]

Hiker95, I cant thank you enough for replying that fast. I have been trying to reach teach myself VBA but its just beyond me

I tried to use the code above but i got run time error 13 and when i debugged it highlighted that line
n = Application.Count(.Range(.Cells(2, 5), .Cells(UBound(a, 1), UBound(a, 2))))

I guess working with the example didnt help with my actual file. I have 11 columns before starting on the dates, i tried changing cells(2, 11) but it didnt work :P guess it more complicated than that. Can you help me please?

Thanks a million again

 

[hiker95]

rayray,

I guess working with the example didnt help with my actual file.

Can we have the workbook?

You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[rayray]

https://www.box.com/s/zg9fsekri5qhjxo1skn6

Here is the link to the file. Thanks a million

 

[hiker95]

rayray,

When I try to download/open your workbook I receive the following message:

Excel found unreadable content in Macrotest.xlsx.....

Please post another/smaller workbook. And, 225K is too big.