Setting a Variable Print Range

[melodramatic]

OK, I give. I'm trying to set a variable print range, based on the # of log entries I have on a worksheet. The last row # is saved in Cell C4, so all I need to do is incorporate that number (My print range will always be A1:J(Range C4).

My macro keeps hanging up at the line where I set the PrintArea. I get a Run-time error '1004': The text you entered is not a valid reference or defined name. I know I'm doing something screwy, but I can' t figure it out. Help?

Dim Message, Title, Default, MDRProjNum
Dim MDRProject
Dim PrintRange
Message = "Enter Project Number to Print"
Title = "Print MDR"
MDRProjNum = InputBox(Message, Title)

Range("C4").Select
ActiveCell.FormulaR1C1 = MDRProjNum
MDRProject = Range("C13")

Exists = (Dir(MDRProject) <> vbNullString)
If Exists Then
    Workbooks.Open Filename:=MDRProject
    Sheets("Assignment History").Select
    PrintRange = "A1:J" & Range("L6").Value
    ActiveSheet.PageSetup.PrintArea = PrintRange            'this is where I'm hanging up
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False

    ActiveWorkbook.Close False
            
    resp = MsgBox("Print-Out is at your default printer.", vbOK)
        
Else
    resp = MsgBox("MDR File does not exist for PN " & MDRProjNum, vbOK)
        Exit Sub
End If

 

[Snakehips]

melodramatic,

(My print range will always be A1:J(Range C4)

PrintRange = "A1:J" & Range("L6").Value 

One of these is incorrect.
If the code is looking at a cell that has no value then it will generate that error.

Hope that helps.

 

[melodramatic]

I sure hoped I had mistyped the macro - but no, I mistyped my statement here.

I just double-checked, and L6 is where the last row # is located.

I moved my Dim statement (Dim PrintRange) to just above the PrintRange = line, and am now getting past that line.

I'm getting the exact same error, but one line down, at the ActiveSheet.PageSetup.PrintArea = PrintRange

Out of curiosity, I set up a MsgBox to show me what it's getting for the values - and you're right, it's seeing blank in that cell. BUT, the cell clearly has 11 as it's value. So, I saved the value "X" in the macro file, and THAT is what is showing up in the Range. So, it's not getting my L6 range from the file I just opened, it's getting it from the macro file.

How do I fix this?

 

[melodramatic]

OK, let me show what I actually have now - so the question is, how do I get the variable to read the range from File #2, rather than File #1?

Dim Message, Title, Default, MDRProjNum                       'the macro opens up in and is run from the macro face file (File #1)
Dim MDRProject
Message = "Enter Project Number to Print"
Title = "Print MDR"
MDRProjNum = InputBox(Message, Title)

Range("C4").Select
ActiveCell.FormulaR1C1 = MDRProjNum
MDRProject = Range("C13")

Exists = (Dir(MDRProject) <> vbNullString)
If Exists Then
    Workbooks.Open Filename:=MDRProject                     'this opens the log file (File #2)
    Sheets("Assignment History").Select                           'this switches me to the log sheet in the log file (File #2)
    Dim PrintRange As String
    PrintRange = "A1:J" & Range("L6").Value                     'HERE IS THE PROBLEM.  The Range("L6") it's reading is from File #1
    ActiveSheet.PageSetup.PrintArea = PrintRange
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False

    ActiveWorkbook.Close False

        Exit Sub
        
Else
    resp = MsgBox("MDR File does not exist for PN " & MDRProjNum, vbOK)
        Exit Sub
End If


End Sub

 

[Snakehips]

m,

Try...

 PrintRange = "A1:J" & ActiveSheet.Range("L6").Value       'HERE IS THE PROBLEM.  The Range("L6") it's reading is from File #1 

 

[melodramatic]

It worked, it worked, it worked! You just made an AWESOME beginning to a new day - THANK YOU!!!

 

[Snakehips]

You are welcome. I hope the rest of your day is just as good.