J7House1984
New Member
- Joined
- Oct 30, 2020
- Messages
- 21
- Office Version
- 365
- Platform
- Windows
After receiving the Run-Time error '1004':
Method 'Resize' of object 'Range' failed
I am receiving the above error message for the following code. When I stepped into the code to debug it started at the snippet:
What am I doing incorrectly to cause the error message?
Method 'Resize' of object 'Range' failed
I am receiving the above error message for the following code. When I stepped into the code to debug it started at the snippet:
What am I doing incorrectly to cause the error message?
VBA Code:
Option Explicit
Sub copyData()
Dim wb As Workbook
Set wb = ThisWorkbook
Dim tgt As Worksheet
Set tgt = wb.Worksheets("HealthgradesUploadTemplate")
Dim sLast As Long
Dim tLast As Long
' Copy from InQuicker
With Worksheets("InquickerData")
sLast = .Range("M:AB") _
.Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
tLast = sLast - 1
' Copy "MRN" Data
tgt.Range("B8").Resize(tLast).Value = .Range("BR2:BR" & sLast).Value
' Copy "First Name" Data
tgt.Range("D8").Resize(tLast).Value = .Range("S2:S" & sLast).Value
' Copy "Last Name" Data
tgt.Range("E8").Resize(tLast).Value = .Range("U2:U" & sLast).Value
' Copy "Middle Initial" Data
tgt.Range("F8").Resize(tLast).Value = .Range("T2:T" & sLast).Value
' Copy "Birthdate" Data
tgt.Range("G8").Resize(tLast).Value = .Range("AA2:AA" & sLast).Value
' Copy "Email" Data
tgt.Range("H8").Resize(tLast).Value = .Range("Y2:Y" & sLast).Value
' Copy "City" Data
tgt.Range("J8").Resize(tLast).Value = .Range("V2:V" & sLast).Value
' Copy "State" Data
tgt.Range("K8").Resize(tLast).Value = .Range("W2:W" & sLast).Value
' Copy "Zip" Data
tgt.Range("L8").Resize(tLast).Value = .Range("X2:X" & sLast).Value
' Copy "Gender" Data
tgt.Range("P8").Resize(tLast).Value = .Range("AB2:AB" & sLast).Value
' Copy "Registration Date" Data
tgt.Range("U8").Resize(tLast).Value = .Range("M2:M" & sLast).Value
End With
' Copy from Control Panel.
With wb.Worksheets("Control Panel")
' Copy "Lead Source" Data
tgt.Range("Q8").Value = .Range("E6").Value
' Value "MAC ID" Data
tgt.Range("S8").Value = .Range("E8").Value
' Value "Status" Data
tgt.Range("T8").Value = .Range("E10").Value
End With
' Copy in ActiveSheet.
With wb.Worksheets("HealthgradesUploadTemplate")
tLast = .Cells(.Rows.Count, "D").End(xlUp).Row
.Range("Q8").AutoFill Destination:=.Range("Q8:Q" & tLast)
.Range("S8").AutoFill Destination:=.Range("S8:S" & tLast)
.Range("T8").AutoFill Destination:=.Range("T8:T" & tLast)
End With
End Sub