Dynamic Print area vba code

[Woody75]

​Hello Everyone:

I have ws containing two tables of info that is vlookup'd from a range in a separate ws. The amount of data within the tables varies depending on the value of a data validated cell. I have already done a simple macro to autofilter the sheet but i cannot get the print area sorted.

Each table contains 42 rows and 5 columns that can be filtered down to a min of 7 rows each (obviously depends on the amount of data that is vlookup'd), there is also text inbetween the tables.

Also, does anyone have a code for selecting the 1st, 2nd, 3rd, etc data value within the data validated cell?

I'm really a beginner in VBA, any help would be great.

Many Thanks

Woody

 

[Woody75]

Any one????

 

[s hal]

I'm not following what you want - is there a reason that formatting the print area to fit to a page isn't working for this? 42 rows and 5 columns seems like a one page in either potrait or landscape - not sure why you need to do anything other than filter and hit print. I know its not a vba-solution, but I don't think this requires anything fancy!

 

[Woody75]

Sorry, not very good at explaining...

you are right in that 42 rows and 5 columns would equal one page, however once the 2 tables are autofiltered there would be less rows shown. I need a code that will dynamically set the print area at A4 regardless of the amount of rows that are filtered out and if needs be, carry forward any excess to the next page.

The second code i was looking for was to move the initial value of a data validated cell to the 2nd, 3rd 4th etc value as in my spreadsheet the value of that cell determines what is looked up within the two tables mentioned before.

I hope this explains what I need assistance with.

Many thanks

Woody

 

[Woody75]

s hal,

just re-read what you stated and thought about it (can be so dumb sometimes...). I agree, there is no need to use VBA for the print area, however I would still like a code to move the to the next option in the data validated (list) cell.

Woody

 

[s hal]

I'm glad you saw things my way Simplicity is always the best solution...

In terms of the validation - how is the data validation being passed to that cell? Is there a formula somewhere? Is it part of a macro?

 

[Woody75]

The validation data is entered on a different sheet, lets say sheet1 (a basic data input sheet). The data is then copied to a hidden column in the sheet with the tables (sheet2)

i have then used cell C4 as the validation cell (data, validation, selected list, then selected the migrated data, say range M2:M30 as an in cell drop down list) so it is not part of a macro.

What i am trying to achieve is to insert a command button that will execute the following:

select the first value from the drop down list in C4, autofilter the results (already know how to do this), print the page(s), unfilter, select the next value from the drop down list in C4 and continue to do this until all values are done. sort of like a loop.

Cheers

 

[s hal]

So, in the autofilter criteria, why not say criteria:=Sheets("your_sheet_here").Range("cell_here")? Or some fascimile thereof... you could do an if loop for this and have it cycle through as well and skip when the filter returns nothing, but that's getting complicated and may not be necessary. In any case, that would look something like the following:

For i = 1 to 3
Range("1:1").AutoFilter Field:=1, Criteria1:=Sheets("sheet1").Cells(i, 1)
Activesheet.Print
Next i

This assumes your data validation list starts in cell A1 and goes down 3 in sheet1 which is hidden for my example - modify to suit your needs

 

[Woody75]

s hal,

Thanks, like i said b4, I am a complete novice at this, in fact i'm way below that... what is 'i'? dont you usually have to use code to state what i represents??

if i insert that into vba will it just work (obviously with my sheets & cells inserted) or is there more to it?

Also, the auto filter is used to filter out the blank rows, maybe i'm not understanding this too well

Regards

 

[s hal]

Oh, I thought you said you know how to autofilter.. Autofilter filters whatever criteria you give it. You would want to substitute what I put as "1:1" for whatever row you are filtering on - in this case, if you data had a heading row in row 1, it would apply a filter and filter for whatever was in cell A1 in your hidden sheet.

You are correct with usually declaring a variable, but I was just doing lazy coding - vba will make a variant variable when it sees i declared as I did. You could also declare the variable in advanced with a Dim statement. Sorry, I had assumed you had some knowledge with the way you were posting.