Copy Cells to next blank column in another sheet

[bdadswell]

Hello;

I have a sheet where I want to take B4:B12 and have a macro that copies those cells into another sheet, row 3, but to the next blank column. So if the last time the user ran the macro, it went into column "B", the next time it should go to column "C" and so on...

Any ideas?

Thanks

 

[BMRC]

Hi,
You could try a count function on row 1 to find your last cell in row 1 that has data, add "1" to it and use that as your location reference for your past.

See if this does what you were looking for:

[FONT=Calibri]Sub Macro1()
Dim BlankC As Variant
Sheets("Sheet2").Activate
BlankC = Application.WorksheetFunction.CountA(Rows(1))+1
    Range("Sheet1!B4:B12").Copy
    Sheets("Sheet2").Activate
    Cells(1, BlankC).Select
    ActiveSheet.Paste
End Sub[/FONT]

 

[bdadswell]

I'm getting a subscript out of range error.

 

[BMRC]

Which line of the code are you getting the error on?

 

[bdadswell]

Which line of the code are you getting the error on?

Here's the updated code:

Sub Macro1()
Dim BlankC As Variant
Sheets("Weekly_Report").Activate
BlankC = Application.WorksheetFunction.CountA(Rows(1)) + 1
Range("Weekly_Report!B4:B12").Copy
Sheets("Week_Over_Week").Activate
Cells(1, BlankC).Select
ActiveSheet.Paste
End Sub

I'm getting a "400" error now - I fixed the other error.

 

[BMRC]

Is there anything hidden or protected on either of these sheets?

 

[bdadswell]

No - I just double checked.

 

[BMRC]

Hmm. I cannot duplicate your error on my end.

 

[bdadswell]

Let me try moving the macro from the worksheet to the entire workbook and see if that helps...

 

[bdadswell]

OK - I moved it - it kind of works - it's putting the data into A1 in the new sheet, overwriting the data that was there.