Hello,
I'm pretty proficient with learning and picking up vba in excel, but I haven't gotten the hang of the loop process yet.
I created a macro to format a multi-row, multi-column report out of SAP into a fluid single row/multi column. The macro creates a new tab with one particular set of formatted data, then creates an additional tab with a different set of formatted "clean data." (Sheet1 = original data, Sheet2 = original data with header removed and alignment adjusted, Sheet3 = fully formatted data from Tab2)
The fully formatted sheet (Sheet3) is the same procedure over and over again (looped - copy rows 1:3 from Sheet2 to Sheet3 under the correct single column headings).
Here is what I have and the part of the code I need to loop:
Sub FormatIt()
'
' FormatIt Macro
'
' 'CREATING THE SECOND SHEET'
Sheets("MASTER DATA").Select
Sheets("MASTER DATA").Copy After:=Sheets(1)
'FORMATING MATSER DATA 2'
'DELETING THE FIRST EIGHT LINES'
Rows("1:8").Select
Selection.Delete Shift:=xlUp
'ALIGNING THE MASTER DATA LEFT'
Cells.Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("1:7").Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("B2,B6").Select
Range("B6").Activate
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A3:B3,A7:B7").Select
Range("A7").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll ToRight:=2
Range("K5:L5").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("I6").Select
Selection.ClearContents
Range("N6").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F7").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("N7:O7").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll ToRight:=5
Range("R7:T7").Select
Selection.Delete Shift:=xlToLeft
Range("S7").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll ToRight:=-5
Range("D1").Select
Selection.Delete Shift:=xlUp
Range("E1:N2").Select
Selection.Delete Shift:=xlUp
Range("P1").Select
Selection.Delete Shift:=xlUp
Range("Q1:U2").Select
Selection.Delete Shift:=xlUp
Rows("2:4").Select
Range("G2").Activate
Selection.Delete Shift:=xlUp
Range("U2").Select
ActiveCell.FormulaR1C1 = "STK"
Range("R2").Select
ActiveCell.FormulaR1C1 = "MATL"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "34B"
Range("M2").Select
ActiveCell.FormulaR1C1 = "C"
Range("H2").Select
ActiveCell.FormulaR1C1 = "VB"
Range("A2").Select
Sheets("MASTER DATA (2)").Select
Selection.Delete Shift:=xlUp
***THIS IS THE PART I NEED TO LOOP***
'GATHERING NEXT SET OF DATA'
Rows("1:3").Select
Selection.Cut
Sheets("Sheet2").Select
Range("A3").Select
ActiveSheet.Paste
Range("B4").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A5:B5").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F5").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("L3:M3").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("I4").Select
Selection.ClearContents
Range("O4").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("N5:O5").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("R5:T5").Select
Selection.Delete Shift:=xlToLeft
Range("S5").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D3").Select
Selection.Delete Shift:=xlUp
Range("E3:N4").Select
Selection.Delete Shift:=xlUp
Range("P3").Select
Selection.Delete Shift:=xlUp
Range("Q3:R4").Select
Selection.Delete Shift:=xlUp
Range("U3:U4").Select
Selection.Delete Shift:=xlUp
Range("U3").Select
ActiveCell.FormulaR1C1 = "STK"
Range("R3").Select
ActiveCell.FormulaR1C1 = "MATL"
Range("Q3").Select
ActiveCell.FormulaR1C1 = "34B"
Range("M3").Select
ActiveCell.FormulaR1C1 = "C"
Range("H3").Select
ActiveCell.FormulaR1C1 = "VB"
Range("D3").Select
I have attached the "*****" spredsheet with the macro in it for better understanding of what I'm doing.
Any help you can provide would be greatly appreciated!!SYDS STOCK SPREADSHEET.xlsx
Bookmarks