Speed up Copy/Paste with variable range size

[willywonka11]

I need to speed up my code. This code is looped through hundreds of times and is super slow. The problem is that the range of cells I need to copy and paste changes.

Here is my original code:
Worksheets(Sht2).Activate
Worksheets(Sht2).Range(Cells(13, 1), Cells(LastPt, 15)).Copy Worksheets(ShtName).Activate
Worksheets(ShtName).Range(Cells(13, 1), Cells(13, 1)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Here is the code that I can't get to work:
Worksheets(ShtName).Range(Cells(13, 1), Cells(LastPt, 15)).Value = Worksheets(Sht1).Range(Cells(13, 1), Cells(LastPt, 15)).Value

What is frustrating is that I have been able to get this code to work but I need to be able to change the number of rows being copied.:
Worksheets(ShtName).Range(Cells(1, 1), Cells(60, 15)).Value = Worksheets(Sht1).Range("A1:O60").Value

 

[JLGWhiz]

Unless you particularly like the flicker and flash, you don't need to use the 'Activate' or 'Select' syntax. You can just use direct coding.

Sheets(Sht2).Range("A13:O" & Sheets(Sht2).UsedRange.Rows.Count).Copy
Sheets(ShtName).Range("A13").PasteSpecial xlPasteValues

You are most likely having difficulty because when you use the Cells property to define your range, it has to be qualified for each use like:

Worksheets(ShtName).Range(Worksheet(ShtName).Cells(1, 1), Worksheet(ShtName).Cells(60, 15)).Value =

Or a shorter version using With statement

With Worksheets(ShtName)
 .Range(.Cells(1, 1), .Cells(60, 15) = Sheets(Sht1).Range("A1:O60").Value
End With

Where the periods tie the Range and Cells to the parent worksheet

 

[willywonka11]

Thanks so much JLGWhiz! I got it to work. I'm sure I will also use "UsedRange.Rows.Count" a bunch in the future!