+ Reply to Thread
Results 1 to 11 of 11

Stuck and Need to Loop a Particular Piece of My Code

  1. #1
    Registered User
    Join Date
    04-24-2013
    Location
    Oakland
    MS-Off Ver
    Excel 2010
    Posts
    6

    Stuck and Need to Loop a Particular Piece of My Code

    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

  2. #2
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Stuck and Need to Loop a Particular Piece of My Code

    Hi,

    Welcome to the forum.
    Can you provide a before and after example.
    Most of the code you posted can be shorten.
    Charles

    There are other ways to do this, this is but 1 !
    Be Sure you thank those who helped.
    IF YOU'RE SATISFIED BY ANY MEMBERS RESPONSE TO YOUR ISSUE PLEASE USE THE STAR ICON AT THE BOTTOM LEFT OF THE POST UNDER THEIR NAME.

  3. #3
    Registered User
    Join Date
    04-24-2013
    Location
    Oakland
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Stuck and Need to Loop a Particular Piece of My Code

    Hi Charles!

    As you have requested. The before spreadsheet *should be attachjed in the original post. Here is the after spreadsheet.SYDS STOCK SPREADSHEET AFTER.zip. (Sorry, having some attachement issues. I hope that worked.)

  4. #4
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Stuck and Need to Loop a Particular Piece of My Code

    HI,

    Will all of the "Material" be copied to the "Clean" sheet?
    I see some Material "1003781" that has a zero value.
    Also do you want the destination sheet to be formated as you have it in your example?
    If I do not get back too you another member may pick up and help you.

  5. #5
    Registered User
    Join Date
    04-24-2013
    Location
    Oakland
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Stuck and Need to Loop a Particular Piece of My Code

    Hi Charles,

    Thanks for looking in to this for me. So...to better explain:

    Rows 9-10 on the "Master Data" tab directly correlate with Lines 13-15.
    So:
    B9 (Material) = B13 (the 10022XXX number
    C9 (Short Text) = C13
    C10 = C14
    C11 = C15

    The rest gets a little screwy, hence the heavy shifting.
    "USD" gets Deleted altogether
    F11 = E15 and so forth...

    Ultimately, the goal is to make the "Group" of the 3 rows, which are directly associcated with the Material (Colum B) (i.e.shift and sift into one fluid row of data. There would be no blanks between the Material numbers.

    Hope that clarified things a little...

  6. #6
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Stuck and Need to Loop a Particular Piece of My Code

    Hi,

    So you really want the 3 rows of data to 1 row in a new sheet?

  7. #7
    Registered User
    Join Date
    04-24-2013
    Location
    Oakland
    MS-Off Ver
    Excel 2010
    Posts
    6

    Lightbulb Re: Stuck and Need to Loop a Particular Piece of My Code

    Exactly (I don't know why I couldn't have just said that before)

  8. #8
    Registered User
    Join Date
    04-24-2013
    Location
    Oakland
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Stuck and Need to Loop a Particular Piece of My Code

    I tweaked the macro a bit.
    It is now cutting the data from the one sheet and pasting it into the correct location in the other then deleting the original information.

    I want the below piece of the macro to keep running until all of the data has been deleted. How do I loop this?

    Sheets("MASTER DATA (2)").Select
    Range("A1").Select
    Selection.Cut
    Sheets("Sheet2").Select
    Range("A2").Select
    ActiveSheet.Paste
    Sheets("MASTER DATA (2)").Select
    Range("B1").Select
    Selection.Cut
    Sheets("Sheet2").Select
    Range("B2").Select
    ActiveSheet.Paste
    Sheets("MASTER DATA (2)").Select
    Range("B2").Select
    Selection.Cut
    Sheets("Sheet2").Select
    Range("C2").Select
    ActiveSheet.Paste
    Sheets("MASTER DATA (2)").Select
    Range("B3").Select
    Selection.Cut
    Sheets("Sheet2").Select
    Range("D2").Select
    ActiveSheet.Paste
    Sheets("MASTER DATA (2)").Select
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "VB"
    Range("D3").Select
    Selection.Cut
    Sheets("Sheet2").Select
    Range("E2").Select
    ActiveSheet.Paste
    Sheets("MASTER DATA (2)").Select
    Range("I3").Select
    ActiveCell.FormulaR1C1 = "C"
    Range("I3").Select
    Selection.Cut
    Sheets("Sheet2").Select
    Range("F2").Select
    ActiveSheet.Paste
    Sheets("MASTER DATA (2)").Select
    Range("K3").Select
    ActiveCell.FormulaR1C1 = "34B"
    Range("K3").Select
    Selection.Cut
    Sheets("Sheet2").Select
    Range("G2").Select
    ActiveSheet.Paste
    Sheets("MASTER DATA (2)").Select
    Range("L1").Select
    Selection.Cut
    Sheets("Sheet2").Select
    Range("H2").Select
    ActiveSheet.Paste
    Sheets("MASTER DATA (2)").Select
    Range("M2").Select
    Selection.Cut
    Sheets("Sheet2").Select
    Range("I2").Select
    ActiveSheet.Paste
    Sheets("MASTER DATA (2)").Select
    Range("O3").Select
    ActiveCell.FormulaR1C1 = "MATL"
    Range("O3").Select
    Selection.Cut
    Sheets("Sheet2").Select
    Range("J2").Select
    ActiveSheet.Paste
    Sheets("MASTER DATA (2)").Select
    Range("Q3").Select
    ActiveCell.FormulaR1C1 = "STK"
    Range("Q3").Select
    Selection.Cut
    Sheets("Sheet2").Select
    Range("K2").Select
    ActiveSheet.Paste
    Sheets("MASTER DATA (2)").Select
    Rows("1:3").Select
    Selection.Delete Shift:=xlUp


    After all of the data has been "Cut Over" (so to speak) the empty sheet will be deleted.

  9. #9
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Stuck and Need to Loop a Particular Piece of My Code

    Hi,

    This is what I came up with.
    In sheet 2 I have "Headers.
    The code will move the data from the Master sheet to sheet2.
    I did not code to remove or create a new sheet.
    In sheet 2 you will see a button for "Test" click on it.
    This should start the code.
    If you look at the code you will see that I used the "Offset" method.
    The code will run for a short while as to you have a lot of data to look at.
    If you satisfied with this the you may be able to adapt it to the what you want.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    04-24-2013
    Location
    Oakland
    MS-Off Ver
    Excel 2010
    Posts
    6

    [SOLVED] Stuck and Need to Loop a Particular Piece of My Code

    So AWESOME!! Thanks for your help on this!!
    Last edited by Monkey Brain; 05-08-2013 at 03:44 PM. Reason: Solved

  11. #11
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Stuck and Need to Loop a Particular Piece of My Code

    You are welcome.
    Thanks for the "Rep"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1