Hi all,
I have a set of data for which I need to calculate % split based on criteria on sheet2. I have recorded it for 1 criteria which is in row2. I have till rows 15. Don't no how to loop it till end and im afraid that my code also will get increased. Below is the recorded code.
Please help me to make it simple and loop until last keyed cell.
I have a set of data for which I need to calculate % split based on criteria on sheet2. I have recorded it for 1 criteria which is in row2. I have till rows 15. Don't no how to loop it till end and im afraid that my code also will get increased. Below is the recorded code.
Please help me to make it simple and loop until last keyed cell.
Code:
Sub Macro1()
'
' Macro1 Macro
'
Sheet2.Select
Range("B2").Select
Findstring = Selection.Value
Selection.Copy
Sheet3.Select
ActiveSheet.Range("A:AH").AutoFilter Field:=11, Criteria1:=Findstring
Application.CutCopyMode = False
Range("A2:AB2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheet4.Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("X1:AB1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("AC1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheet2.Select
Range("C2").Select
Selection.Copy
Sheet4.Select
Range("X2:AB2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Style = "Currency"
Range("A1:AB1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheet5.Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheet2.Select
Range("C1").Select
Selection.Copy
Sheet5.Select
Range("AC2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheet4.Select
Range("AC2:AG2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("X2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Sheet2.Select
Range("D2").Select
Selection.Copy
Sheet4.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Style = "Currency"
Range("A2:AB2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheet5.Select
Range("A1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheet2.Select
Range("D1").Select
Selection.Copy
Sheet5.Select
Range("AC1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("AB1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Sheet4.Select
Range("AC2:AG2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("X2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Sheet2.Select
Range("E2").Select
Selection.Copy
Sheet4.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Selection.Style = "Currency"
Application.CutCopyMode = False
Range("A2:AB2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheet5.Select
Range("A1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheet2.Select
Range("E1").Select
Selection.Copy
Sheet5.Select
Range("AC1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("AB1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
End Sub