Setting a Variable Print Range

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
161
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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,561
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

 

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
161
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

Board Regular
Joined
Apr 28, 2003
Messages
161
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
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,561
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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 
 

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
161
It worked, it worked, it worked! You just made an AWESOME beginning to a new day - THANK YOU!!!
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,561
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You are welcome. I hope the rest of your day is just as good.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,299
Messages
5,836,484
Members
430,435
Latest member
Benforest1

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
Top