Excel VBA?

[wicked23]

I'm using MS Excel '07 trying to write a macro where when you hit the button it opens to a floder and let's you choose a text file. Then it imports to excel, deletes a few cells, cut's and pastes some more, then i need it to sort. Everything works except the sort. Basically i don't know what to put in the red Quotes below.

Things i've tried:

myfile
*
*.*
*.305
Sheet2
Left it blank
deleted worksheets and put myfile

Any ideas????


Sub repair5()
'
' repair5 Macro
'
' Keyboard Shortcut: Ctrl+m
'
Sheets("Sheet2").Select
ChDir "E:\MODEM\305\305zip"
myfile = Application.GetOpenFilename("files,*.305")
Workbooks.OpenText Filename:=myfile, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), _
Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15 _
, 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2)), TrailingMinusNumbers:= _
True
Selection.ClearContents
Range("A2:C2").Select
Selection.ClearContents
Range("A338").Select
Selection.ClearContents
Range("Q3:S38").Select
Selection.Cut
Range("A3").Select
ActiveSheet.Paste
Columns("A:A").ColumnWidth = 22.57
Columns("B:B").ColumnWidth = 16.57
Range("B3:B38").Select
ActiveWorkbook.Worksheets("").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("").Sort.SortFields.Add Key:=Range("B3"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("").Sort
.SetRange Range("B3:B38")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ChDir "E:\MODEM\305\Repairs"

 

[office politics]

ActiveWorkbook.Worksheets(0).Sort.SortFields.Clear

this would reference the first worksheet. all worksheets have a name (string value, default "Sheet1") and a index (number value...int?) associated with them.

 

[wicked23]

Thanks OP but that did not work either

 

[office politics]

try worksheets(1)


i thought they were zero based


Worksheets in VBA Coding and in Worksheet Formulas

To use in a macro (note use of a single quote within double quotes in first example)
For csht = 1 To ActiveWorkbook.Sheets.Count 'worksheet or sheets
Cells(cRow - 1 + csht, cCol) = "'" & Sheets(csht).Name
Cells(cRow - 1 + csht, cCol + 1) = Sheets(Sheets(csht).Name).Range("A1").Value
Next csht

 

[wicked23]

1

That's it. Thank you so much.