CODEWITHSUNDEEP
excelvba
Ghulam Murtaza
Ghulam Murtaza

Reputation: 13

Application defined or object defined error (named ranges)

Im stumped at this point with my code here, im getting this generic error at the line where its supposed to copy and paste the selected range after creating the worksheet but its giving me this frustrating error - i dont know what im doing wrong here, please help guys, all help appreciated :)

Private Sub ExpBttn()

Dim WrkShtExists As Boolean
Dim Sht As Worksheet
Dim x As Integer
Dim TgtRngR As Range
Dim TgtRngB As Range
Dim TgtNme As String

Select Case Multi
Case MultiPage1.Value = 0
    Set TgtRngR = Sheets("Tracker").Range("U2:Z19")
    Set TgtRngB = Sheets("Tracker").Range("U21:Z26")
    TgtNme = "P4PSoft"
Case MultiPage1.Value = 1
    Set TgtRngR = Sheets("Tracker").Range("AB2:AG19")
    Set TgtRngB = Sheets("Tracker").Range("AB21:AG26")
    TgtNme = "P4PHard"
Case MultiPage1.Value = 2
    Set TgtRngR = Sheets("Tracker").Range("AP2:AU19")
    Set TgtRngB = Sheets("Tracker").Range("AP21:AU26")
    TgtNme = "RCI"
Case MultiPage1.Value = 3
    Set TgtRngR = Sheets("Tracker").Range("AI2:AN19")
    Set TgtRngB = Sheets("Tracker").Range("AI21:AN26")
    TgtNme = "RCDI"

End Select

Sheets.Add.Name = "Exported_" & TgtNme

Sheets("Tracker").Range("TgtRngR").Copy Destination:=Sheets("Exported_" & TgtNme).Range("A1:F18")
Sheets("Tracker").Range("TgtRngB").Copy Destination:=Sheets("Exported_" & TgtNme).Range("A20:F25")
Sheets("Exported_" & TgtNme).Range("A1:F18") = Sheets("Tracker").Range("TgtRngR").Value
Sheets("Exported_" & TgtNme).Range("A20:F25") = Sheets("Tracker").Range("TgtRngB").Value
Sheets("Exported_" & TgtNme).Select
ActiveSheet.Columns("A:F").AutoFit

Upvotes: 1

Views: 767

Answers (2)

chris neilsen
chris neilsen

Reputation: 53146

You've set your range variable in the Case statement, so use it in the copy/paste

TgtRngR.Copy Destination:=Sheets("Exported_" & TgtNme).Range("A1:F18")

and

Sheets("Exported_" & TgtNme).Range("A1:F18") = TgtRngR

Also, your Case statment logic is incorrect. It should be something like

TgtNme = ""
Select Case MultiPage1.Value
Case 0
    ...
Case 1
    ...
'etc
End Select

If TgtNme <>"" then
    ' do the cop pastes
End If

Upvotes: 1

MikeD
MikeD

Reputation: 8941

Please review your Case statement:

  • in the Case opener you use a (local) variable Multi
  • in the contition statements you use a different object MultiPage1.Value
  • both of them are not declared

For me it looks as none of the Case branches are executed and hence TgtRngR, TgtRngB, TgtNme are undefined.

Setting a breakpoint at Private Sub ExpBttn() and stepping through the Sub with F8 would help.

Upvotes: 0

Related Questions