Passing a Cell value to a Macro Sub

[gjackson]

I have an Excel workbook that when you click on a button it asks you for the txt file you want to use - (they happen to be wk1 files). Then it copies and pastes the contents into certain sheets in the current workbook. I need to have it so the user types in the name of the wk1 file in a cell and hits enter and then the macro will run automatically after that. This will prevent the user from having to go hunt the file down everytime - should make the process a hair faster. - Thanks in advance, George

MsgBox "INSERT 750 (*.wk1) Active File"

ChDir "\\aragorn\SAN\Lode\Clips"
FileToOpen = Application.GetOpenFilename("BOM/Data Files (*.WK1), *.WK1")
If FileToOpen = "False" Then
Exit Sub
End If
Workbooks.Open Filename:=FileToOpen
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy

Windows(2).Activate
Sheets("Lode750").Select
Range("A1").Select
ActiveSheet.Paste



' Sort 750
Sheets("Lode750").Select
Rows("5:100").Select
Selection.Sort Key1:=Range("I5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal



' Insert 550 Specs
MsgBox "INSERT 550 (*.wk1) Active File"

ChDir "\\aragorn\SAN\Lode\Clips"
FileToOpen = Application.GetOpenFilename("BOM/Data Files (*.WK1), *.WK1")
If FileToOpen = "False" Then
Exit Sub
End If
Workbooks.Open Filename:=FileToOpen
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy


Windows(2).Activate
Sheets("Lode550").Select
Range("A1").Select
ActiveSheet.Paste


' Sort 550
Sheets("Lode550").Select
Rows("5:100").Select
Selection.Sort Key1:=Range("I5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


'Find/Replace 750 (For this forum I deleted 90 percent of this "find/Replace" section.)
Sheets("Lode750").Select
Selection.Replace What:="FWD PAD", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="REV PAD", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="REV EQ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="FWD EQ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=" SCS", Replacement:="SCS", LookAt:=xlPart, _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("RV-Ampdata").Select
Range("A1").Select




' Close WK1 files
Windows(2).Activate
ActiveWorkbook.Close
Windows(1).Activate

Windows(2).Activate
ActiveWorkbook.Close
Windows(1).Activate


' RENAME TAB
Sheets("RV-Ampdata").Select
Sheets("RV-Ampdata").Name = "Ampdata"

Sheets("Ampdata").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A7").Select



' deletes tabs
Application.DisplayAlerts = False
Sheets("SAHybrid").Select
ActiveWindow.SelectedSheets.Delete
Sheets("SA-Ampdata").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Cover").Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True

MsgBox "FASTER THAN A SUPERMAN, Huh"



End Sub

[davesexcel]

Hi,
Try this, it is a code that goes into the worksheet module
when you enter a file name in A1 the file will open, I don't have any .wk1 files so do not know if it actually works but it does when i change the suffix to .txt for text files

Please Login or Register  to view this content.