VBA Loop not error

[philipk55]

Hi there,

I have a script which rolls accounting periods from one period into the next keeping the formulas while paste valuing the previous period. The script also excludes certain sheets which are working fine.

However, the script returns an error at the below line (in red bold) on the last sheet and is not finishing out fully. Any ideas why I am getting an error here? The script is doing what i want it to do but is just stopping at this point and running an error.

Many thanks in advance,

Philip

VBA SCRIPT

Sub LastColumn()




Dim ws As Worksheet


Application.ScreenUpdating = False


For Each ws In ActiveWorkbook.Worksheets


If ws.Name <> "FY17 Input" And ws.Name <> "SSC Working" And ws.Name <> "Will" And ws.Name <> "Michael" Then


ws.Activate

With ws
Range("A1").End(xlToRight).Select


ActiveCell.EntireColumn.Select
ActiveCell.EntireColumn.Copy


ActiveCell.Offset(0, 1).PasteSpecial xlPasteFormulasAndNumberFormats
ActiveCell.Offset(0, -1).Select
ActiveCell.EntireColumn.Select
ActiveCell.EntireColumn.PasteSpecial xlPasteValuesAndNumberFormats





End With

End If



Next ws




ActiveWorkbook.Sheets("Sheet1").Activate




Application.ScreenUpdating = True










End Sub

 

[Joe4]

You need to look at the two lines in red in conjunction with one another:

[COLOR=#ff0000]Range("A1").End(xlToRight).Select[/COLOR]


[COLOR=#333333]ActiveCell.EntireColumn.Select[/COLOR]
[COLOR=#333333]ActiveCell.EntireColumn.Copy[/COLOR]


[B][COLOR=#ff0000]ActiveCell.Offset(0, 1).PasteSpecial xlPasteFormulasAndNumberFormats[/COLOR][/B]

If there is nothing in row 1 after column A, the first highlighted red line will take you to the last possible column on your sheet in row 1.
The second line in red is trying to move over one column to the right. However, if you are already in the last possible column, you cannot move over any more columns to the right.
Hence, your error.

I find to search for the last populated column in row 1, this is a more effective method:

Cells(1, Columns.Count).End(xlToLeft).Select

Even if there is nothing at all in row 1, that will go to column A, and moving one column to the right will take you to column B.
So, you may skip over column A in this instance, but at least it won't error out (you could add extra code to account for that and avoid blank columns).

 

[philipk55]

Worked a charm, cheers