Macro to run in all workbooks with condition

zain_shaikh · Mar 1, 2015

Hello friends,

I am an accountant in a firm which deals mostly in Installment sales of motorcycles. Apart from manual book keeping
I keep all of my data related to installment sales saved in excel files. For this purpose I use a template which is very much
like a an invoice but also shows due dates, payment dates, amount of installment paid & remaining balance of the customer.
I am currently managing sales data of 4 of our branches. The folders I have are Branch-1, Branch-2, Branch-3 & Branch-4.
Each of these folders contains sub folders named according to year of sales like from year 2006 to 2015 & has 12 workbooks each (Jan 2006.xls, Feb 2006.xls & so on till March 2015.xls in folder named 2015). Each of these
workbooks has different numbers of worksheets named according to the account number assigned to the customer.

I am searching for a solution to run a macro with certain conditions in all of my workbooks in a folder. The macro I use
is a simple code which goes through all worksheets in a workbook, extracts data from certain cells in all worksheets &
creates a summary in a worksheet named ‘Summary’ by pasting the data row wise. This is the macro:

Code:

Sub Create_Summary()
Dim wksht As Worksheet: Set wksht = Sheets("Summary")
Dim ws As Worksheet
Dim LR As Long

Application.ScreenUpdating = False

For Each ws In Worksheets
    If ws.Name <> wksht.Name Then
        LR = wksht.Range("A" & Rows.Count).End(xlUp).Row + 1
        wksht.Range("A" & LR) = ws.Range("H2")
        wksht.Range("B" & LR) = ws.Range("B2")
        wksht.Range("C" & LR) = ws.Range("B4")
        wksht.Range("D" & LR) = ws.Range("C2")
        wksht.Range("E" & LR) = ws.Range("B3")
        wksht.Range("F" & LR) = ws.Range("E3")
        wksht.Range("G" & LR) = ws.Range("H3")
        wksht.Range("H" & LR) = ws.Range("H1")
        wksht.Range("I" & LR) = ws.Range("F48")
        wksht.Range("J" & LR) = ws.Range("H13")
        wksht.Range("K" & LR) = ws.Range("H12")
        wksht.Range("L" & LR) = ws.Range("H14")
        wksht.Range("M" & LR) = ws.Range("H15")

    End If
Next ws

Application.ScreenUpdating = True
       
End Sub

I run this code in all of my workbooks, which is a bit time consuming. I have to custom format summary sheet by adding
header, headings in column, set the desired column width etc. Then I have to copy all sheet & paste in a another
work book in order to complete master summary for any branch so that I have complete customer info from year
2006 to 2015 in either branches. Apart from master summary sheet, I have to prepare two more sheets, one which
shows customers with remaining balance & the other which shows remaining balance with a condition i.e. H13 has data
more than 0 (H13>0) in omitting any account with H13=0.

I am looking for a macro or may be 3 macros (considering 2 conditions I mentioned above) that runs through all of my
workbooks (excluding a sheet named Summary which is in every workbook) branch wise, collect data from cells,
paste into one master sheet row wise with column headings, sets column width according to space occupied by text in
a cell, with the main header as Branch folder name & shows date - time when summary is created. I possess a very
limited expertise on excel & a will to learn more therefore any kind of assistance will be highly appreciated.

zain_shaikh · Mar 2, 2015

I am attaching files for better understanding of my requirement.

File: February 2014.xls

HTML:

https://drive.google.com/file/d/0B0L43wu5SnIoWklmNTVNSnNHbWM/view?usp=sharing

File: March 2014.xls

HTML:

https://drive.google.com/file/d/0B0L43wu5SnIoQjRTdnRCRGxLRWM/view?usp=sharing

File: Master_Branch_01.xls

HTML:

https://drive.google.com/file/d/0B0L43wu5SnIoZjU0V0JQbTc0ZTg/view?usp=sharing

The first 2 files are example files from Branch 1 folder named 2014. The 3rd file is Master file i.e. summary of two example files above.

Macro to run in all workbooks with condition

zain_shaikh

New Member

Excel Facts

zain_shaikh

New Member

Similar threads

Forum statistics

Share this page

Macro to run in all workbooks with condition

zain_shaikh

New Member

Excel Facts

zain_shaikh

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock