Posts by Giorgio

    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

    the table is 8r x 9c and repeats 650 times down the page at intervals of 13 rows. I've got a load of spreadsheets to go through and it takes about 10-20 minutes to got through each sheet manually. I'm certain a macro could reduce the boring and repeative bits of this operation.


    Mainly creating the array, copying it down the page, copying the single resulting array to a new sheet.


    The array I'm trying to create is 1 X 72. I would like to copy this new array onto a new sheet with 1 row for each of the 650 tables. I have a small section as a sample 47kb if this helps explain my predicament more clearly.


    thanks for taking the time to respond.

    I'm performing ranking functions of for example RANK(BJ8716,BJ$2:BJ$8867). I've got between 30 colums such as this and 90 colums in spreadsheets.


    Is there a limit to the number of calculations excel can perform?


    PC - P4, 512 mb ram 120gb hard disk


    I've got a spreadsheet with seven workbooks, 75 ish mb., that kept crashing when trying to calculate.
    I've cut it into seven seperate sheets as I thought the file might be too big to do all the calculations simultaneously. The files range from 8 to 21mb.


    I have a spreadsheet with about 9500 rows and up to 110 columns. Of these about 30 are ranks. The sheet is set to manual calculations. when i try to do a page recalculate is stops responding and i don't get any progress about cell updates.


    as you would expect the sheets smaller than 10mb - take over 1 minute to work.


    anyone know whats going on?

    Quote

    Originally posted by thomach
    Are you familiar with Excel VBA? It should be fairly easy to write a macro that would automate this task if your data column labels are consistent.


    not really, i can understand some vba and can make some modifications but its been a long time since i wrote any code. the other problem is the data isn't consitent in all the spreadsheets, but i suppose it might be easier to edit the worksheet if i could get some sample code.

    I've always done this manually there must be a better way.


    I select the named range, go to sort select the column, copy column b, paste it into the new rank column, copy this column and paste special, values.


    as i've got loads of sheets to do this time thought i'd ask if anyone knows of a better way of doing this.