Copy data from one file to the active workbook in VBA

Gemdv

New Member
Joined
Nov 7, 2018
Messages
2
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.

Code:
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
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

New Member
Joined
Nov 7, 2018
Messages
2
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,242
Messages
5,623,573
Members
415,981
Latest member
Baltwin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top