Jagdev
03-19-2015, 04:14 AM
Hi Experts
I managed to create a macro which modifies the sheet which I received on daily basis. The count of sheet varies from 40-50. The code is working fine, but the only issue right now is to open each sheet which needs to be modified and run each code on it and then save it. Is it possible to amend the macro which will go to a particular folder and modify the sheets automatically instead of manually opening each file and run the code.
Regards,
JD
Sub Logo()
'
' Logo Macro
'
'
Range("I2:U2").Select
'ActiveSheet.Pictures.Insert( _
"C:\Users\Sinderjt\Desktop\New folder (3)\ LOGO.png" _
).Select
'Selection.ShapeRange.IncrementLeft 350.25
'Selection.ShapeRange.IncrementTop 9.75
Range("S4:W4").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Summary"
Range("B30").Select
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Bank Details"
ActiveSheet.Shapes.Range(Array("Picture 2")).Select
Sheets("Summary").Select
Range("U17:V17").Select
End Sub
Sub StatementMacro()
'
' StatementMacro Macro
'
'
Columns("F:F").Select
With Selection
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Selection.UnMerge
With Selection
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:B").Select
Columns("B:B").EntireColumn.AutoFit
Cells.Select
Cells.EntireColumn.AutoFit
Rows("7:7").Select
Selection.Font.Underline = xlUnderlineStyleNone
With Selection
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A7").Select
ActiveCell.FormulaR1C1 = "Date"
Range("C7").Select
ActiveCell.FormulaR1C1 = "Due Date"
Range("E7").Select
ActiveCell.FormulaR1C1 = "Reference"
Range("J7").Select
ActiveCell.FormulaR1C1 = "Ves"
Range("O7").Select
ActiveCell.FormulaR1C1 = "Ins"
Range("T7").Select
ActiveCell.FormulaR1C1 = "Int"
Range("V7").Select
ActiveCell.FormulaR1C1 = "Ccy"
Range("AB7").Select
ActiveCell.FormulaR1C1 = "Amount"
Range("AF7").Select
ActiveCell.FormulaR1C1 = "Dir"
Range("AG7").Select
ActiveCell.FormulaR1C1 = "Comments"
Range("AG7").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("AB7").Select
Selection.Copy
Range("AG7").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("AG7").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("AG7").Select
Selection.Copy
Range("AF7").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("B13").Select
Columns("A:A").ColumnWidth = 10.43
Range("D7").Select
ActiveCell.FormulaR1C1 = "Cover"
Range("B7").Select
Selection.EntireColumn.Delete
Range("E7").Select
Columns("N:N").EntireColumn.AutoFit
Columns("N:N").Select
Selection.Cut
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Range("F13:I13").Select
Selection.EntireColumn.Delete
Range("G7").Select
ActiveCell.FormulaR1C1 = "Details"
Range("H7:N7").Select
Selection.EntireColumn.Delete
Range("F5:U5").Select
Range("U5").Activate
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("F3:U3").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("I4").Select
Selection.EntireColumn.Delete
Range("P4:R4").Select
Range("R4").Activate
Selection.EntireColumn.Delete
Range("L4:N4").Select
Range("N4").Activate
Selection.EntireColumn.Delete
Range("J9:K9").Select
Range("K9").Activate
Selection.EntireColumn.Delete
Columns("B:I").Select
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("A7").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range("A7").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:B").Select
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").Select
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").Select
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").Select
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").Select
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").Select
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").Select
Columns("H:H").EntireColumn.AutoFit
Columns("I:I").Select
Columns("I:I").EntireColumn.AutoFit
Columns("J:J").Select
Columns("J:J").EntireColumn.AutoFit
Columns("K:K").Select
Columns("K:K").EntireColumn.AutoFit
Columns("L:L").Select
Columns("L:L").EntireColumn.AutoFit
Range("J25").Select
Columns("J:J").EntireColumn.AutoFit
Columns("K:K").EntireColumn.AutoFit
Columns("L:L").EntireColumn.AutoFit
End Sub
Sub CleanData()
Dim lastrow As Long
lastrow = Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
Range("A7").AutoFilter
ActiveSheet.Range("$A$7:$L$7" & lastrow).AutoFilter Field:=3, Criteria1:="<>"
Range("A8:L" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Range("A1").AutoFilter
ActiveSheet.Range("$A$7:$L$7" & lastrow).AutoFilter Field:=1, Criteria1:= _
"** PART OF"
'"AND COMPRISES THE FOLLOWING POSTINGS"
Range("A8:L" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Range("A7").AutoFilter
End Sub
I managed to create a macro which modifies the sheet which I received on daily basis. The count of sheet varies from 40-50. The code is working fine, but the only issue right now is to open each sheet which needs to be modified and run each code on it and then save it. Is it possible to amend the macro which will go to a particular folder and modify the sheets automatically instead of manually opening each file and run the code.
Regards,
JD
Sub Logo()
'
' Logo Macro
'
'
Range("I2:U2").Select
'ActiveSheet.Pictures.Insert( _
"C:\Users\Sinderjt\Desktop\New folder (3)\ LOGO.png" _
).Select
'Selection.ShapeRange.IncrementLeft 350.25
'Selection.ShapeRange.IncrementTop 9.75
Range("S4:W4").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Summary"
Range("B30").Select
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Bank Details"
ActiveSheet.Shapes.Range(Array("Picture 2")).Select
Sheets("Summary").Select
Range("U17:V17").Select
End Sub
Sub StatementMacro()
'
' StatementMacro Macro
'
'
Columns("F:F").Select
With Selection
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Selection.UnMerge
With Selection
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:B").Select
Columns("B:B").EntireColumn.AutoFit
Cells.Select
Cells.EntireColumn.AutoFit
Rows("7:7").Select
Selection.Font.Underline = xlUnderlineStyleNone
With Selection
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A7").Select
ActiveCell.FormulaR1C1 = "Date"
Range("C7").Select
ActiveCell.FormulaR1C1 = "Due Date"
Range("E7").Select
ActiveCell.FormulaR1C1 = "Reference"
Range("J7").Select
ActiveCell.FormulaR1C1 = "Ves"
Range("O7").Select
ActiveCell.FormulaR1C1 = "Ins"
Range("T7").Select
ActiveCell.FormulaR1C1 = "Int"
Range("V7").Select
ActiveCell.FormulaR1C1 = "Ccy"
Range("AB7").Select
ActiveCell.FormulaR1C1 = "Amount"
Range("AF7").Select
ActiveCell.FormulaR1C1 = "Dir"
Range("AG7").Select
ActiveCell.FormulaR1C1 = "Comments"
Range("AG7").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("AB7").Select
Selection.Copy
Range("AG7").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("AG7").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("AG7").Select
Selection.Copy
Range("AF7").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("B13").Select
Columns("A:A").ColumnWidth = 10.43
Range("D7").Select
ActiveCell.FormulaR1C1 = "Cover"
Range("B7").Select
Selection.EntireColumn.Delete
Range("E7").Select
Columns("N:N").EntireColumn.AutoFit
Columns("N:N").Select
Selection.Cut
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Range("F13:I13").Select
Selection.EntireColumn.Delete
Range("G7").Select
ActiveCell.FormulaR1C1 = "Details"
Range("H7:N7").Select
Selection.EntireColumn.Delete
Range("F5:U5").Select
Range("U5").Activate
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("F3:U3").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("I4").Select
Selection.EntireColumn.Delete
Range("P4:R4").Select
Range("R4").Activate
Selection.EntireColumn.Delete
Range("L4:N4").Select
Range("N4").Activate
Selection.EntireColumn.Delete
Range("J9:K9").Select
Range("K9").Activate
Selection.EntireColumn.Delete
Columns("B:I").Select
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("A7").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range("A7").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:B").Select
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").Select
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").Select
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").Select
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").Select
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").Select
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").Select
Columns("H:H").EntireColumn.AutoFit
Columns("I:I").Select
Columns("I:I").EntireColumn.AutoFit
Columns("J:J").Select
Columns("J:J").EntireColumn.AutoFit
Columns("K:K").Select
Columns("K:K").EntireColumn.AutoFit
Columns("L:L").Select
Columns("L:L").EntireColumn.AutoFit
Range("J25").Select
Columns("J:J").EntireColumn.AutoFit
Columns("K:K").EntireColumn.AutoFit
Columns("L:L").EntireColumn.AutoFit
End Sub
Sub CleanData()
Dim lastrow As Long
lastrow = Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
Range("A7").AutoFilter
ActiveSheet.Range("$A$7:$L$7" & lastrow).AutoFilter Field:=3, Criteria1:="<>"
Range("A8:L" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Range("A1").AutoFilter
ActiveSheet.Range("$A$7:$L$7" & lastrow).AutoFilter Field:=1, Criteria1:= _
"** PART OF"
'"AND COMPRISES THE FOLLOWING POSTINGS"
Range("A8:L" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Range("A7").AutoFilter
End Sub