VBA message input to complete print range

[Shadkng]

I would like to use a macro with a message box response to complete a print range. So let's say I want a print range of A3 to C30, but I want the C30 to change based on the input in the message box. The A3 can remain fixed as that will not likely change. I am new to vba and could not fine anything in the search. Thanks

 

[Trebor76]

Hi Shadkng,

Have a look at this thread that I was part of.

Thanks,

Robert

 

[Shadkng]

I know the code below is not correct but it may help convey what I want to do.

Sub print_range()
Dim response As String
response = InputBox ("enter 2nd part of range")
Worksheets("table prod").PageSetup.PrintArea = $A$3 and here I want to add the entry from the input box.
End Sub

 

[rlv01]

So something like this?

Sub print_range()
    Dim response As String
    Dim PrintAreaString As String


    response = InputBox("Enter 2nd part of range (example: $H$10)", "Enter Data")
    PrintAreaString = "$A$3:" & Trim(response)
    If response <> "" Then
        Worksheets("table prod").PageSetup.PrintArea = PrintAreaString    ' and here I want to add the entry from the input box.
    End If
End Sub

 

[Shadkng]

Yes this seems to work, thanks. Can you add one more twist? I have a row number in cell A1 which represents the last row that has data and which the print range goes down to. So could this be modified so the input would only have to be the column letter? So A3 to F50 as the print range, and 50 is in cell A1. The column letter would vary depending on what I need to print.

 

[rlv01]

Sub print_range()
    Dim response As String
    Dim PrintAreaString As String


    response = InputBox("Enter column letter for 2nd part of range", "Enter Data")


    With Worksheets("table prod")
        PrintAreaString = "$A$3:$" & Trim(UCase(response)) & "$" & .Range("A1").Value
        If response <> "" Then
            .PageSetup.PrintArea = PrintAreaString
        End If
    End With
End Sub

 

[Shadkng]

Works perfectly. I'm trying to add other things on my own before asking but need a little more help. To modify the code to run on the active sheet? There seems to be many ways when I searched so not sure.
Also, I want to add some printing attributes such as fit to page, landscape, etc. which I can do them myself, but if you can add one command then I can follow with others. Thanks again I really appreciate it.

 

[rlv01]

To modify the code to work on the activesheet, change

    With Worksheets("table prod")

to

With Activesheet

Keep in mind that if you don't have the row number stored in cell A1 of the activesheet, the macro will break. For the print settings I recommend using the macro recorder to record a macro changing the printing attributes you are interested in and then inspecting the code that the recorder creates as a guide to implementing them in your code.

 

[Shadkng]

OK will do. thanks

 

[Shadkng]

I made the change to "with activesheet" and the macro will not run on my other sheets in the same workbook. They all have the row number stored in A1 of the respective sheet. Even if I specify the other sheets by name in the macro it still doesn't run.