Pull data from changing column each day

[ckserra]

I have a pretty complicated (for me) spreadsheet that I am working on. I am stuck on the final piece. Each day, sales are updated in a spreadsheet, and it creates an additional column. In one of my macros, I go to the sales tab, click on a blank cell, and than CMD-Left Arrow to get to the last cell in the column, and than copy the copy and have the macro do its work. Problem is the macro doesnt see it as the last column, it sees it as column F or whatever. Is there a way to have it pull data from the last column, which changes each day in a macro?

Range("P2").Select
Selection.End(xlToLeft).Select
Range("H2:H5").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-1
Range("I2").Select
ActiveSheet.Paste
Application.CutCopyMode = False

The H2:H5 is the issue. Each day a column will be added, so tomorrow it will I, than J, etc. I thought by clicking on a cell than CTRL and left arrow would "trick" the macro into doing what I want, but I am incorrect.

This is what Im (trying to) use now.

[MarvinP]

Hi ckserra,

Watch this:

Dim LastColumn as Double
Dim LastRow as Double
LastColumn = Cells(1,Columns.Count).End(xlToLeft).Column ' This will give column of last data entered in row 1
LastRow = Cells(Rows.Count,LastColumn).End(xlDown).Row ' This will give last row (or first blank row) in the last column

Range(Cells(1,LastColumn),Cells(LastRow,LastColumn).Select

Cells(2,LastColumn + 1).Select

I hope the above gives you enough code to do what you need.

I'm not sure it is exactly what you need for your problem but should give you some ideas.

[ckserra]

Great, thank you very much Marvin! I am very new to vba, but have used macros for a while. I will give this a shot.

Thanks again!

Chris

[ckserra]

Ok so I can get i to select a cell in the last column, but what I need is for it to copy the entire column, and I am struggling with that. Any help would be much appreciated.