Nicolaf
07-08-2013, 04:49 AM
Hi,
I have a macro which looks for a Cell in a specific sheet of a file (File Fruit) then copies it onto another file (File Data) and then looks for a Cell in a specific sheet of another file (File Veg) and again copies it onto file Data.
I have put an error Handler in case the sheet does not exist so I get a message telling me "Sorry missing sheet".
If the error occurs the second time round then all OK if error occurs on first search then I get message "Sorry missing sheet" and macro stops.
What I would like to do is to receive the message "Sorry missing sheet" but then to continue the macro the point it stopped (so would like to do second search).
Code below:
Private Sub CommandButton1_Click()
On Error GoTo ErrorHandler
ActiveSheet.Calculate
IName = ThisWorkbook.Sheets("List").Range("B7").Value 'name with extension
Set NewWkbk = Workbooks.Open(Filename:="P:\Lonib\" & IName)
SName = ThisWorkbook.Sheets("List").Range("A7").Value 'sheet name
NewWkbk.Sheets(SName).Select
Windows(IName).Activate
ActiveSheet.Range("M6").Select
Selection.Copy
Windows("Data.xls").Activate
Sheets("List").Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(IName).Activate
Application.CutCopyMode = False
ActiveWindow.Close
ActiveSheet.Calculate
IName = ThisWorkbook.Sheets("List").Range("B8").Value 'name with extension
Set NewWkbk = Workbooks.Open(Filename:="P:\Lonib\" & IName)
SName = ThisWorkbook.Sheets("List").Range("A8").Value 'sheet name
NewWkbk.Sheets(SName).Select
Windows(IName).Activate
ActiveSheet.Range("M6").Select
Selection.Copy
Windows("Data.xls").Activate
Sheets("List").Range("C8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(IName).Activate
Application.CutCopyMode = False
ActiveWindow.Close
Exit Sub
ErrorHandler:
MsgBox ("Sorry missing sheet")
End Sub
How can I change my macro to do that?
:dunno :dunno
Nix
I have a macro which looks for a Cell in a specific sheet of a file (File Fruit) then copies it onto another file (File Data) and then looks for a Cell in a specific sheet of another file (File Veg) and again copies it onto file Data.
I have put an error Handler in case the sheet does not exist so I get a message telling me "Sorry missing sheet".
If the error occurs the second time round then all OK if error occurs on first search then I get message "Sorry missing sheet" and macro stops.
What I would like to do is to receive the message "Sorry missing sheet" but then to continue the macro the point it stopped (so would like to do second search).
Code below:
Private Sub CommandButton1_Click()
On Error GoTo ErrorHandler
ActiveSheet.Calculate
IName = ThisWorkbook.Sheets("List").Range("B7").Value 'name with extension
Set NewWkbk = Workbooks.Open(Filename:="P:\Lonib\" & IName)
SName = ThisWorkbook.Sheets("List").Range("A7").Value 'sheet name
NewWkbk.Sheets(SName).Select
Windows(IName).Activate
ActiveSheet.Range("M6").Select
Selection.Copy
Windows("Data.xls").Activate
Sheets("List").Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(IName).Activate
Application.CutCopyMode = False
ActiveWindow.Close
ActiveSheet.Calculate
IName = ThisWorkbook.Sheets("List").Range("B8").Value 'name with extension
Set NewWkbk = Workbooks.Open(Filename:="P:\Lonib\" & IName)
SName = ThisWorkbook.Sheets("List").Range("A8").Value 'sheet name
NewWkbk.Sheets(SName).Select
Windows(IName).Activate
ActiveSheet.Range("M6").Select
Selection.Copy
Windows("Data.xls").Activate
Sheets("List").Range("C8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(IName).Activate
Application.CutCopyMode = False
ActiveWindow.Close
Exit Sub
ErrorHandler:
MsgBox ("Sorry missing sheet")
End Sub
How can I change my macro to do that?
:dunno :dunno
Nix