Hi all,
I have table(sheet) with 14 columns and around 40 000rows. Data in the sheet is added on a weekly basis, every Monday. So basically plus 100 to 300 rows every week added.
I made a macro which is really simple, it works well but only on small sample of data.
What i want from that macro:
1. filter data (I use only 3 columns to filter the data)
2. create a SUBTOTAL(SUM) formula
3. get the result from 2. and put it in the summary workbook
my sub_macro:
Main problems:
1. I need around 40 submacros to do this, filter the data, make a formula and put result in the summary workbook (every submacro has different filter data condittion, so i get 40 different results)
2. Second problem is, half of the results will prob be zero (0), since it will not find any data in the filter accourding to the specified criteria.
3. I did 6 sheets, cuz i dont know how to make it only from one sheet ( is it even possible?)
4. I get error after i added more than 6 submacros, substring out of range error
my main_macro:
Can someone help me how to do this? Thanks!
I have table(sheet) with 14 columns and around 40 000rows. Data in the sheet is added on a weekly basis, every Monday. So basically plus 100 to 300 rows every week added.
I made a macro which is really simple, it works well but only on small sample of data.
What i want from that macro:
1. filter data (I use only 3 columns to filter the data)
2. create a SUBTOTAL(SUM) formula
3. get the result from 2. and put it in the summary workbook
my sub_macro:
VBA Code:
Sub VSD_MZ_FVE()
'
Sheets("Sheet3_MZ_FVE").Select
ActiveSheet.Range("$A$1:$N$48718").AutoFilter Field:=14, Criteria1:= _
"=Little sources", Operator:=xlOr, Criteria2:="=Little source"
Range("D1").Select
ActiveSheet.Range("$A$1:$N$48718").AutoFilter Field:=4, Criteria1:="FVE"
ActiveSheet.Range("$A$1:$N$48718").AutoFilter Field:=1, Criteria1:="VSD"
Range("O1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[59]C[-5]:R[48714]C[-5])"
Range("P1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/1000"
Range("P1").Select
Selection.Copy
Application.WindowState = xlNormal
Workbooks.Open "C:\Users\XXXX\Desktop\final\RESULTS.xlsx"
Range("AA4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End SubMain problems:
1. I need around 40 submacros to do this, filter the data, make a formula and put result in the summary workbook (every submacro has different filter data condittion, so i get 40 different results)
2. Second problem is, half of the results will prob be zero (0), since it will not find any data in the filter accourding to the specified criteria.
3. I did 6 sheets, cuz i dont know how to make it only from one sheet ( is it even possible?)
4. I get error after i added more than 6 submacros, substring out of range error
my main_macro:
VBA Code:
Sub Main_macro()
'
Application.Run "ZIV_ZSD.xlsm!ZSD_MZ_FVE"
Windows("ZIV_ZSD.xlsm").Activate
Application.Run "ZIV_ZSD.xlsm!SSD_MZ_FVE"
Windows("ZIV_ZSD.xlsm").Activate
Application.Run "ZIV_ZSD.xlsm!VSD_MZ_FVE"
Windows("ZIV_ZSD.xlsm").Activate
Application.Run "ZIV_ZSD.xlsm!ZSD_LZ_FVE"
Windows("ZIV_ZSD.xlsm").Activate
Application.Run "ZIV_ZSD.xlsm!SSD_LZ_FVE"
Windows("ZIV_ZSD.xlsm").Activate
Application.Run "ZIV_ZSD.xlsm!VSD_LZ_FVE"
Windows("ZIV_ZSD.xlsm").Activate
End SubCan someone help me how to do this? Thanks!
