Printing multiple records

[spock1971]

Guys

I have a s/sheet detailing financial P&L information for stores which the user can choose using a dropdown. However, we want a print button which will print every store regardless of the one chosen.

My first idea is to record a macro where I manually change each store and print the current page, then the next store and print the page again etc. Then the user can just run the macro.

However, the stores change depending on time open so there may be 20 stores less than 12 months old now, but only 15 next month so I'd print 5 blank pages and not enough over 12 months.

Any ideas?

Cheers

 

[HaHoBe]

Hi, spock1971,

certainly there a place with both the information of the names of the stores as well as the date of opening. This information can be read in to an array (well only the information to fit the criteria and by using ReDim fo rthe correct number in the array) which can be used for the printout (using LBound to UBound for the array). By this you will make sure only to print the correct number of stores...

Ciao,
Holger

 

[spock1971]

Wass?

You lost me with the word Array buddy. Any chance of some dummy code for me to play with.

Cheers

 

[HaHoBe]

Hi. spock1971,

something like this:

Option Explicit

Sub StoresMeetCriteria()
Dim lngLastRow As Long
Dim lngCounter As Long
Dim lngArray As Long
Dim myArray() As Variant
Dim strMessage As String
lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
For lngCounter = 2 To lngLastRow
If Cells(lngCounter, 2).Value > DateSerial(Year(Date) - 1, Month(Date), Day(Date)) Then
  ReDim Preserve myArray(lngArray)
  myArray(lngArray) = Cells(lngCounter, 1).Value
  lngArray = lngArray + 1
End If
Next lngCounter
strMessage = "number of stores: " & UBound(myArray) + 1 & vbCrLf
For lngArray = LBound(myArray) To UBound(myArray)
  strMessage = strMessage & myArray(lngArray) & vbCrLf
Next lngArray
MsgBox strMessage
End Sub

Ciao,
Holger

 

[spock1971]

Will have a look and try and work it out. Cheers buddy.

Do you have any ideas how to get an Indirect formula to populate the data without having the file open?

My Indirect is pulling data in from various other files into one summary s/sheet.

Cheers

 

[HaHoBe]

Hi, spock1971,

as far as I know no chance with Indirect. Maybe you´ll have a look at A VBA Function to Get a Value From a Closed File or Copy a range from closed workbooks (ADO) or Different approaches to retrieve data from underlying workbooks for that purpose.

Ciao,
Holger

 

[spock1971]

Blimey - cheers buddy.

Will have a lok if I get time. Think I'm going to end up creating 32 different sheets for this report.

Darren

 

[spock1971]

Nope - don't understand the printing the right number of stores code you posted earlier.

I have 3 templete s/sheets to print - each store is only going to appear in one of the three templates, but move depending on time open.

i.e. all stores will print in template 1 until they become 2 years old. Then x number will still be less than 2 so stay in template 1 but y stores are now 2 so move to sheet 2 and need to be print template 2(sheet2).

I'm using a counter to move the stores automatically into a data validated field on the corresponding template sheets so it's a moving target.

Cheers

D

 

[HaHoBe]

Hi, spock1971,

sorry but I neither know your plans nor your workbook/worksheets or even how and what each button (on on each?) should be doing. You can have one button to do all the work, you can have one on each sheet. This depends on what should be done next. If you already know that only a certain amount of stores are on a sheet you maybe should explain your way of changing sheets:

I'm using a counter to move the stores automatically into a data validated field on the corresponding template sheets so it's a moving target.

Sorry, foreigner with a different native tongue has problems understanding what´s meant.

Ciao,
Holger

 

[spock1971]

It's something I do that works for me but is probably very long winded.

Stores that have been open less than one year only have one years data to present, those open between 1 and 2 years have 2 years data and those over 2 we report on the first 3 years.

Therefore I've got 3 different w/sheets set up identically but with 1,2,3 years data calculations set up on them.

On a seperate sheet I've got a formula which looks at the length of time open and lists the stores in seperate columns. Column A being only stores open less than 1 year, column B less than 2 years etc.

Then, on the reporting sheets I've got a data validated cell the user can choose a store to report on (which then uses my columns A, B, C in seperate sheet) as it's driver. Therefore, the user won't have to manually move stores when they hit 1 and 2 years old. They move between columns A, B, C and then only appear in the dropdown of the correct reporting sheet.

So there will always be 32 stores to print, but currently 20 less than 1 year, 10 2 years, and 2 3years. But next month this will change to 17,13,2 etc.

Hope it makes it a little clearer.