Hello all, I'm having a problem dynamically setting the print area:
Basically I start with a table that runs from A1 to AG19, and I copy it. Column A is placeholder column, which allows me to use the .End(xlDown) function to find the bottom of the table. I find the bottom of the table; and if a cell two below the bottom is filled, I know that a table has already been copied to that location. Then the function iterates, until it finds the lowest table that has been inserted into the worksheet.![]()
ActiveSheet.Range("A1:AG19").Select Selection.copy i = True lastRow = Range("a1").End(xlDown).row ranges = "$A$1:$AG$19," While i = True If IsEmpty(Cells(lastRow + 2, 1)) Then 'in theory at this point the macro should have found the 'bottom most output Range("A" & lastRow + 2).Select ActiveSheet.Paste Application.CutCopyMode = False ranges = ranges & "$A" & "$" & (lastRow + 2) & ":" & "$AG" & "$" & (lastRow + 20) i = False Else ranges = ranges & "$A" & "$" & (lastRow + 2) & ":" & "$AG" & "$" & (lastRow + 20) & "," lastRow = Range("A" & lastRow + 2).End(xlDown).row End If Wend ActiveSheet.PageSetup.PrintArea = ranges
So for instance I start with a table for A1 to AG19; if I have already inserted a table that goes from A21 to AG39, the function will iterate until the next table that can be inserted runs from A41 to AG59.
The ranges variable is an attempt to create a string with the exact range of the tables; so that if I have a table on A1 to AG19, and a table on A21 to AG 39, and a table from A41 to AG59, I will make the string
"$A$1:$AG$19, $A$21:$AG$39, $A$41:$AG$59" which is the format, seemingly that the ActiveSheet.PageSetup.PrintArea command requires.
However when I run this code, it returns error 1004.
Is this an error with the variable type (meaning that the ActiveSheet.PageSetup.PrintArea doesn't accept strings in that specific format)?
Is this an error with the string itself (meaning that $A$1:$AG$19, $A$21:$AG$39, $A$41:$AG$59 is not in the correct form?
Bookmarks