VBA 1004 Error Reference Range with Cells Formula-CodePudding

I'm trying to speed up a model, avoiding the use of indirect formula. Selecting a sheet based on the sheet name in 'Live' B3, then using a start and end row and column formula to copy the range and paste it in the 'LivePaste' Range back on the 'Live' tab. I've been trying to avoid select and activate if possible and finding that I keep running into various errors. Thanks, Max

Sub LiveCopyPaste()

Dim sht As String
Dim row_start As Long
Dim row_end As Long
Dim col_start As Long
Dim col_end As Long


    sht = Sheets("LIVE").Range("B3")
    row_start = Sheets("LIVE").Range("K4")
    row_end = Sheets("LIVE").Range("K5")
    col_start = Sheets("LIVE").Range("M4")
    col_end = Sheets("Live").Range("M5")
    
    Range("LivePaste") = Range(Sheets(sht).Cells(row_start, col_start), Sheets(sht).Cells(row_end, col_end))
 
    
    

End Sub

Expected this to copy the Range on the sht referenced tab and paste in in livePaste. This didn't happen, just keeps coming up with 1004 errors.

CodePudding user response：

Although there are more code lines - but it is easier to avoid errors if you are more explicit esp. regarding the worksheets you want to work with ...

Sub LiveCopyPaste()

Dim shtName As String
Dim row_start As Long
Dim row_end As Long
Dim col_start As Long
Dim col_end As Long

With Sheets("LIVE")
    shtName = .Range("B3")
    row_start = .Range("K4")
    row_end = .Range("K5")
    col_start = .Range("M4")
    col_end = .Range("M5")
End With
    

Dim wsSource As Worksheet
Set wsSource = ThisWorkbook.Worksheets(shtName)

Dim rgSource As Range
With wsSource
    Set rgSource = .Range(.Cells(row_start, col_start), .Cells(row_end, col_end))
End With

Dim rgTarget As Range
Set rgTarget = Application.Range("LivePaste")
With rgSource
    rgTarget.Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
 
End Sub

As I don't know if the "LivePaste" has the same "dimensions" as rgSource I am resizing it to the size of rgSource.