Can anybody help me out a bit please I am trying to set up some code to open workbooks that are in a list from N2 to N8 (with a header in N1) on sheet cert data. What I eventually need to achieve is to copy certain data from each workbook as it opens, but at the moment I can get the workbooks to open but not close.
I may be completely wrong but I am trying to use a set statement so I can then reference that to close the workbook down
This is what I have tried
But none of the above seem to work
Any help is appreciated
Full code below
I may be completely wrong but I am trying to use a set statement so I can then reference that to close the workbook down
This is what I have tried
VBA Code:
Set wbk = Workbooks.Open(filename)
Set wbk = (directory & ThisWorkbook.Sheets("Cert Data").Range("N" & i).Value & ".xlsm")
Set Workbook = ("Cert Data").Range("N" & i).Value & ".xlsm")
Any help is appreciated
Full code below
VBA Code:
Sub CopyFromAllRegisters()
'Application.DisplayAlerts = False
'Application.ScreenUpdating = False
Dim i As Integer
Dim c As Integer
Dim directory As String
Dim filename As String
Dim ws As Worksheet
Dim wb As Workbook
Dim wbk As Workbook
Set wbk = Workbooks.Open(filename)
Set wb = ActiveWorkbook
Set ws = Worksheets("Cert Data")
'define location of material registers
directory = "L:\MATERIALS\Material Certification\"
'-----------------------------------------------------------------code to open each material register in turn
i = 1 'loop starts from here
For i = i + 1 To Cells(Rows.Count, "N").End(xlUp).Row 'a is the column name where the filenames are stored
'define filename of material registers
filename = Dir(directory & ThisWorkbook.Sheets("Cert Data").Range("N" & i).Value & ".xlsm")
If filename = "" Then 'check if material register does not exist
Continue:
Else
Workbooks.Open (directory & filename), ReadOnly:=True 'open material register
'-----------------------------------------------------------------Need to add my code here
''-----------------------------------------------------------------to here
End If
wbk.Close (False)
Next i 'loop ends here and it will continue to last material register as it works down the list
'Application.DisplayAlerts = True
'Application.ScreenUpdating = True
End Sub