[Solved] VBA: flippin' macros

  • My macro is below. all the bits work independently but put them together and i get errors.

    If I dim this line it works perfectly. All I want the macro to do is to create a new sheet prior to filling it with data, any ideas why this is happening as i'm pulling my hair out. (i've not got much to begin with).
    Application.Run "'saved macros.xls'!AddSheet"

    Sub DoTables9r7c()
    Dim i As Long, j As Long, k As Long, r As Range, s As Worksheet
    Application.ScreenUpdating = False
    Application.Run "'saved macros.xls'!AddSheet"
    Set s = Sheets("Update") 'sheet to copy tables to
    s.UsedRange.Clear 'start with clean sheet
    k = 0
    i = 1582 'enter the first row of table to convert
    Set r = Cells(i, 1)
    While Not IsEmpty(r)
    k = k + 1
    r.Copy s.Cells(k, 1)
    Set r = Range(Cells(i + 1, 2), Cells(i + 1, 2)) 'change 7 to number of columns and 8 to columns + 1
    For j = 1 To 1 'this refers to the number of rows
    r.Rows(j).Copy s.Cells(k, 17 * (j - 1) + 2) ' change 7 to number of columns
    Next j
    i = i + 19 'this is the interval between the first row of subsequent tables
    Set r = Cells(i, 1)
    Selection.Insert Shift:=xlToRight
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Cells.Replace What:="-", Replacement:="0", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False
    Application.Run "'saved macros.xls'!TextToColumnGorJ"
    End Sub

    Sub AddSheet()
    Sheets("Sheet2").Name = "Update"
    End Sub

    yours gratefully


  • Hi G,

    If you are getting errors, please indicate what the errors are and where they arise.

    Anyway, just as an observation, your routine always selects "Sheet2" to rename to "Update". How do you know that the added sheet will always be "Sheet2"? Better to just refer to the worksheet variable ("s" in your code).

    Something like this perhaps:

    If I'm completely missing the pointy here perhaps you could elaborate on the nature of the problem.


  • It seems as if my macro skips the filling in of the sheet with data and goes to the next application run function which is a text to column function.

    it works properly if i dim this statement however!!!ahhhhhhhhhhhhh!

    I can't put a create new sheet statement as I want the macro to run on the select workbook and not the one which contains the macros. More than one person has access to the macros sheet.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!