0

There's this code i wrote that doesn't execute and sends a message error 1004 . 

Dim I As Long
Dim K As Long
Dim Plage() As String
Dim Dummy As String
Dim Adresses() As String
Dim ObjetCible As Workbook
Dim AdresseCible As String
'The function Plagedetab gets ranges of tables in the workbook as strings
  Plage = Plagedetab("C:\Users\Oumayna EL JAHRANI\Desktop\Test\TABLEAUX_PILLIER_III_CONSO")
'The function adresses gets paths to Excel workbooks as strings (there's a workbook for each worksheet)
  Adresses = Adressescibles("C:\Users\Oumayna EL JAHRANI\Desktop\Test\TABLEAUX_PILLIER_III_CONSO")
Dim Fichiersource As Workbook
Set Fichiersource = Workbooks.Open("C:\Users\Oumayna EL JAHRANI\Desktop\Test\TABLEAUX_PILLIER_III_CONSO")
Debug.Print (Fichiersource.Name)
Dim ClasseurType As Workbook: Set ClasseurType = Workbooks.Open("C:\Users\Oumayna EL JAHRANI\Desktop\Test\Fichier Type.xlsx")
  For I = 1 To Fichiersource.Sheets.Count
    AdresseCible = Adresses(I)
    Debug.Print (AdresseCible)
    Set ObjetCible = Workbooks.Open(AdresseCible)
    Dummy = Plage(I)
    Debug.Print (Dummy)
    ClasseurType.Activate
      For K = 1 To ClasseurType.Sheets.Count - 1
        If K = 1 Or K = 2 Then
'Here i copy all the formatting from my original file to the workbooks i created
          Debug.Print (CStr(ClasseurType.Sheets.Count))
          Fichiersource.Activate
          Fichiersource.Worksheets(I).Select
          Range(Dummy).Select
          Selection.Copy
          ObjetCible.Activate
          ObjetCible.Worksheets(K).Select
          Range("C7").Select
          Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
          SkipBlanks:=False, Transpose:=False
          Application.CutCopyMode = False
        ElseIf K = 3 Then
        End If
      Next
'Here i need to copy links from the last worksheet to the 2 first ones( of each workbook )
    ObjetCible.Activate
    ObjetCible.Worksheets(4).Select
    Range(Dummy).Select
    Selection.Copy
    ObjetCible.Worksheets(2).Select
    Range("C7").Select
'The pastespecial beyond doesn't work i tried with paste Nothing seems to work and i get a message error 1004
    ActiveSheet.PasteSpecial (Link = True)
    ObjetCible.Worksheets(4).Select
    Range(Dummy).Select
    Selection.Copy
    ObjetCible.Worksheets(1).Select
    Range("C7").Select
    ActiveSheet.PasteSpecial (Link = True)
    ObjetCible.Close SaveChanges:=True
  Next
End Sub

The problem here is the paste special , i know it only works for worksheets so i'm thinking maybe it's the fact that i put Range("C7").Select before that blocks my code . But still i need to paste links to the table that's in that exact range . Thank's in advance

Oumayma El Jahrani
  • 1
  • 2
  • 1
    That should be `ActiveSheet.PasteSpecial Link:=True` – BigBen Dec 09 '19 at 14:14
  • 1
    This code does hurt the eye =). Have a look at [this](https://stackoverflow.com/a/10717999/9758194) thread on SO. – JvdV Dec 09 '19 at 14:20
  • @BigBen I Don't get the error anymore but links aren't copied do you know what could cause that ? – Oumayma El Jahrani Dec 09 '19 at 14:28
  • Not sure. From the [documentation](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.pastespecial), for `Link`: **True** to establish a link to the source of the pasted data. If the source data isn't suitable for linking or the source application doesn't support linking, this parameter is ignored. The default value is **False** – BigBen Dec 09 '19 at 14:31
  • At the point you execute activesheet.PasteSpecial there is nothing in the clipboard to paste.The selection.pastespecial above has emptied the clipboard – Harassed Dad Dec 09 '19 at 15:05

0 Answers0