How to copy data from rows with value to a new sheet using macro Closed

Question

I have a massive excel spread sheet that I am using to keep track of sales. 
I would like a formula or function that will take both the headers along with values in rows, and columns then add info to new sheet2 as shown below. The amount of rows and columns either increase or decrease based on the ID for sales team every time I receive the report

sample of sheet1 raw data:
	  DATE	          INTERNET	IN-PERSON
1004	  1/1/2014	           $10.00 	        $50.00 
1007	  1/1/2014	           $40.00 	        $60.00 

Results in Sheet2 should look like this:
ID	   Code	         Amount	Date
1004	   INTERNET	 $10.00 	1/1/2014
1007	   IN-PERSON	 $40.00 	1/1/2014

Any help would be appreciated I am new to working with macro, and the spreadsheet is massive. I also need the ability to run this bi-weekly.

TrowaD · Answer

Hi Ospeng,

Manually create the header in sheet2, then run the following code:
Sub RunMe()
Dim iCol As Long

iCol = 2
Sheets("Sheet1").Select

Do
    Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(1, iCol)
    iCol = iCol + 1
Loop Until IsEmpty(Cells(1, iCol))

For Each cell In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = cell
    Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = cell.Offset(0, 2)
    Sheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = cell.Offset(0, 1)
Next cell

End Sub

How to implement and run a code:

- From Excel hit Alt + F11 to open the "Microsoft Visual Basic" window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro's.
- Double-click the macro you wish to run.
NOTE: macro's cannot be reversed using the blue arrows. Always make sure you save your file before running a code, so you can reopen your file if something unforeseen happens.

Best regards,
Trowa

ospeng · Answer

Thank you again for your help, but I am having trouble with the code. The codes are not copying down for all sales rep. I am a novice to macro. Below is a sample of the raw data:

ID	  10	      55	        65	       85
3351	    -   	 -   	            -   	 24.00 
3356	    -   	 1,200.00 	     -   	         -   
3357	     -   	 -   	              -   	      -   
3358	    -   	 1,200.00 	       -   	 -   
3360	    -   	 1,200.00 	         -   	 -   
3361	    -   	 -   	 -   	 24.00 
3362	    -   	 636.66 	     -   	 19.17 
3363	    -   	 300.00 	    -   	 24.00 
3364	    -   	 300.00 	     -   	 8.00 
3365	    -   	 1,050.00 	     -   	 -   
3366	    -   	 1,050.00     	 -     	 -   

The result should look something like this for all ID's:
ID	Code	Amount	Date
3351	   10	0	9/12/2014
3351	   55	0	9/12/2014
3351    65	0	9/12/2014
3351    85	24	9/12/2014
3356    10	0	9/12/2014
3356	   55	1200	9/12/2014
3356	   65	0	9/12/2014
3356    85	0	9/12/2014

If you could explain what the macro is doing would also help. Your help is greatly appreciated.

How to copy data from rows with value to a new sheet using macro

2 responses

Similar discussions

Subscribe To Our Newsletter!