PDA

View Full Version : Macro to count repeat occurrences from closed WB and paste total counts in new WB



Silver
12-10-2016, 03:42 AM
Hello All,

Exel beginner here.

There are 2 workbooks TestA and TestB. Both of them will be stored in different folders.

TestA has sheets named as per dates of month, like 1,2,3,4,5... and so on. On each sheet, column B will be updated with code L7. This workbook will always remain closed.

Macro will be run from TestB workbook. It has a sheet named Total which will reflect path of TestA in cell F2. It will also reflect sheet names of TestA one below the other from cell A3 onwards. Column B is were macro will update total counts of code L7.

What the macro should do -

Each time the macro is run, it will update column B with total counts of code L7 against each sheet name. Macro will do this by referring to path mentioned in column F. (Refer snapshot for clarity)

Note - I would like to use path reference as there is possibility of the data being pulled from different workbooks.

Any assistance will be greatly appreciated.

p45cal
12-10-2016, 04:05 AM
Save us a bit of time and guessing; supply 2 workbooks (or at the very least TestA) with some semi-realistic data in.

Silver
12-10-2016, 08:24 AM
Sorry.... kindly find attached workbooks TestA and TestB

p45cal
12-10-2016, 10:09 AM
Does the path in F2 include the filename?
That is, does it contain the likes of:
C:\Users\Public\Documents\vbaExpress57976
or:
C:\Users\Public\Documents\vbaExpress57976\
or:
C:\Users\Public\Documents\vbaExpress57976\TestA.xlsb
?

The macro will work by putting formulae in the cells. The macro can leave those formulae there - they will be updated everytime the sheet is calculated, or the macro can replace the formulae with their calculated results as a plain number. This latter will not update without running the macro again.
The macro doesn't need to open the workbook because formulae on a sheet can gain access to a closed workbook; it's a bit more convoluted for code to gain access to a closed workbook (without opening it).

Silver
12-11-2016, 01:48 PM
I like your idea of using macro and formulae.

Kindly provide macro which will replace the formulae with plain numbers.

It would also be helpful if you can insert comments in the code. Like if I want to change the path of the workbook TestA to TestH, or, change any of the columns. Will be helpful for future purpose.

p45cal
12-11-2016, 02:32 PM
I have a suggestion… answer my question.

Silver
12-12-2016, 01:12 AM
Path will include filename -

C:\Users\Public\Documents\vbaExpress57976\TestA.xlsb

p45cal
12-12-2016, 05:31 AM
The following macro:

requires you to select the cells you wish to fill with data before running
expects a full path and file name in cell F2 of the active sheet
expects valid sheet names in the column directly to the left of the selected range
expects the data to be counted to be in column B of the sheets


Sub blah()
Set RngToFill = Selection
Z = Range("F2").Value
x = InStrRev(Z, Application.PathSeparator, , vbTextCompare)
myStr = Left(Z, x) & "[" & Mid(Z, x + 1) & "]"
For Each cll In RngToFill.Cells
cll.FormulaArray = "=SUM(IF('" & myStr & cll.Offset(, -1).Value & "'!$B:$B=""L7"",1,0))"
Next cll
RngToFill.Value = RngToFill.Value 'comment-out/delete this line to keep formulae on sheet.
RngToFill.Cells(rngToFill.Rows.Count + 1).Formula = "=SUM(" & RngToFill.Address(0, 0) & ")" 'add SUM formula at bottom.
End Sub



if I want to change the path of the workbook TestA to TestH, or, change any of the columns.Since the path and name of the workbook is in cell F2, to change the file name/location you need only to update cell F2 manually.
You'll see the code includes a reference to $B:$B. This is the column searched on each sheet. Change this to change the column searched. You don't even need the $ symbols, it can be B:B
Currently, the macro knows where to place the formulae/results because you've selected that range beforehand. It is possible to hard code that location by replacing Set RngToFill = Selection with Set RngToFill = Range("B3:B33") or some such; it might even be possible for the macro to determine for itself where to put the values, however this would require more information on how the real sheet is set up, what's constant about that sheet, what varies etc. etc. Altogether a different animal.