Setting a Variable Print Range

melodramatic

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

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
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
136
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
136
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,059
Office Version
  1. 2013
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
136
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,059
Office Version
  1. 2013
Platform
  1. Windows
You are welcome. I hope the rest of your day is just as good.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,348
Messages
5,528,187
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top