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

Report
Posts
2
Registration date
Wednesday October 15, 2014
Status
Member
Last seen
October 22, 2014
-
Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
-
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.

2 replies

Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
455
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
Posts
2
Registration date
Wednesday October 15, 2014
Status
Member
Last seen
October 22, 2014

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.
Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
455
So you provide sample data and I write a code for you.
Then you say in doesn't work and provide different sample data.

Of course it doesn't work, macro's are tailored for specific situations.

Taking a look at your new sample, where does the date come from?

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!