Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2013
    Posts
    14

    Unanswered: Shortening macro code

    As you can see, i have a fairly lengthy code. Is there any way to use a loop to shorten this?

    Sub BillMove()
    '
    ' BillMove Macro
    ' Move information from the selected spreadsheet and insert them in the table.
    '
    ' Keyboard Shortcut: Ctrl+Shift+M
    '
    Dim active As String
    active = ActiveSheet.Name
    Range("C3:G3").Select
    Selection.Copy
    Sheets("Table").Select
    Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select

    Sheets(active).Select
    Range("B11:G11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 6).Range("A1").Select

    Sheets(active).Select
    Range("B12:G12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 6).Range("A1").Select

    Sheets(active).Select
    Range("B13:G13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 6).Range("A1").Select

    Sheets(active).Select
    Range("B14:G14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 6).Range("A1").Select

    Sheets(active).Select
    Range("B15:G15").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 6).Range("A1").Select

    Sheets(active).Select
    Range("B16:G16").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 6).Range("A1").Select

    Sheets(active).Select
    Range("B17:G17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 6).Range("A1").Select

    Sheets(active).Select
    Range("B18:G18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 6).Range("A1").Select

    Sheets(active).Select
    Range("B19:G19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 6).Range("A1").Select

    Sheets(active).Select
    Range("B20:G20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 6).Range("A1").Select

    Sheets(active).Select
    Range("C21:G21").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    ActiveCell.Offset(0, 5).Range("A1").Select

    Sheets(active).Select
    Range("B24").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select

    Sheets(active).Select
    Range("D24:G24").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 4).Range("A1").Select

    Sheets(active).Select
    Range("B25").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select

    Sheets(active).Select
    Range("D25:G25").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 4).Range("A1").Select

    Sheets(active).Select
    Range("B26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select

    Sheets(active).Select
    Range("D26:G26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 4).Range("A1").Select

    Sheets(active).Select
    Range("B27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select

    Sheets(active).Select
    Range("D27:G27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 4).Range("A1").Select

    Sheets(active).Select
    Range("B28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select

    Sheets(active).Select
    Range("D28:G28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 4).Range("A1").Select

    Sheets(active).Select
    Range("B29").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select

    Sheets(active).Select
    Range("D29:G29").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 4).Range("A1").Select

    Sheets(active).Select
    Range("B30").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select

    Sheets(active).Select
    Range("D30:G30").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 4).Range("A1").Select

    Sheets(active).Select
    Range("B31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select

    Sheets(active).Select
    Range("D31:G31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 4).Range("A1").Select

    Sheets(active).Select
    Range("B32").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select

    Sheets(active).Select
    Range("D32:G32").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 4).Range("A1").Select

    Sheets(active).Select
    Range("B33").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select

    Sheets(active).Select
    Range("D33:G33").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 4).Range("A1").Select

    Sheets(active).Select
    Range("D34:G34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 4).Range("A1").Select

    Sheets(active).Select
    Range("D35:G35").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 4).Range("A1").Select

    Sheets(active).Select
    Range("D36:G36").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    ActiveCell.Offset(0, 4).Range("A1").Select

    Sheets(active).Select
    Range("c40:G40").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 5).Range("A1").Select

    Sheets(active).Select
    Range("C41:G41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 5).Range("A1").Select

    Sheets(active).Select
    Range("C42:G42").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 5).Range("A1").Select

    Sheets(active).Select
    Range("C43:G43").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    ActiveCell.Offset(0, 5).Range("A1").Select

    Sheets(active).Select
    Range("C45:G45").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.Font.Bold = True
    Selection.Font.Bold = False
    Selection.Font.Italic = True
    Selection.Font.Italic = False
    Selection.Font.Underline = xlUnderlineStyleSingle
    Selection.Font.Underline = xlUnderlineStyleNone
    ActiveCell.Rows("1:1").EntireRow.Select
    ActiveCell.Offset(1, 0).Range("A1").Select

    Sheets(active).Select
    Range("H1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    Range("H1").Select

    End Sub

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Yes, there is. In your previous thread, I illustrated using an array of range addresses and a For Each loop to iterate through it. You should be able to adapt that.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Apr 2013
    Posts
    1
    indeed! It's a very long code. You got to practice writing shorter codes, just a piece of advice. catalogues for bad credit
    Last edited by nettieriley; 05-17-13 at 16:34.

Posting Permissions

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