Only copy Column B (INCLUDING THE TITLE ROW) to the last row of data in Column A

[VBAProIWish]

Hello All,

I have this code below that copies Column B, but it starts in cell B2. I need it to copy the title row (Cell B1) as well.

 .Range("B2").Copy Destination:=.Range("B2:B" & .Range("A" & Rows.Count).End(xlUp).Row)

I tried this...

 .Range("B:B").Copy Destination:=.Range("B:B" & .Range("A" & Rows.Count).End(xlUp).Row)

...but it didn't work.


Does anyone know how to copy a column (including the title row; row 1) but only copy down to the last row of data in column A?

Thanks much

 

[Joe4]

OK, I think you described where you want to copy from, but where exactly are you trying to copy this to?

 

[VBAProIWish]

Ohhh, let's say column C.

Sorry about that

 

[Jonmo1]

Try

lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("B1:B" & lr).Copy Range("C1")

 

[My Aswer Is This]

Assuming from your Title this script will copy all values in column "B" and paste after last row with data in column "A"

Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1)

 

[My Aswer Is This]

Your title says:
Only copy Column B (INCLUDING THE TITLE ROW) to the last row of data in Column A

Ohhh, let's say column C.

Sorry about that

 

[Joe4]

Your title says:
Only copy Column B (INCLUDING THE TITLE ROW) to the last row of data in Column A

I took that to mean when determining how many rows in column B to copy, only go down as far as the last row of data found in column B.
Not to copy the data to column A...

 

[Jonmo1]

It seems to me that
'To the last row of data in Column A' is meant to determine the last row to copy, it's not referencing where to paste.

 

[VBAProIWish]

Sorry for the confusion everyone. My thread seemed to create a lot of discussion.

So, the whole reason I need to do this is because, initially, I would copy a column, paste it and then do a mail merge on it. The problem is that my mail merge had a bunch of blank rows; which I assume were caused by doing a simple "copy column AN" and paste column AN to column AP.

Although the number of rows are dynamic each time I run the report, this particular time I only had about 45 rows. However, "CTRL+END" brought me to the last cell which was in AN135 (or possibly AP135, can't recall)!

So, I knew I simply couldn't copy column AN and paste it to column AP, because for some reason, it was populating cell AN135 with something. I tried goto/special/delete rows among other things and this would also defeat the purpose.

Therefore, I thought the best solution was to simply copy column AN, (down to the last row of data in column A because column A is always contiguous data and no data is ever below there) and paste the relevant data in column AN to column AP.

All the code that you all provided will definitely assist me in doing this.

Thanks much to all and feel free to ask me any more questions if you are curious about anything else or have any further ideas.

 

[My Aswer Is This]

Probable best to tell us where your data is now and where you want it pasted to.

First we were talking about column "A" and "B" but now your also mentioning column "AN" and "AP"

Is it possible your wanting us to look in all columns from B to AP and any cell with values copy that cell value into column "A" ?