Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi there,

    I am trying to record simple macro in excel 2007 as below

    Sub Macro1()
    '
    ' Macro1 Macro
    '

    '
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Copy
    Sheets.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$AG$967").AutoFilter Field:=4, Criteria1:= _
    "511000-Advance-"
    Selection.Copy
    Sheets.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortField s.Clear
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortField s.Add Key:=Range("C2:C3"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet3").Sort
    .SetRange Range("A1:AG3")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Selection.Style = "Comma"
    Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    Columns("A:A").Select
    Selection.NumberFormat = "[$-409]d/mmm/yy;@"
    Range("H2").Select
    ActiveWindow.FreezePanes = True
    Columns("M:AH").Select
    Selection.Delete Shift:=xlToLeft
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "Advance Register"
    Range("D21").Select
    End Sub

    but always appears errors (debug message)
    I don't know what was wrong, which did not happen using 2003

    thanks in advance

    Indra

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    We may need to see a copy of your workbook to fully understand this, but if you can provide the following information then someone may have an idea for you.
    • Exactly what error message do you get?
    • If you click the "debug" button on the error message, which line of code is highlighted?

  3. #3
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='StuartR' post='788570' date='12-Aug-2009 15:39']We may need to see a copy of your workbook to fully understand this, but if you can provide the following information then someone may have an idea for you.
    • Exactly what error message do you get?
    • If you click the "debug" button on the error message, which line of code is highlighted?
    [/quote]

    Please find as attached for the file,

    (just realize, that allowed file uploaded has been increased..)

    regards

    Indra
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    Your macro fails at the line
    Code:
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
    because there isn't a worksheet called "Sheet3", the only worksheets at that time are "Sheet2", "Sheet1", "01-General Ledger" and "04-Chart of Account".

    This is nothing to do with Excel 2007, it fails on Excel 2003 for me. Please check what changes you have made to your workbook since it used to work.

  5. #5
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ya, and perhaps my question should be how to modify this macro (I knew only how to record,) so every time user create new sheet, range of data has been selected will be copied to the new sheet


    regards

    Indra

  6. #6
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Stuart,

    I think I can modify some of the macro steps, simply copy to certain name of new worksheet. will get back to this forum, if can not

    Thanks for giving hint

    Indra

    : )

  7. #7
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    You could create a variable of type Worksheet, set that variable to point to your newly created sheet, and then use it when you want to make changes.

    For example, put
    Code:
    Dim sht As Worksheet
    once at the beginning of your code.

    Replace code like...
    Code:
    Sheets.Add
    with...
    Code:
    Set sht = Sheets.Add
    and replace code like
    Code:
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
    with
    Code:
    sht.Sort.SortFields.Clear
    Finally add
    Code:
    Set sht = Nothing
    just before End Sub, to release the memory that this variable was using.

  8. #8
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for advise Stuart,

    regards,

    Indra

Posting Permissions

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