Transfer data from one workbook to another in different folders via vba.

[MS_1977]

I'm having trouble with a vba code, to copy data from one Workbook to another, in different folders. Would there be any way to accomplish this process via code?

 

[MS_1977]

I need to take the data from the Planejado_Matriz_Atualizado spreadsheet to the other.

 

[mumps]

Thank you for the files. I still don't understand what data you want to copy and where you want to paste it. I realize that there is a language barrier, but in order for me to help, I need to know the sheet name that contains the data you want to copy, the cells you want to copy, the sheet name and location on that sheet where you want to paste the data. Please try to explain in detail referring to specific cells, rows, columns and sheets, using a few examples from your data.

 

[MS_1977]

You actually don't need my email address. You have have to create a Google Drive account for yourself to be able to use it.

Mumps,
I need to take the data from the Planejado_Matriz_Atualizado spreadsheet to the other.

 

[MS_1977]

Thank you for the files. I still don't understand what data you want to copy and where you want to paste it. I realize that there is a language barrier, but in order for me to help, I need to know the sheet name that contains the data you want to copy, the cells you want to copy, the sheet name and location on that sheet where you want to paste the data. Please try to explain in detail referring to specific cells, rows, columns and sheets, using a few examples from your data.

Mumps,
In the Consolidado_Das_Cargas Sheet, I need to copy the data from columns C, B, H, AR and U, all the rows of these columns, from row 3 until there is data.
That would be mumps, in a nutshell.

And paste in the Programação de Cargas D+1, on the BD-Consolidated sheet.

 

[mumps]

Try this macro in the Planejado_Matriz_Atualizado.xlsb workbook. Make sure that both workbooks are open. The data will be pasted at the bottom of Sheet "BD-Planejado".

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, lRow As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Consolidado_De_Cargas")
    Set desWS = Workbooks("Programação de Cargas D+1.xlsb").Sheets("BD-Planejado")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lRow = desWS.Columns(2).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    Union(Range("C3:C" & LastRow - 3), Range("B3:B" & LastRow - 3), Range("H3:H" & LastRow - 3), Range("AR3:AR" & LastRow - 3), Range("U3:U" & LastRow - 3)).Copy
    desWS.Range("B" & lRow).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[MS_1977]

Try this macro in the Planejado_Matriz_Atualizado.xlsb workbook. Make sure that both workbooks are open. The data will be pasted at the bottom of Sheet "BD-Planejado".

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, lRow As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Consolidado_De_Cargas")
    Set desWS = Workbooks("Programação de Cargas D+1.xlsb").Sheets("BD-Planejado")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lRow = desWS.Columns(2).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    Union(Range("C3:C" & LastRow - 3), Range("B3:B" & LastRow - 3), Range("H3:H" & LastRow - 3), Range("AR3:AR" & LastRow - 3), Range("U3:U" & LastRow - 3)).Copy
    desWS.Range("B" & lRow).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Mumps,
The error occurred, according to the attached print.


runtime error '9': Subcrystal out of range

 

[mumps]

I tested the macro using the two files you uploaded and it worked properly without error. If you are using the macro in different files, please upload copies of the files that are giving you the error.

 

[MS_1977]

I tested the macro using the two files you uploaded and it worked properly without error. If you are using the macro in different files, please upload copies of the files that are giving you the error.

Good afternoon!
Mumps,
The error is occurring on the command line below, but the point is that the files are in separate folders on the company's network.

Set desWS = Workbooks("Programação de Cargas D+1.xlsb").Sheets("BD-Planejado")

 

[mumps]

If "Programação de Cargas D+1.xlsb" is not open and you want the macro to open it, I need to know the full path to the folder where that file is saved.

 

[MS_1977]

If "Programação de Cargas D+1.xlsb" is not open and you want the macro to open it, I need to know the full path to the folder where that file is saved.

Mumps, Good afternoon!
This is the way in which the Load Programming Worksheet will be opened and saved data, from the other worksheet.

\\Loja5470\pac$\Delivery\TRANSPORTE ESTRATÉGICO\38 - Planejamento Logistico\Roteirização\Programação de Cargas D+1.xlsb