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.
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.
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:
