View original (French)

Copy-paste data from one file to another

devdev21 Posted messages 6 Status Member -  
 Gilles -
Hello,

I’m looking to create a macro that will retrieve data from a specific tab of a table in one Excel file and paste it into a tab of another file, following the last non-empty row of the table.

I’m new to VBA, just to clarify!

Thanks in advance

1 answer

  1.
    ThauTheme Posted messages 1564 Status Member 160
     
    Hello Devdev, hello forum,
    Code to adapt given the little information you provide... To place in the destination workbook!

    Sub Macro1()
    Dim CD As Workbook 'declare the destination workbook
    Dim OD As Worksheet 'declare the destination worksheet
    Dim Fichier As String 'declare the variable F (File)
    Dim CS As Workbook 'declare the source workbook
    Dim OS As Worksheet 'declare the source worksheet
    Dim DEST As Range 'declare the destination cell

    Application.ScreenUpdating = False 'hide screen refresh
    Set CD = ThisWorkbook 'define the destination workbook
    Set OD = CD.Worksheets("Feuil1") 'define the destination worksheet OD (to adapt to your case)
    Fichier = Application.GetOpenFilename 'source workbook selection box
    Workbooks.Open Filename:=Fichier 'open the selected workbook
    Set CS = ActiveWorkbook 'define the source workbook
    Set OS = CS.Worksheets("Feuil1") 'define the source worksheet OS (to adapt to your case)

    'defines the destination cell DEST
    Set DEST = IIf(OD.Range("A1").Value = "", OD.Range("A1"), OD.Cells(Application.Rows.Count, "A").End(xlUp).Offset(1, 0))

    OS.Range("ta_plage").Copy DEST 'copy and paste "ta_plage" into DEST ("ta_plage" represents the address of your source table)

    Application.ScreenUpdating = True 'show screen refresh
    End Sub

    --
    À plus,
    ThauTheme
    1
    1. devdev21 Posted messages 6 Status Member
       
      Nickel, it works! thank you very much :)
      0
    2. Gilles
       

      Hello,

      Here’s a message long after. I just used your @Thau Theme script.

      I’ve made a few changes, I’m sharing it again for anyone who might need it.

      I have my main file and will fetch the data from several files in a subfolder .\Pays\

      So I loop over the number of countries listed in a Library tab of my main file.

      Have a nice day

      Gilles

      Sub IMPORTATION() Dim CD As Workbook 'declare the variable CD (Destination Workbook) Dim OD As Worksheet 'declare the variable OD (Destination Worksheet) Dim Fichier As String 'declare the variable F (File) Dim CS As Workbook 'declare the variable CS (Source Workbook) Dim OS As Worksheet 'declare the variable OS (Source Worksheet) Dim DEST As Range 'declare the variable DEST (destination cell) Dim nbrpays As Long Dim path As String Dim Chemin As String Dim lignefin As Long Dim i As Integer Application.ScreenUpdating = False 'hide screen refresh nbrpays = Range("nbpays").Value + 3 Set CD = ThisWorkbook 'set destination workbook CD Set OD = CD.Worksheets("SUMMARY") 'set destination sheet OD OD.Range("B26:H10000").Clear For i = 4 To nbrpays Chemin = ActiveWorkbook.path & CD.Sheets("Bibliothèque").Cells(i, 2).Value Workbooks.Open (Chemin) Set CS = ActiveWorkbook 'set the source workbook CS Set OS = CS.Worksheets("Feuil1") 'set the source sheet OS lignefin = OS.Range("A10").End(xlDown).Row If lignefin > 10000 Then lignefin = 10 End If 'set the destination cell DEST (A1 if A1 is empty, otherwise the first empty cell in column A of the destination sheet) Set DEST = IIf(OD.Range("B26").Value = "", OD.Range("B26"), OD.Cells(Application.Rows.Count, "B").End(xlUp).Offset(3, 0)) OS.Range(Cells(10, 1), Cells(lignefin, 8)).Copy DEST ' copy and paste "ta_plage" into DEST ("ta_plage" represents the address of your source table) ActiveWorkbook.Close Next i Worksheets("SUMMARY").Select Application.ScreenUpdating = True 'show screen refresh End Sub
      0