Easy VBA.. Copy + Paste formatting...error 404?

[billyheanue]

Hi everyone. I have code that succeeds in expanding the current selection (CurrentRegion) to include one more column. It does that great. But when I added in lines (4) and (8), i get a error 404.

Heres what I got:

Private Sub CommandButton3_Click()Set Rng = Range("L5").CurrentRegion
Rng.Select
Sheets(1).Rng.Copy
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.resize(numRows + 0, numColumns + 1).Select
Sheets(1).CurrentSelection.PasteSpecial xlPasteFormats


End Sub

is something wrong with "Sheets(1).Rng.Copy" or "Sheets(1).CurrentSelection.PasteSpecial xlPasteFormats" ?
I am trying to copy the old formatting, and later paste it into the new selection once the current region has been expanded.

Thanks everyone,
Bill

 

[Technowolf]

Try just Rng.Copy without the Sheet(1)

and change
Sheets(1).CurrentSelection.PasteSpecial xlPasteFormats

to

Selection.PasteSpecial xlPasteFormats

 

[revcanon]

Test this on a copy of your document.

Rng.Copy
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 0, numColumns + 1).Select
Selection.PasteSpecial Paste:=xlPasteFormats