Application defined or object defined error (named ranges)

Question

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

chris neilsen · Accepted Answer · 2013-06-21 07:52:35Z

1

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

answered Jun 21, 2013 at 7:52

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ghulam Murtaza Over a year ago

Wow! Thanks alot that worked like a charm. Yes I'm a noob as you can clearly see :D thanks so much i really didnt know where to go with this but the response time and the knowledgble people here make me feel great! You sir, are my hero. Thank you again.

MikeD · Accepted Answer · 2013-06-21 07:48:28Z

0

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.

answered Jun 21, 2013 at 7:48

MikeD

8,9412 gold badges32 silver badges50 bronze badges

1 Comment

Ghulam Murtaza Over a year ago

Thank you so much for the super fast reponse I got it :) youre awesome

Collectives™ on Stack Overflow

Application defined or object defined error (named ranges)

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related