Guru do Excel e Power BI

Workbooks(Sheets("Configuração").Range("E9")).Activate

Option Explicit

Public Sub tranferirgeral()
    Dim strBackup       As String
    Dim lngUltLin       As Long
    Dim lngUltCol       As Long
    Dim vrtDados        As Variant
    Dim wbkBackup       As Workbook
    
    Application.ScreenUpdating = False
    
    With wshHist
        strBackup = .Range("S2").Value2                                 'Caminho do arquivo de backup
        
        lngUltLin = .Cells(.Rows.Count, 1).End(xlUp).Row                '?ltima linha com dados
        lngUltCol = .Cells(2, .Columns.Count).End(xlToLeft).Column      '?ltima coluna com dados
        vrtDados = .Range(.Cells(2, 1), .Cells(lngUltLin, lngUltCol))   'Dados a serem transferidos
    End With
        
    Set wbkBackup = Workbooks.Open(strBackup)                          'Abre o arquivo de backup
    
    With wbkBackup.Worksheets("Hist?ricosEmpresas")
        .Cells.ClearContents                                            'Limpa todo o conte?do anterior do backup
        .Range(.Cells(2, 1), .Cells(lngUltLin, lngUltCol)) = vrtDados   'Copia os dados para o backup
        
        wbkBackup.Close SaveChanges:=True                               'Fecha o arquivo de backup
    End With
        
    Set wbkBackup = Nothing
    
    Application.ScreenUpdating = True
    
    VBA.MsgBox Prompt:="Dados transferidos com sucesso", Title:="Backup de dados"
End Sub