Problems closing another worksheet by VBA

Question

Problems closing another worksheet by VBA

Navigation

#1 by (1 votes)

2

I'm running the code below for comparing data from different worksheets. It is running correctly, but I am not able to close the data source spreadsheets. I have already tried with '.Close', '.Quit' and '= Nothing', in all ways the 'subscript out of range' error appears.

Sub teste()

extrato = Range("B1").Value
comparativa = Range("B2").Value

Dim ws As Workbook
Set ws = Application.Workbooks.Add

    Range("B2").Select
    ActiveCell.FormulaR1C1 = "Contraparte"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "Faturamento"


Data = Replace(Replace(Now(), ":", "."), "/", "_")
ws.SaveAs ("C:\Users\leandro.lazari\Desktop\Financeiro\" & Data & ".xlsx")

'Abre Planilha extrato e copia lançamentos e valores
Workbooks.Open ("C:\Users\leandro.lazari\Desktop\Financeiro\" & extrato & ".xls")
    Range("E12:F12").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

'Cola dados de extrato
Workbooks.Open ("C:\Users\leandro.lazari\Desktop\Financeiro\" & Data & ".xlsx")
    Range("E2").Select
    ActiveSheet.Paste

'Abre Planilha extrato e copia lançamentos e valores
Workbooks.Open ("C:\Users\leandro.lazari\Desktop\Financeiro\" & comparativa & ".xlsx")

    Range("A2:B2").Select
    Range(Selection, Selection.End(xlDown)).Select

    With Selection
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlLTR
        .MergeCells = False
    End With
    Selection.Copy

'Cola dados de extrato
Workbooks.Open ("C:\Users\leandro.lazari\Desktop\Financeiro\" & Data & ".xlsx")
    Range("H2").Select
    ActiveSheet.Paste


'Checa os valores que convergem e retorna as convergências
i = 2
j = 3
    Do Until Range("I" & i) = ""

        Range("K" & i).Select
            ActiveCell.FormulaR1C1 = "=IFERROR(IF(VLOOKUP(R[0]C[-2],C[-5],1,FALSE), ""ENCONTRADO""), ""NÃO ENCONTRADO"")"

            If Cells(i, 11).Value = "ENCONTRADO" Then
                Cells(i, 8).Copy
                Cells(j, 2).PasteSpecial
                Cells(i, 9).Copy
                Cells(j, 3).PasteSpecial

            j = j + 1
            End If
      i = i + 1
    Loop

'limpa dados utilizados
Range("E:Z").ClearContents
Range("A:Z").ClearFormats

'formata largura das colunas e valores como moeda
    Columns("B:B").Select
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").Select
    Columns("C:C").EntireColumn.AutoFit
    Selection.Style = "Currency"
        Range("B2:C2").Select
    Selection.Font.Bold = True
    Selection.Font.Size = 12

ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub

vba excel-vba

asked by anonymous 13.06.2018 / 19:36

1 answer

PHP min ($ footage) bringing the wrong value How to make a promise linked with pure Javascript? [duplicate]

score 1 · Answer 1

With this piece of code you can close any workbook.

Dim wb As Workbook

'nesse caso eu atribuiria uma referência a variável wb
Set wb = Application.Workbooks.Open("<caminho\seuArquivo.xlsx>")

'com a referência atribuida ao WorkBook você pode fechar com o .Close
wb.Close