Setting a string variable as the file name
 

I am trying to set a string variable as the file name for a workbook
so that I can open another workbook and copy sheets into the 1st
workbook. Here is what I have so far:

Sub MoveSheets()

Dim BkName As String
Dim NumSht As Integer
Dim BegSht As Integer
Dim FileNum As String
Dim ActiveWKBK As String

'Starts with third sheet - replace with index number of starting
sheet.
BegSht = 3
'Moves 7 sheets - replace with number of sheets to move.
NumSht = 7
BkName = ActiveWorkbook.Name

'Sets the active directory
ChDir "C:\Documents and Settings\hx27484\Desktop\Work Books"

'User input the Assembly Number to get workbook
On Error Resume Next
FileNum = InputBox("Enter Assembly Number")
If Err.Number = 0 Then
ChDir "C:\Documents and Settings\hx27484\Desktop\Work Books"
Workbooks.Open Filename:=FileNum
If Err.Number = 0 Then
'file opened ok

For x = 1 To NumSht
'Moves third sheet in source to front of designated
workbook.
Workbooks(FileNum).Sheets(BegSht).Move _
Befo=Workbooks(BkName).Sheets(4)
'In each loop, the next sheet in line becomes indexed as
number 3.

Next

'Response if the file could not open
Else
MsgBox "Could Not Open File, Try Again"
End If
Else
MsgBox "Invalid File Number"
End If

End Sub



However in the code line below the file names are usually entered as
Workbooks("File Name.xls"):
Workbooks(FileNum).Sheets(BegSht).Move _
Befo=Workbooks(BkName).Sheets(4)

I would like to use a variable here because the workbook names wont be
the same. I am all out of ideas on how to make this work, any ideas???

Setting a string variable as the file name
 

Not 100% sure what you're asking but I think you want:
FileNum = InputBox("Enter Assembly Number")
FileNum = FileNum & ".xls"

HTH,

wrote in message
oups.com...
I am trying to set a string variable as the file name for a workbook
so that I can open another workbook and copy sheets into the 1st
workbook. Here is what I have so far:

Sub MoveSheets()

Dim BkName As String
Dim NumSht As Integer
Dim BegSht As Integer
Dim FileNum As String
Dim ActiveWKBK As String

'Starts with third sheet - replace with index number of starting
sheet.
BegSht = 3
'Moves 7 sheets - replace with number of sheets to move.
NumSht = 7
BkName = ActiveWorkbook.Name

'Sets the active directory
ChDir "C:\Documents and Settings\hx27484\Desktop\Work Books"

'User input the Assembly Number to get workbook
On Error Resume Next
FileNum = InputBox("Enter Assembly Number")
If Err.Number = 0 Then
ChDir "C:\Documents and Settings\hx27484\Desktop\Work Books"
Workbooks.Open Filename:=FileNum
If Err.Number = 0 Then
'file opened ok

For x = 1 To NumSht
'Moves third sheet in source to front of designated
workbook.
Workbooks(FileNum).Sheets(BegSht).Move _
Befo=Workbooks(BkName).Sheets(4)
'In each loop, the next sheet in line becomes indexed as
number 3.

Next

'Response if the file could not open
Else
MsgBox "Could Not Open File, Try Again"
End If
Else
MsgBox "Invalid File Number"
End If

End Sub



However in the code line below the file names are usually entered as
Workbooks("File Name.xls"):
Workbooks(FileNum).Sheets(BegSht).Move _
Befo=Workbooks(BkName).Sheets(4)

I would like to use a variable here because the workbook names wont be
the same. I am all out of ideas on how to make this work, any ideas???