Using The Next Empty Column

[Disco3Stu]

Hello

I'm a complete novice when it comes to Macro's. I'm trying to copy a cell of data (F564)and a column of data (K564:K639) from one sheet and copy them to a cell and a column in a second sheet (currently C1 and C5-C80), but want to use the first empty column in the second sheet each time, not Column C. This would be updated daily, with the most recent data being in the right-most column.

I've been playing with offsets etc but cannot get it to work. Any help would be gratefully recieved!

My current code is:

Please Login or Register  to view this content.

Many thanks,

Stuart

[Mordred]

Hi Disco and welcome the best Excel forum on the net. You are violating forum rule #3 in that you need to wrap your code in code tags. Please read all of the forum rules and change your post accordingly. After you do that, someone will be more than happy to help you.

[MarvinP]

Hey Stuart, I have the answer but will get demerits if I tell you before you put code tags on.

Click the Green "Edit" button below your original post and then select "Go Advanced" below the message area. Then select your code and click on the "#" icon above the message area. This will put code tags around your code. Save and that will do it.

[Disco3Stu]

Thanks Marvin. All tidied up....

And rules read!

[MarvinP]

Cool,
The problem is with your

Please Login or Register  to view this content.

Before you do

Please Login or Register  to view this content.

You are turning off the selection so nothing is copied into the Windows Clipboard.

Remove that line and try again.

[Disco3Stu]

The copy/paste side of things is working ok, just the end location is wrong.

As it stands, every time I run the macro the correct data is inserted into Sheet 2 Column C.

What I would like to happen is the first time it is run the data goes to Column C, the next time Column D, then E, etc, always going into the next column.

[Disco3Stu]

A copy of the file....

The macro is run from sheet 2 (Times).

1st run should populate column C, 2nd run column D, etc.

[ZLPROG]

If you had a cell that is always populated with "paste data" for instance after pasting in column C cell 1 would always have something in it ect.. you could run a quick loop to determine the start value of your range.

Please Login or Register  to view this content.

You could then set your paste range to start at "cell 1,i" that would always be the first empty column and cell 1.

Didn’t look into all your stuff just thought this might be a quick fix for you.

[MarvinP]

Hi Stuart,

Find the attached with a new/modified macro. You've found the problem with recording Macros and then playing them back. Things move around and you need new locations. I hope the code comments will make you think and learn.

You need to almost memorize the lines of code below:

Please Login or Register  to view this content.

Also - your merged cells on the first sheet hurt you. The good Excel guys now almost never used merged cells. In the new code I don't think you need to unmerge cells as the new code only copies over a single cell.

I hope you will see how to deal with more data.

[Disco3Stu]

Marvin, thank you so much

The 'walk-through' is really great, I'd never have followed it otherwise. I'll spend some time looking through it today.

Unfortunately I cannot get away from the merged cells as they are merged when the data is supplied to me. As you've already seen from the macro I've already realised what a pain they can be!



Thank you for your time,


Stuart


PS:

and welcome the best Excel forum on the net.

Looks like you were right!!