VBA 1004 error

[Richard in Redditch]

Hello.
I am sure this problem has come up before, but as a tyro I just don't know where my code is going wrong. I an trying to set up a macro, which will copy some data from a summary sheet into another sheet in the same workbook. Here is the code which is giving me angst:

' Add the new sheet
    
    ActiveWorkbook.Sheets.Add After:=Sheets(LastShtNo)
   
' Copy heading and formatting to new sheet
    Sheets(LastShtNo).Range("2:2").Copy Sheets(NewShtNo).Range("2:2")
    Sheets(LastShtNo).Range("D4:G36").Copy Sheets(NewShtNo).Range("d4:g36")
    Sheets(LastShtNo).Range("c36").Copy Sheets(NewShtNo).Range("c36")
    Range("d4:e35").ClearContents
    Sheets(NewShtNo).Range("2:2").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
' Get data

The system (Xl 2013) falls over at the underlined line with error "1004: Select error of Range class failed".

Assistance would be greatly appreciated!

 

[RoryA]

You can't select a range unless it's sheet is active, but I can't see a need to select the range there. Just use:

Sheets(NewShtNo).Range("A2").PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 

[Richard in Redditch]

Hello Rory. Many thanks for your prompt reply.

Unfortunately, when I tried your modification, Excel fell over at the same place with the error "PasteSpecial method of Range class failed". Where am I going wrong?

Regards

 

[Fluff]

Try

    Sheets(LastShtNo).Range("2:2").Copy Sheets(NewShtNo).Range("2:2")
    Sheets(LastShtNo).Range("2:2").Copy
    Sheets(NewShtNo).Range("2:2").PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Sheets(LastShtNo).Range("D4:G36").Copy Sheets(NewShtNo).Range("d4:g36")
    Sheets(LastShtNo).Range("c36").Copy Sheets(NewShtNo).Range("c36")
    Range("d4:e35").ClearContents

 

[Richard in Redditch]

Hello Rory and Fluff

Many thanks again for your help with my problem, which - I am very happy to say - is no longer a problem! Thank you Fluff - your solution has done the trick.

Btw, just why should basically reordering the order of the statements cause so much heartache? I had started on that piece of code about lunchtime and tried (almost) everything until I made the first posting. Is it an Excel "feature" - i.e. bug - or what? I could not find any reference in my VBA books.

Once again, sincerest thanks.

 

[Fluff]

Glad we could help & thanks for the feedback.

I suspect the reason it wasn't working, is that the last thing you copied was C36 (a single cell), but you then tried to pastespecial on the entire row 2, which means your copy & paste ranges were different sizes.

 

[Richard in Redditch]

Ah - I see. I'll know now for the future. Many thanks again!

 

[RoryA]

I had amended that to only paste to the first cell, so I suspect the problem (which I overlooked) was that the ClearContents line was clearing the clipboard.

 

[Richard in Redditch]

Many thanks Rory. Another advancement in my Excel knowledge!