Copy data from one file to the active workbook in VBA

[Gemdv]

Hello everyone.

I've been struggling trying to copy data from a file that I download on a regular bases and paste this data in another workbook. I created a loop in order to select the downloaded file from the folders, after that I try to select the data I need and then paste it in the active workbook. Once I run it, the error "-2147221080 (800401a8)" appears on the line "Set wsDestino = wbDestino.Worksheets("Registros")".

I pasted my code below, I hope some of you can help me with this.

Sub CopiarCeldas()


' Especificas que wb1 y wb2 van a ser archivos (guardas un archivo en cada variable).
Dim wbOrigen As Workbook, _
    wbDestino As Workbook, _
    wsOrigen As Excel.Worksheet, _
    wsDestino As Excel.Worksheet, _
    rngOrigen As Excel.Range, _
    rngDestino As Excel.Range


' Asignas la variable wb1 al archivo que está abierto y en uso actualmente.
Set wbDestino = ActiveWorkbook




' Con esto te va a aparecer una ventana para que selecciones un archivo de Excel para que lo abras.
archivo = Application.GetOpenFilename _
    (Title:="Seleccione el archivo de inventarios del día para agregar a la hoja (Input)", _
        filefilter:="Excel Files *.xls* (*.xls*),")
        


' Si el archivo no existe, te va a aparecer una ventana marcando un error y va a dejar de correr el código.
If archivo = False Then
    MsgBox "No existe el archivo", vbExclamation, "Error"
    Exit Sub


' Si el archivo si existe, lo abres y se asigna a la variable wb2.
Else
        
        Set wbOrigen = Workbooks.Open(Filename:=archivo)


End If


'Indicar las hojas de origen y destino
'Set wsDestino = ActiveWorkbook.Worksheets("Hoja2")
Set wsDestino = wbDestino.Worksheets("Registros")
Set wsOrigen = wbOrigen.Worksheets("Sheet0")
 
'Indicar la celda de origen y destino
Const celdaOrigen = "A2"
Const celdaDestino = "A2"
 
'Inicializar los rangos de origen y destino
Set rngOrigen = wsOrigen.Range(celdaOrigen)
Set rngDestino = wsDestino.Range(celdaDestino)
 
'Seleccionar rango de celdas origen
rngOrigen.Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
 
'Pegar datos en celda destino
rngDestino.PasteSpecial xlPasteValues
Application.CutCopyMode = False




End Sub

 

[mumps]

Do you actually have a sheet named "Registros" in your ActiveWorkbook? If you do, check to make sure that the sheet name doesn't have any leading or trailing spaces.

 

[Gemdv]

Hello Mumps.

I do have a sheet named "Registros". I already checked for spaces or anything else in the name and it all seems to be fine.

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual files which includes any macros you are currently using. Perhaps you could upload a copy of each file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to the each file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.