earthworm
Well-known Member
- Joined
- May 19, 2009
- Messages
- 773
- Office Version
- 2019
- 2016
- Platform
- Windows
I have numeric values in another sheet by the name of summary.
If cell value in summary sheet example F5 is > 0 or less then 0 or <> 0 only then i want to run the macro for that
particular company .
The problem is if i use if condition it stop running the second macro if the first value is found as true.
My requirement .
if the value in F5 is 0 then move to next and if value in cell F4 > 0 then run second macro and so on . Please help!
Without above loop the macro will create and save all the sheet on desktop in folder Test even if there is no value in cell which is taking a lot of time . There are total 36 companies on which i want to run.
I have posted only few for my understanding and learning.
If cell value in summary sheet example F5 is > 0 or less then 0 or <> 0 only then i want to run the macro for that
particular company .
The problem is if i use if condition it stop running the second macro if the first value is found as true.
My requirement .
if the value in F5 is 0 then move to next and if value in cell F4 > 0 then run second macro and so on . Please help!
Without above loop the macro will create and save all the sheet on desktop in folder Test even if there is no value in cell which is taking a lot of time . There are total 36 companies on which i want to run.
I have posted only few for my understanding and learning.
Code:
Sub SplitData()
' Adler Jung
Range("B6").Select
Sheets("Dump Data Here").Select
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$W$23916").AutoFilter Field:=4, Criteria1:= _
"Adler Jung"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Range("A1:W683").Select
Selection.Columns.AutoFit
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A2").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Stop:=50000, Trend:=False
Selection.End(xlDown).Select
Range("B50001").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, -1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\user\Desktop\Test\ADLER JUNG.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("Dump Data Here").Select
Range("A1").Select
Selection.AutoFilter
Range("A1").Select
Sheets("Summary").Select
Range("A1").Select
' Aftab Currency Exchange Uk
Range("B6").Select
Sheets("Dump Data Here").Select
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$W$23916").AutoFilter Field:=4, Criteria1:= _
"Aftab Currency Exchange Uk"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Range("A1:W683").Select
Selection.Columns.AutoFit
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A2").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Stop:=50000, Trend:=False
Selection.End(xlDown).Select
Range("B50001").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, -1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\user\Desktop\Test\AFTAB CURRENCY EXCHANGE UK.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("Dump Data Here").Select
Range("A1").Select
Selection.AutoFilter
Range("A1").Select
Sheets("Summary").Select
Range("A1").Select
' Al Ansari Exchange Llc, Uae
Range("B6").Select
Sheets("Dump Data Here").Select
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$W$23916").AutoFilter Field:=4, Criteria1:= _
"Al Ansari Exchange Llc, Uae"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Range("A1:W683").Select
Selection.Columns.AutoFit
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A2").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Stop:=50000, Trend:=False
Selection.End(xlDown).Select
Range("B50001").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, -1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\user\Desktop\Test\AL ANSARI EXCHANGE LLC, UAE.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("Dump Data Here").Select
Range("A1").Select
Selection.AutoFilter
Range("A1").Select
Sheets("Summary").Select
Range("A1").Select
' Al Zaman Exchange
Range("B6").Select
Sheets("Dump Data Here").Select
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$W$23916").AutoFilter Field:=4, Criteria1:= _
"Al Zaman Exchange"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Range("A1:W683").Select
Selection.Columns.AutoFit
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A2").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Stop:=50000, Trend:=False
Selection.End(xlDown).Select
Range("B50001").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, -1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\user\Desktop\Test\AL ZAMAN EXCHANGE.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("Dump Data Here").Select
Range("A1").Select
Selection.AutoFilter
Range("A1").Select
Sheets("Summary").Select
Range("A1").Select
End Sub
Last edited: