0

Getting the error Run-time error '1004': Method 'Range' of object'_Worksheet' failed but I'm not on the kind of the of VBA to understand where I'm doing wrong. The idea of the code is to let the user choose one workbook, to be used as a "base" file, and choose another where defined range from the base workbook (ws_origin.Range(Cells(2, 1), Cells(2, lCol)).Copy) will be pasted.

Appreciate any help you can provide!

Option Explicit
Sub search_data()
'Author:    Rafael Simonário de Carvalho    rafacarvalho93@gmail.com
'Date:      July, 2020.
'
'
'Instructions:  Use this Workbook as a tool to
Dim wb_origin As Workbook   'Planilha de origem dos dados
Dim wb_report As Workbook   'Planilha de destino dos dados
Dim ws_origin As Worksheet  'Aba da planilha de origem dos dados
Dim ws_report As Worksheet  'Aba da planilha de destino dos dados
Dim wb_or   As String       'Variável para armazenar o caminho da planilha de origem
Dim wb_re   As String       'Variável para armazenar o caminho da planilha de destino
Dim lCol    As Integer      'Variável que armazenará a qnt. de colunas da planilha de origem
Dim lrow    As Integer      'Variável que armazenará a qnt. de colunas da planilha de origem
Dim var_copy    As Range    'Variável para armazenar uma região que será copiada


'Definir nomes e locais
    MsgBox "Selecionar o Arquivo de origem dos dados."
    'Abrir um navegador para selecionar o arquivo gerado pelo ERP
        wb_or = Application.GetOpenFilename("All XLSX files (*.xlsx*), *.xlsx", , "Teste")
        Set wb_origin = Application.Workbooks.Open(wb_or)
        Set ws_origin = wb_origin.Sheets(1)
        
    MsgBox "Selecionar o Arquivo de destino dos dados."
    'Abrir um navegador para selecionar o arquivo gerado pelo ERP
        wb_re = Application.GetOpenFilename("All XLSX files (*.xlsx*), *.xlsx", , "Teste")
        Set wb_report = Application.Workbooks.Open(wb_re)
        Set ws_report = wb_report.Sheets(1)
        
'Retira a atualização de tela e muda o  cálculo para manual para poder aumentar a velocidade
'de processamento
    'Application.ScreenUpdating = False
    'Application.Calculation = xlCalculationManual
        
'Encontra última coluna usada
lCol = ws_origin.Cells(2, Columns.Count).End(xlToLeft).Column

'ws_origin.Range("A1:A2").Value = 100
'ws_report.Range("A1:A2").Value = lCol

ws_origin.Range(Cells(2, 1), Cells(2, lCol)).Copy

ws_report.Range(Cells(2, 1)).PasteSpecial

    
End Sub
Rafael Carvalho
  • 1
  • 4
  • You need to qualify which worksheet the `Cells` are on too. See the linked duplicates. – BigBen Jul 30 '20 at 20:52
  • Btw, change `ws_report.Range(Cells(2, 1)).PasteSpecial` to `ws_report.Cells(2, 1).PasteSpecial` – BigBen Jul 30 '20 at 20:53
  • Thank you @bigBen, I did a search around here about my error but didn't find anything (still learning how the Stackoverflow and it's search engine works). Read those questions you pointed and got to an answer. `ws_report.Range(ws_report.Cells(2, 1), ws_report.Cells(2, lCol)).Value = ws_origin.Range(ws_origin.Cells(2, 1), ws_origin.Cells(2, lCol)).Value` Thnak you again by the help. – Rafael Carvalho Jul 30 '20 at 21:09

0 Answers0