Setting a Variable Print Range

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
180
Office Version
  1. 365
Platform
  1. Windows
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?

Code:
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
melodramatic,

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


Rich (BB code):
Rich (BB code):
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.

 
Upvote 0
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?
 
Upvote 0
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?

Code:
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
 
Upvote 0
m,

Try...

Rich (BB code):
 PrintRange = "A1:J" & ActiveSheet.Range("L6").Value       'HERE IS THE PROBLEM.  The Range("L6") it's reading is from File #1 
 
Upvote 0
It worked, it worked, it worked! You just made an AWESOME beginning to a new day - THANK YOU!!!
 
Upvote 0
You are welcome. I hope the rest of your day is just as good.
 
Upvote 0

Forum statistics

Threads
1,214,262
Messages
6,118,549
Members
448,835
Latest member
Profast123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top