Copy Data from Column and Paste to Next Blank Column on 2nd Sheet

**adammark** · 04-16-2010, 01:34 PM

I am using Excel 2003
Sheet 1: A1:A20 Contain Questions, B1:B20 is where we will insert answers

I want to be able to push a button linked to a macro that will copy/paste the range b1:b20 and paste to the next empty column on sheet 2, starting with column B.

Ideally this macro would then blank out the cells b1:b20 on sheet 1

Please any help would be appreciated, I am very inexperienced.

**mdbct** · 04-16-2010, 02:16 PM

Thy this:

Please Login or Register  to view this content.

**Marcol** · 04-16-2010, 02:38 PM

You could try this

Please Login or Register  to view this content.

This will
1/. Copy Sheet1.Columns("B") to Next free column in Sheet2
2/. Number the Column Header Answer 1, 2, 3, etc
3/. Hide all columns with answers
4/. Finally clear the answers from Sheet1

Hope this is of some help.

**adammark** · 04-19-2010, 10:59 AM

Thanks for the help. I ended up going with teh first solution seemed simpler. Though I'd still like to add 1 mor eline that clears the responses on Sheet 1 range b1:b20

**mdbct** · 04-19-2010, 11:05 AM

The data should be cleared from B1:B20 on sheet 1 using the Cut command. Is it still there?

**Marcol** · 04-19-2010, 11:15 AM

Both solutions clear B1:B20

My solution allows for any amount of Questions and Answers, ie not limited to 20, and also hides previous answers on "Sheet 2"

Cheers