File Name

[jrobin5881]

I have a file that I pull down from the mainframe. I wrote a macro that formats the file and opens a template file and then stops. I have been taking the info from the formatted file worksheet, copying and pasting it into the template and saving it.

I recorded a macro to select the range from the template and copy and paste it into the template. I intend to append this snippet to the end of the macro that I have.

Here's the issue: When I recorded the macro it uses the name of the file as a reference of which workbook and worksheet to select from. So I added a variable where my macro stops and I type in the name of the formatted file. The macro then continues and copies and pastes the info just fine. What I'd like to do is NOT have to input the info and programitically have my macro find the name of the workbook and insert it. Here's my code as it exists now:

Sub CutPaste()
'
' CutPaste Macro
' Macro recorded 12/29/2006 by Authorized User
'

WkBk = InputBox("Enter Workbook Name-ie 088.csv")

Windows(WkBk).Activate
Range("J1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Final_Template.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("FLTS").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(WkBk).Activate
Range("S2").Select

End Sub

 

[CBasicAsslember]

It is not really clear which workbook has the macros. Would also need to see the code that you use to open up the other workbook. With respect to the workbook names this thread may be of some assistance.

 

[PHV]

You may play with ActiveWorkbook and ThisWorkbook.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886