Error After Pasting Data Into New Sheet 3

[cghoga]

Hello,

I am geting the run time error 1004 'Select Method of Range Failed'.

I am attempting to copy data from the active workbook and then paste the data into a new workbook.

It is pasting fine, but when I try to select row 1 to bold the header the code errors out.

Here is the code:

Range("A1:E1").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("1:1").Select (<--IT BOMBS HERE)
Selection.Font.Bold = True
Cells.Select
Cells.EntireColumn.AutoFit

Thanks for your help!

 

[SkipVought]

Hi,

You must reference you workbook/sheet objects...

Dim wb As Workbook
'!!! avoid using the Select or Activate methods !!!
ActiveSheet.Range("A1:E1").Copy
Set wb = Workbooks.Add
wb.Sheets(1).Cells(1, 1).PasteSpecial _
    Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
    
With wb.Sheets(1)
    .Rows("1:1").Font.Bold = True
    .Cells.EntireColumn.AutoFit
End With
Set wb = Nothing

Skip,

_[red][/red]

 

[PHV]

what about this ?

Dim s1 As Worksheet, s2 As Worksheet
Set s1 = ActiveSheet
s1.Range("A1:E1").Copy
Workbooks.Add
Set s2 = ActiveSheet
s2.Range("A1:E1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
s2.Rows("1:1").Font.Bold = True
s2.Cells.EntireColumn.AutoFit

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cghoga]

Thanks very much!