macro pular planilha se não encontrada.
Enviado: Qui Mai 16, 2019 9:43 am
Pessoal, bom dia.
tenho a macro abaixo:
O que eu preciso é que, por exemplo, se a macro não encontrar a planilha Esbs (2), a macro continua, sem dar mensagem de erro ou travar.
Seria como ela "pular" a planilha se não encontrar e ir para próxima, e se ela encontrar, processo normal (abrir a planilha e "pegar" as infos que são requeridas). Podem me ajudar, por favor?
Sub Macro_Esbs ()
'Inicio códio EAN
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Resume Next
Workbooks.Open Filename:= _
"C:\Users\esantos10\OneDrive - RBs\Documents\ABR\Meses\04 - abr - 19\Esbs (1) - abr19.xlsm"
Range("N5").Select
ActiveCell.FormulaR1C1 = "=CELL(""filename"")"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ultima = Range("D5").End(xlDown).Row
Range("N6:N" & ultima).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Windows("Esbs (1) - abr19.xlsm").Activate
Call PreencheZeros_1
ultima = Range("D5").End(xlDown).Row
Range("D5:D" & ultima).Select
Selection.Copy
Windows("DASH - macro").Activate
While ActiveCell.Text <> ""
ActiveCell.Offset(1, 0).Select
Wend
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Workbooks.Open Filename:= _
"C:\Users\esantos10\OneDrive - RBs\Documents\ABR\Meses\04 - abr - 19\Esbs (2) - abr19.xlsm"
Range("N5").Select
ActiveCell.FormulaR1C1 = "=CELL(""filename"")"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ultima = Range("D5").End(xlDown).Row
Range("N6:N" & ultima).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Windows("Esbs (2) - abr19.xlsm").Activate
Call PreencheZeros_1
ultima = Range("D5").End(xlDown).Row
Range("D5:D" & ultima).Select
Selection.Copy
Windows("DASH - macro").Activate
While ActiveCell.Text <> ""
ActiveCell.Offset(1, 0).Select
Wend
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Workbooks.Open Filename:= _
"C:\Users\esantos10\OneDrive - RBs\Documents\ABR\Meses\04 - abr - 19\Esbs (3) - abr19.xlsm"
Range("N5").Select
ActiveCell.FormulaR1C1 = "=CELL(""filename"")"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ultima = Range("D5").End(xlDown).Row
Range("N6:N" & ultima).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Windows("Esbs (3) - abr19").Activate
Call PreencheZeros_1
ultima = Range("D5").End(xlDown).Row
Range("D5:D" & ultima).Select
Selection.Copy
Windows("DASH - macro").Activate
While ActiveCell.Text <> ""
ActiveCell.Offset(1, 0).Select
Wend
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
tenho a macro abaixo:
O que eu preciso é que, por exemplo, se a macro não encontrar a planilha Esbs (2), a macro continua, sem dar mensagem de erro ou travar.
Seria como ela "pular" a planilha se não encontrar e ir para próxima, e se ela encontrar, processo normal (abrir a planilha e "pegar" as infos que são requeridas). Podem me ajudar, por favor?
Sub Macro_Esbs ()
'Inicio códio EAN
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Resume Next
Workbooks.Open Filename:= _
"C:\Users\esantos10\OneDrive - RBs\Documents\ABR\Meses\04 - abr - 19\Esbs (1) - abr19.xlsm"
Range("N5").Select
ActiveCell.FormulaR1C1 = "=CELL(""filename"")"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ultima = Range("D5").End(xlDown).Row
Range("N6:N" & ultima).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Windows("Esbs (1) - abr19.xlsm").Activate
Call PreencheZeros_1
ultima = Range("D5").End(xlDown).Row
Range("D5:D" & ultima).Select
Selection.Copy
Windows("DASH - macro").Activate
While ActiveCell.Text <> ""
ActiveCell.Offset(1, 0).Select
Wend
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Workbooks.Open Filename:= _
"C:\Users\esantos10\OneDrive - RBs\Documents\ABR\Meses\04 - abr - 19\Esbs (2) - abr19.xlsm"
Range("N5").Select
ActiveCell.FormulaR1C1 = "=CELL(""filename"")"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ultima = Range("D5").End(xlDown).Row
Range("N6:N" & ultima).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Windows("Esbs (2) - abr19.xlsm").Activate
Call PreencheZeros_1
ultima = Range("D5").End(xlDown).Row
Range("D5:D" & ultima).Select
Selection.Copy
Windows("DASH - macro").Activate
While ActiveCell.Text <> ""
ActiveCell.Offset(1, 0).Select
Wend
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Workbooks.Open Filename:= _
"C:\Users\esantos10\OneDrive - RBs\Documents\ABR\Meses\04 - abr - 19\Esbs (3) - abr19.xlsm"
Range("N5").Select
ActiveCell.FormulaR1C1 = "=CELL(""filename"")"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ultima = Range("D5").End(xlDown).Row
Range("N6:N" & ultima).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Windows("Esbs (3) - abr19").Activate
Call PreencheZeros_1
ultima = Range("D5").End(xlDown).Row
Range("D5:D" & ultima).Select
Selection.Copy
Windows("DASH - macro").Activate
While ActiveCell.Text <> ""
ActiveCell.Offset(1, 0).Select
Wend
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste