Copy Data Over Based on Another Cell

[hgraybill]

I'm trying to create a macro in Excel that will copy data from a couple of cells (if another cell is equal to true) and paste the copied data into the first blank cell in another sheet.

So basically (because I'm sure the above isn't very clear!), if cell K16 is equal to true, copy cells B16 and C16 (from sheet "SALES") to the first blank cell in column A on the sheet called planning. The macro would go down column k until it checked all the values.

The copying part isn't what I'm struggling with. I'm having a hard time consistently getting the data into the first blank cell all the time.

Does anyone have any suggestions?

Thanks in advance!
Heather

 

[SkipVought]

hi,

I'm having a hard time consistently getting the data into the first blank cell all the time.

somerange.copy Destination:=sheets("planning").[A1].End(xldown).offset(1)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hgraybill]

That almost works perfectly, except my range starts at A1 and it is pasting the value into A27. A3 is the first blank cell.

 

[SkipVought]

If you select A1 and hit keys [END] [DOWN ARROW], what happens?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hgraybill]

The selected area jumps down one cell, so basically moves to the next cell in the column.

 

[SkipVought]

Did you punch [END]

Remove your finger.

punch [DOWN ARROW]?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hgraybill]

Yes, it skips to A2 when I do that. If there isn't anything in A3, it will then go to A25.

 

[hgraybill]

I meant it will go to A25 if I hit those keys again.

 

[SkipVought]

Yes, it skips to A2 when I do that. If there isn't anything in A3, it will then go to A25.

I'm sorry, I am not understanding.

Please copy 'n' paste your range from A1:A25 AND explain what happens.

IF you have NOTHING in A3, your activecell will STOP on A2.

If A1:A25 are NOT EMPTY, your activecell will STOP on A25.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hgraybill]

I don't want to waste your time on this little piece anymore. You helped me a lot already with that line of code that I was missing.

I'll figure out the rest! Thanks so much for the help!