Excel Hints & Tips - Mixing Absolute + Relative mode

In: BlogDate: Jun 19, 2013By: Chris James

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?

Hi, we are Happy

We are leading a movement to create happy, empowered and productive workplaces.

How can we help you and your people to find joy in at least 80% of your work?

More about Happy

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 4: Select the first cell in the existing table where the record is to be transposed – cell E2

Step 5: Move the cursor down to the last record – press Ctr+ (down cursor)

Step 6: Switch the recording to Relative mode

 

 

Step 7: Click into the empty cell immediately below (in this example this happens to be E6)

Step 8: 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!

Why not sign up to our newsletter?

Sign up to our monthly newsletter, full of tips, tricks and news to help you to be happier and more productive at work.

Sign up here

Learn More Excel Tips in Our Online Training Courses

Our online training options are available as both 90-minute and full day courses to fit around your schedule. Each one is full of interaction with your trainer and other learners — just like our classroom training courses.

Our events are always focused on how we can help you to be more productive at work. Plus, pick and choose the packages you need, with sessions covering all Microsoft packages (including Word, PowerPoint and Outlook). And because the sessions are online, you can learn from home at a time best suited to you.

See all of our online IT sessions here

Why learn online with Happy?

  • Interactive and engaging - just like our classroom sessions
  • Bitesize or full-day sessions - fit around your schedule
  • Learn from home - all you need is a quiet place to call from and an internet connection
  • IT Helpline - 2 years of free support after your course
  • No quibble money-back guarantee

Chris James

Chris was an Associate Trainer for Happy between 2012 and 2016.

More by Chris

Testimonials from happy Customers