smart macro

[JAZZNAURA]

Hi all,

I have a macro which imports data from a text file, filters out certain info then paste it to a worksheet (let’s say a worksheet called “week 1”). My question is, is it possible to insert a line in the macro so that each time it runs it pastes the info in the next worksheet.
So, the next time it is runs it will insert the info in the worksheet called “week 2” and then the next time “week 3” and so on.

Is it possible?

Thank you

[Leith Ross]

Hello JAZZNAURA,

Since you didn't post your macro code, I am making the following assumptions. The worksheets are saved in the same workbook each time the macro runs, and the worksheet names are "week " plus a number. Under these conditions this code will work for you.

 Dim WeekNumber
  Dim Wks As Worksheet
  
    With ThisWorkbook.Worksheets
      WeekNumber = Split(.Item(.Count).Name, " ")
      WeekNumber = WeekNumber(1) + 1
      .Add After:=.Item(.Count)
    End With

    ActiveSheet.Name = "Week " & WeekNumber

Sincerely,
Leith Ross

[JAZZNAURA]

sorry, but yes your assumptions are correct.

do i insert this anywhere within my macro code?

[JAZZNAURA]

kinda new to this

[Leith Ross]

Hello JAZZNAURA,

Post your macro, and be sure to wrap it. Read the Forum rules first before you post.

http://www.excelforum.com/showthread.php?t=593996

Once I see your code, I can help you determine where the additional code should be placed.

Sincerely,
Leith Ross

[JAZZNAURA]

thanks again for your reply. had a go at inserting your code but no luck. any help would be happly received. if you need any more info please ask.
P.S. attached workbook

regards,

jazz

Sub run()
'
' run Macro
' Macro recorded 25/07/2007 by Jazz Naura
'
'
    Cells.Select
    Selection.ClearContents
    Selection.QueryTable.Delete
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;G:\huron.TXT", _
        Destination:=Range("A1"))
        .Name = "huron_12"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 11
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(9, 1, 1, 9, 1, 9, 1, 9)
        .TextFileFixedColumnWidths = Array(10, 8, 27, 8, 9, 3, 13)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Rows("32:144").Select
    Selection.ClearContents
    Range("B26").Select
    Cells.Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Rows("11:86").Select
    Selection.ClearContents
    Range("B14").Select
    Columns("A:D").Select
    Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A1:D10").Select
    Selection.Copy
    Sheets("Week 1").Select
    Range("A1:D10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

[JAZZNAURA]

all sorted, thanks Leith