How do you record a macro that will copy data from a set area on your spread sheet and then paste it into the first available empty row, in an ever increasing list?
In the example below our data is imported into an area of our spread sheet in column B – the Sales Order Form (in yellow). We then wish to copy this data from the yellow area and transpose it as a new record in the first available empty row below any existing records – with the blue field headings in row 2.
Step 1:- Start your macro recorder with the setting set to Absolute mode
Step 2:- Select the cells to be transposed – cells B3:B11
Step 3:- Copy into system memory (Ctr+C)
Step 3:- Select the first cell in the existing table where the record is to be transposed – cell E2
Step 4:- Move the cursor down to the last record – press Ctr+ (down cursor)
Step 5:- Switch the recording to Relative mode
Step 6:- Click into the empty cell immediately below (in this example this happens to be E6)
Step 7:- Choose, Home à Paste à Transpose (as below)
Step 8:- Press Escape
Step 9:- Stop Recoding
So by starting at the top of the table and using the Ctr+down-key (in Absolute mode) followed by a switch to Relative mode and then clicking one cell lower, the replay is always able to locate the first empty row wherever it happens to be!
Written by Happy Trainer Chris James.
If you have any Excel questions or hints and tips you would like shared get in touch, use this link!
Remember – If you’ve been on a Happy Computers course you get 2 years free helpline access. Call the helpline on 020 7375 7373.