Guru do Excel e Power BI

Enviado: **08 Jan 2021 às 21:07**

Então pessoal estou estou me arriscando nas macros e VBA criei duas macros

1 - Que importa os dados escritos pra abas (banco de dados)
2 - Outra que coloca o banco de dados em ordem.

O fato e que quando coloco o banco de dados oculto as duas macros da erro, entendo que este erro e porque a aba esta oculta.

Teria como eu configura somente o codigo VBA e isso ser resolvido?

segue código.
Fico grato se alguém puder me orientar.

Código: Selecionar todos

Sub GRAVACLIENTE()
'
' GRAVACLIENTE Macro
'

'
    Range("B4:Q4").Select
    Selection.Copy
    ActiveSheet.Previous.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 127
    ActiveWindow.ScrollRow = 680
    ActiveWindow.ScrollRow = 818
    ActiveWindow.ScrollRow = 968
    ActiveWindow.ScrollRow = 1141
    ActiveWindow.ScrollRow = 1291
    ActiveWindow.ScrollRow = 2915
    ActiveWindow.ScrollRow = 2995
    ActiveWindow.ScrollRow = 3007
    ActiveWindow.ScrollRow = 3030
    ActiveWindow.ScrollRow = 3168
    ActiveWindow.ScrollRow = 3341
    ActiveWindow.ScrollRow = 3445
    ActiveWindow.ScrollRow = 3629
    ActiveWindow.ScrollRow = 3709
    ActiveWindow.ScrollRow = 4032
    ActiveWindow.ScrollRow = 4044
    ActiveWindow.ScrollRow = 4067
    ActiveWindow.ScrollRow = 4078
    ActiveWindow.ScrollRow = 4504
    ActiveWindow.ScrollRow = 4539
    ActiveWindow.ScrollRow = 4608
    ActiveWindow.ScrollRow = 4781
    ActiveWindow.ScrollRow = 4804
    ActiveWindow.ScrollRow = 4815
    ActiveWindow.ScrollRow = 4907
    ActiveWindow.ScrollRow = 4930
    ActiveWindow.ScrollRow = 4977
    ActiveCell.Offset(4997, 0).Range("A1").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Next.Select
    Application.CutCopyMode = False
    Selection.ClearContents
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
Sub ORDENARCLIENTE()
'
' ORDENARCLIENTE Macro
'

'
    ActiveSheet.Previous.Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Range("A1:P162").Select
    ActiveWorkbook.Worksheets("BD - CLIENTES").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("BD - CLIENTES").Sort.SortFields.Add Key:=Range( _
        "A2:A162"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("BD - CLIENTES").Sort
        .SetRange Range("A1:P4999")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveCell.Select
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 173
    ActiveWindow.ScrollRow = 277
    ActiveWindow.ScrollRow = 496
    ActiveWindow.ScrollRow = 2742
    ActiveWindow.ScrollRow = 2776
    ActiveWindow.ScrollRow = 2800
    ActiveWindow.ScrollRow = 3433
    ActiveWindow.ScrollRow = 3491
    ActiveWindow.ScrollRow = 3675
    ActiveWindow.ScrollRow = 3721
    ActiveWindow.ScrollRow = 4067
    ActiveWindow.ScrollRow = 4101
    ActiveWindow.ScrollRow = 4228
    ActiveWindow.ScrollRow = 4619
    ActiveWindow.ScrollRow = 4654
    ActiveWindow.ScrollRow = 4677
    ActiveWindow.ScrollRow = 4700
    ActiveWindow.ScrollRow = 4712
    ActiveWindow.ScrollRow = 4746
    ActiveWindow.ScrollRow = 4758
    ActiveWindow.ScrollRow = 4792
    ActiveWindow.ScrollRow = 4804
    ActiveWindow.ScrollRow = 4827
    ActiveWindow.ScrollRow = 4861
    ActiveWindow.ScrollRow = 4873
    ActiveWindow.ScrollRow = 4884
    ActiveWindow.ScrollRow = 4896
    ActiveWindow.ScrollRow = 4907
    ActiveWindow.ScrollRow = 4919
    ActiveWindow.ScrollRow = 4930
    ActiveWindow.ScrollRow = 4942
    ActiveWindow.ScrollRow = 4953
    ActiveWindow.ScrollRow = 4965
    ActiveWindow.ScrollRow = 4977
    ActiveCell.Offset(4998, 0).Range("A1").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Next.Select
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Enviado: **09 Jan 2021 às 10:37**

Bom-dia

Experimente colocar no começo do código:

Código: Selecionar todos

Sheets("Nome da Planilha").Visible = True

E no fim do código:

Código: Selecionar todos

Sheets("Nome da Planilha").Visible = False

Enviado: **10 Jan 2021 às 21:11**

@fcarlosc2018, Obrigado pela resposta era exatamente o que eu precisava.

Agradeço.

Guru do Excel e Power BI

Macro Erro quando a Aba esta oculta

Macro Erro quando a Aba esta oculta

Re: Macro Erro quando a Aba esta oculta

Re: Macro Erro quando a Aba esta oculta