Hello,
I am trying to create a function so that I can set the opened workbook as the variable to reference in other Sub-routines. Here is a snippet of what I started based on how I was using it before separating the sub routines.
This is a snippet of the sub-routine I am trying to call it in and how it's used
This is where I start pulling information and formatting in the opened workbook
Any ideas?
I am trying to create a function so that I can set the opened workbook as the variable to reference in other Sub-routines. Here is a snippet of what I started based on how I was using it before separating the sub routines.
VBA Code:
Function closedbook() As Variant
Dim closedbook As Workbook
On Error Resume Next
Set closedbook = Workbooks.Open(FilePath)
On Error GoTo 0
End Function
This is a snippet of the sub-routine I am trying to call it in and how it's used
VBA Code:
Sub ImportSheets()
UserForm1.Show vbModeless
Application.ScreenUpdating = False
Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
Dim DialogBox As FileDialog
Dim FilePath As String
Dim SheetName As String
Dim wb As Workbook
Dim closedbook As Workbook
Set wb = ThisWorkbook
wb.Sheets("Revision History").Visible = True
'Allow user to select the Estimate files that will be copied
Set DialogBox = Application.FileDialog(msoFileDialogFilePicker)
DialogBox.InitialView = msoFileDialogViewList
DialogBox.InitialFileName = "\\nu.com\data\SharedData\Estimating\"
DialogBox.Title = "Select Estimates to copy " '& FileType
DialogBox.AllowMultiSelect = True
DialogBox.Filters.Clear
DialogBox.Show
If DialogBox.SelectedItems.Count = 1 Then
FilePath = DialogBox.SelectedItems(1)
End If
Dim i As Integer
'Makes a count of the number of files selected
For i = 1 To DialogBox.SelectedItems.Count
'What are we copying...
FilePath = DialogBox.SelectedItems(i)
[B]Set closedbook = Workbooks.Open(FilePath)
closedbook.Activate[/B]
Call UnProtect
Call StartImport
[B]closedbook.Close SaveChanges:=False[/B]
Next i
Call Protect
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Unload UserForm1
End Sub
This is where I start pulling information and formatting in the opened workbook
VBA Code:
Sub StartImport()
[B]closedbook.Sheets("Data Input Sheet").Select[/B]
'do some stuff
[B]closedbook.Sheets(Sheetcover).Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)[/B]
ThisWorkbook.Worksheets(Sheetcover).Activate
Dim cov As String
cov = wb.Sheets(Sheetcover).Range("D1").Value & "_" & Range("C1").Value
End Sub
Any ideas?