Previous Topic Next Topic

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

Closed
ospeng Posts 2 Registration date Wednesday October 15, 2014 Status Member Last seen October 22, 2014 - Oct 15, 2014 at 05:41 PM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Oct 28, 2014 at 11:55 AM
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

Answer 1 / 2
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Oct 16, 2014 at 11:03 AM
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
0
Answer 2 / 2
ospeng Posts 2 Registration date Wednesday October 15, 2014 Status Member Last seen October 22, 2014
Oct 22, 2014 at 03:07 AM
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.
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Oct 28, 2014 at 11:55 AM
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?
0