Working on last populated column

[mgcf]

I have a few sheets I need to work on daily that involve dragging the formulas of the last populated column (e.g column C) to the next column (column D), then copying and pasting the values of column D onto itself. Then the next day I would do the same with column D (dragging the formulas to column E). I use macros and VB for some of the other things but I do this task manually everyday for each sheet because I don't know how to program visual basic to take the last column each time.

[DonkeyOte]

You can approach this type of thing a number of ways... one might be:

Sub Example()
    With Sheets("sheet1")
        With .UsedRange.Columns(.UsedRange.Columns.Count)
            .Copy .Offset(, 1)
            With .Offset(,1)
                .Value = .Value
            End With
        End With
    End With
End Sub

but it all rather depends on setup etc...use of UsedRange for ex. is open to error and is not therefore always the best best approach
you might opt for an End(xlToLeft) type approach instead but again that depends on specifics of layout which we're not privvy to

edit:
for a robust approach for establishing last known values etc see: http://www.rondebruin.nl/last.htm

[mgcf]

Not sure what that code does, does it drag the column or copy and paste it's values onto itself? (or both?) And what if I only want to use a certain number of rows?

[DonkeyOte]

Not sure what that code does

If in doubt test... step through with F8
the above code takes the last column of the used range [used rows] and pastes to next column - then applying a values over current content [should it be formulae etc...]

...what if I only want to use a certain number of rows?

If you want help with specifics post a sample file to better illustrate requirements.

[mgcf]

Ok I have attached an example. The last column in Sheet1 takes values from Sheet2.
In this file I would need to drag the formulas from F1:F7 to G1:G7, then paste F1:F7's values onto themselves. Then the next day do the same with G1:G7 and H1:H7.

Book2.xlsx

[DonkeyOte]

OK so assuming formulae should persist in "new" column with prior column being "static", adapting earlier example slightly:

Sub Example()
    With Sheets("sheet1")
        With Intersect(.UsedRange.Columns(.UsedRange.Columns.Count), .Rows("1:7"))
            .Copy .Offset(, 1)
            .Value = .Value
        End With
    End With
End Sub

the above is just a basic proof of concept

[mgcf]

That's great cheers! Could you also tell me what the code would be if I wanted the formulas/values to "continue" for example in the case of the date or if I wanted the vlookup to take the next column (e.g. B1:H30) or would that just be a case of removing the $ sign?

[DonkeyOte]

For the dates and consistency it would be simplest to use a formula, eg:

F2: 
=E2+1

For the column references - yes you would remove the $ preceding the reference to G however you would also want to adjust such that the col_index_num incremented also... so:

F3:
=IF(COUNTIF(Sheet2!$A$1:$A$30,$A3)=0,"",VLOOKUP($A3,Sheet2!$A$1:G$30,COLUMNS(Sheet2!$A$1:G$1),FALSE))
copied down

though you could just as easily use an INDEX/MATCH construct

However, what I would suggest [based on the example file] is that given you're returning numerics a SUMIF would be far more straightforward:

F3:
=SUMIF(Sheet2!$A$1:$A$30,$A3,Sheet2!G$1:G$30)
copied down

note in all of the above the hardwired reference to "cat", "dog" etc is replaced by a simple relative cell reference such that you can apply the same formula to all rows without need for modification.

[mgcf]

Thanks! But how could I get the date to increase automatically from the last column? As with the other data, I wouldn't be able to refer to F3 and E3 specifically because each day the column reference would increase

[DonkeyOte]

If the "current" column contains the formula suggested previously [should refer to row 2 not 3 - F2: =E2+1] then when the code is run to generate a new "current" column that formula reference will adjust given use of relative references.

It might be worth reading up on relative/absolute/mixed referencing.

[mgcf]

Ok thanks a lot for the info!

[mgcf]

Could someone give me the revised code to the following, if I would also like to format painter from the last populated column to the next one and if I would like the date to increase by one day, without knowing the cell references?

[mgcf]

this code:

Sub Example()
With Sheets("sheet1")
With Intersect(.UsedRange.Columns(.UsedRange.Columns.Count), .Rows("1:7"))
.Copy .Offset(, 1)
.Value = .Value
End With
End With
End Sub