Import data with pictures

[Dave_george]

Hello,

I am trying to import data that includes pictures saved in a separate folder. Each picture has its own excel workbook and are separately named. All the data saved in these files are in range A1:N32. I would like to import it when I specify the file name and press a button. Please see the attached picture.

 

[Dave_george]

I have a good which opens up the correct workbook but I get a runtime error on line :

ThisWorkbook.Sheets("Sheet1").Range("A1:N32").PasteSpecial Paste:=xlPasteValues

Sub ImportData()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim pictureName As String
    Dim picturePath As String
    
    ' Get the picture name from cell R2
    pictureName = ThisWorkbook.Sheets("Sheet1").Range("R2").Value
    
    ' Set the picture path
    picturePath = "C:\Users\Public\Pictures" & Range("R2").Text & ".xlsx"
    
    ' Open the workbook with the picture
    Set pictureWorkbook = Workbooks.Open(picturePath)
    
    ' Copy data from the picture workbook
    pictureWorkbook.Sheets(1).Range("A1:N32").Copy
    
    ' Paste data to the active sheet
    ThisWorkbook.Sheets("Sheet1").Range("A1:N32").PasteSpecial Paste:=xlPasteValues
    
    ' Close the picture workbook without saving
    pictureWorkbook.Close SaveChanges:=False
End Sub

 

[Dave_george]

I have a good which opens up the correct workbook but I get a runtime error on line :

ThisWorkbook.Sheets("Sheet1").Range("A1:N32").PasteSpecial Paste:=xlPasteValues

Sub ImportData()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim pictureName As String
    Dim picturePath As String
   
    ' Get the picture name from cell R2
    pictureName = ThisWorkbook.Sheets("Sheet1").Range("R2").Value
   
    ' Set the picture path
    picturePath = "C:\Users\Public\Pictures" & Range("R2").Text & ".xlsx"
   
    ' Open the workbook with the picture
    Set pictureWorkbook = Workbooks.Open(picturePath)
   
    ' Copy data from the picture workbook
    pictureWorkbook.Sheets(1).Range("A1:N32").Copy
   
    ' Paste data to the active sheet
    ThisWorkbook.Sheets("Sheet1").Range("A1:N32").PasteSpecial Paste:=xlPasteValues
   
    ' Close the picture workbook without saving
    pictureWorkbook.Close SaveChanges:=False
End Sub

After adding the below lines I get all the data with formatting but without the picture.

' Paste data to the active sheet
ThisWorkbook.Sheets("Sheet1").Range("A1:N32").PasteSpecial xlPasteAllMergingConditionalFormats
ThisWorkbook.Sheets("Sheet1").Range("A1:N32").PasteSpecial xlPasteRowHeights
ThisWorkbook.Sheets("Sheet1").Range("A1:N32").PasteSpecial xlPasteColumnWidths