CODEWITHSUNDEEP
excelvbaselectcopypaste
Greg
Greg

Reputation: 43

Why does the Range method Select work the first time but not the second

Sub History1()
'
' History Macro
'

'
Dim sDate As String
    Application.ScreenUpdating = False

    sDate = Sheets("Summary").Range("P1").Value
    If Not WorksheetExists(sDate) Then
        Sheets.Add.Name = sDate
    End If
    Sheets(sDate).Visible = True
    Sheets(sDate).Cells.UnMerge
    Sheets("Summary").Range("A1:Z100").Select
    Selection.Copy
    Sheets(sDate).Range("A1:Z100").Select
    Selection.Paste
    
    Sheets(sDate).Visible = False
    Sheets("Summary").Cells(3, 1).Select
    
    Application.ScreenUpdating = True
'    MsgBox ("Done")
End Sub

The value in sDate is the string 05_14_21 and works fine when used earlier in the sub. The first instance of Range.Select operation works fine, at least I think it does. No error generated. The second one says "Select method of Range class failed". The worksheet "05_14_21" is visible at the time of the 2nd operation. I am at a loss. Any insights will be appreciated.Th

Upvotes: 0

Views: 56

Answers (2)

Toddleson
Toddleson

Reputation: 4457

You cant use .Select on a range of a sheet that isn't active. It will give you Run-Time Error 1004. The fix would be to first select that sheet, then select the range. But honestly, using .Select is unnecessary and will make the screen jitter around while the macro is running. .Select also slows down your macro, which becomes very noticeable when you start using loops in your code.

Instead I would suggest directly referencing your ranges instead of using .Select like so:

Sub History1()

Dim sDate As String
    Application.ScreenUpdating = False

    sDate = Sheets("Summary").Range("P1").Value
    If Not WorksheetExists(sDate) Then
        Sheets.Add.Name = sDate
    End If
    Sheets(sDate).Visible = True
    Sheets(sDate).Cells.UnMerge
    Sheets("Summary").Range("A1:Z100").Copy Destination:= Sheets(sDate).Range("A1:Z100")
    
    Sheets(sDate).Visible = False
    
    Application.ScreenUpdating = True
'    MsgBox ("Done")
End Sub

Upvotes: 2

Maya
Maya

Reputation: 134

I am not sure why you need to select Cells(3,1), but in order to do that you need to activate the correct sheet. As pointed out in another answer, using select is not advisable. This also results in a more concise way to copy and paste.

    Dim sDate As String
    Application.ScreenUpdating = False

    sDate = Sheets("Summary").Range("A1").Value
    If WorksheetExists(sDate) = False Then
        Sheets.Add.Name = sDate
    End If
    Sheets(sDate).Visible = True
    Sheets(sDate).Cells.UnMerge
    
    Sheets("Summary").Range("A1:Z100").Copy _
    Destination:=Sheets(sDate).Range("A1:Z100")
    
    Sheets(sDate).Visible = False
    Sheets("summary").Activate
    Sheets("Summary").Cells(3, 1).Select
    
    Application.ScreenUpdating = True

Upvotes: 0

Related Questions