VBA copy values to newly created sheet problem

**dusoo** · 07-01-2013, 07:19 PM

Hi guys,

I'm trying to copy content of one sheet to newly created sheet. The copy&paste code I created using "record macro" option.
Code below fails on the line "Sheets(wsName).Select" with error 400. (select method of class Range failed)
Could you please write what's wrong with that?

Thanks

    wsName = Sheets("Sheet1").Range("I7")
   
    Dim WS As Worksheet
    If WorksheetExists(wsName) Then
       Sheets(wsName).Delete
       Set WS = Sheets.Add(After:=Sheets(Worksheets.Count))
       WS.Name = wsName
    Else
       Set WS = Sheets.Add(After:=Sheets(Worksheets.Count))
       WS.Name = wsName
    End If
    
    Sheets("Sheet1").Range("A1:J10").Select
    Range("J10").Activate
    Selection.Copy

' HAVE PROBLEM HERE - ERROR 400
    Sheets(wsName).Select

    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste

**xladept** · 07-01-2013, 07:46 PM

Try:

Sheets("Sheet1").Range("A1:J10").Select
    Range("J10").Activate
    Selection.Copy
wsName = Sheets("Sheet1").Range("I7")
   
    Dim WS As Worksheet
    If WorksheetExists(wsName) Then
       Sheets(wsName).Delete
       Set WS = Sheets.Add(After:=Sheets(Worksheets.Count))
       WS.Name = wsName
    Else
       Set WS = Sheets.Add(After:=Sheets(Worksheets.Count))
       WS.Name = wsName
    End If
    
        WS.Select

    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste

**dusoo** · 07-01-2013, 07:49 PM

I get same error.
Is there another way to copy & paste?
I have tried this code below, but xlPasteColumnWidths does nothing, replacing it by xlPasteValues helps, but no format is copied over which is bad.
Any ideas?

Set r1 = Sheets("Sheet1").Range("A1:J10")
Set r2 = Sheets(wsName).Range("A1:J10")

r1.Copy
r2.Offset(0, 0).PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

**xladept** · 07-01-2013, 08:14 PM

Try this:

Sub DUSOO(): Dim wsName As String, WS As Worksheet
Sheets("Sheet1").Range("A1:J10").Copy
    wsName = Sheets("Sheet1").Range("I7")
       
   
    For Each WS In Worksheets
    If WS.Name = wsName Then
       Sheets(wsName).Delete: End If: Next
       
       Set WS = Sheets.Add(After:=Sheets(Worksheets.Count))
       WS.Name = wsName
       
        WS.Range("A1").PasteSpecial xlPasteColumnWidths
        WS.Range("A1").PasteSpecial xlPasteAll
        
End Sub

**dusoo** · 07-01-2013, 10:06 PM

Thanks, it worked. So I don't have to Select newly created sheet...

**xladept** · 07-01-2013, 10:32 PM

You're welcome!

No, the newly created sheet is always the active sheet.

(Don't forget to mark this thread as solved.)

VBA copy values to newly created sheet problem

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA copy values to newly created sheet problem

Re: VBA copy values to newly created sheet problem

Re: VBA copy values to newly created sheet problem

Re: VBA copy values to newly created sheet problem

Re: VBA copy values to newly created sheet problem

Re: VBA copy values to newly created sheet problem

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions