Nicolaf
08-29-2013, 04:32 AM
Hi,
I have code below:
Private Sub CommandButton1_Click()
Dim List As Worksheet
Dim BkList As Range
Dim ShtList As Range
Dim FixList As Range
Dim LastRow As Long
Dim Iname As String
Dim SName As String
Dim NewWkBk As Workbook
Dim cel As Long
Set List = ThisWorkbook.Sheets("List")
With List
LastRow = .Range("B7").End(xlDown).Row
Set BkList = .Range("B7:B" & LastRow)
Set ShtList = .Range("A7:A" & LastRow)
Set FixList = .Range("C7:C" & LastRow)
Set FixList1 = .Range("D7:D" & LastRow)
Set FixList2 = .Range("E7:E" & LastRow)
End With
For cel = 1 To BkList.Count
Iname = BkList.Cells(cel).Value 'name with extension
On Error GoTo celnext 'In case worksheet does not exist
Set NewWkBk = Workbooks.Open(Filename:="P:\Lonib\Trades\" & Iname)
SName = ShtList.Cells(cel).Value 'sheet name
Workbooks(Iname).Sheets(SName).Calculate
Workbooks(Iname).Sheets(SName).Range("M6").Copy
FixList.Cells(cel).PasteSpecial Paste:=xlPasteValues
Workbooks(Iname).Sheets(SName).Range("N6").Copy
FixList1.Cells(cel).PasteSpecial Paste:=xlPasteValues
Workbooks(Iname).Sheets(SName).Range("O6").Copy
FixList2.Cells(cel).PasteSpecial Paste:=xlPasteValues
Workbooks(Iname).Sheets(SName).Calculate
Workbooks(Iname).Saved = True
Workbooks(Iname).Close
celnext:
Next cel
End Sub
What it does is open a workbook in a specific location and then if it does not find worksheet (workbook I made sure will always exist) then On Error takes you to celnext which makes code look for another workbook and continue macro.
What I need to do is add some code so that if macro does not find worksheet, so once it reached code:
Set NewWkBk = Workbooks.Open(Filename:="P:\Lonib\Trades\" & Iname)
SName = ShtList.Cells(cel).Value 'sheet name
then it closes the workbook which it has opened (and which contains sheet not found) and only then it goes to Next cel and continues macro.
At present workbook where sheet is not found will remain open and this causes problems.
How can I do this?
:dunno:dunno
I have code below:
Private Sub CommandButton1_Click()
Dim List As Worksheet
Dim BkList As Range
Dim ShtList As Range
Dim FixList As Range
Dim LastRow As Long
Dim Iname As String
Dim SName As String
Dim NewWkBk As Workbook
Dim cel As Long
Set List = ThisWorkbook.Sheets("List")
With List
LastRow = .Range("B7").End(xlDown).Row
Set BkList = .Range("B7:B" & LastRow)
Set ShtList = .Range("A7:A" & LastRow)
Set FixList = .Range("C7:C" & LastRow)
Set FixList1 = .Range("D7:D" & LastRow)
Set FixList2 = .Range("E7:E" & LastRow)
End With
For cel = 1 To BkList.Count
Iname = BkList.Cells(cel).Value 'name with extension
On Error GoTo celnext 'In case worksheet does not exist
Set NewWkBk = Workbooks.Open(Filename:="P:\Lonib\Trades\" & Iname)
SName = ShtList.Cells(cel).Value 'sheet name
Workbooks(Iname).Sheets(SName).Calculate
Workbooks(Iname).Sheets(SName).Range("M6").Copy
FixList.Cells(cel).PasteSpecial Paste:=xlPasteValues
Workbooks(Iname).Sheets(SName).Range("N6").Copy
FixList1.Cells(cel).PasteSpecial Paste:=xlPasteValues
Workbooks(Iname).Sheets(SName).Range("O6").Copy
FixList2.Cells(cel).PasteSpecial Paste:=xlPasteValues
Workbooks(Iname).Sheets(SName).Calculate
Workbooks(Iname).Saved = True
Workbooks(Iname).Close
celnext:
Next cel
End Sub
What it does is open a workbook in a specific location and then if it does not find worksheet (workbook I made sure will always exist) then On Error takes you to celnext which makes code look for another workbook and continue macro.
What I need to do is add some code so that if macro does not find worksheet, so once it reached code:
Set NewWkBk = Workbooks.Open(Filename:="P:\Lonib\Trades\" & Iname)
SName = ShtList.Cells(cel).Value 'sheet name
then it closes the workbook which it has opened (and which contains sheet not found) and only then it goes to Next cel and continues macro.
At present workbook where sheet is not found will remain open and this causes problems.
How can I do this?
:dunno:dunno