[Solved] VBA: flippin' macros

Giorgio · Dec 9th 2003

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)
Wend
Sheets("Update").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Cells.Select
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.Add
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Update"
Range("A1").Select
End Sub

yours gratefully

giorgio

Richie(UK) · Dec 9th 2003

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:

Code

Sub Test()
    Dim s As Worksheet
    Const strWSName As String = "Update"
    
    If Not SheetExists(strWSName) Then
        Set s = ThisWorkbook.Worksheets.Add
        s.Name = strWSName
    Else
        MsgBox "The sheet " & strWSName & " already exists!"
        Exit Sub
    End If
    'check whether sheet exists already, if not add and name
    
End Sub


Function SheetExists(strWSName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(strWSName)
    If Not ws Is Nothing Then SheetExists = True
End Function

Display More

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

HTH

Giorgio · Dec 9th 2003

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.

[Solved] VBA: flippin' macros

Participate now!

Share