Search code examples
excelvba

Macro from Personal not working in other workbooks I want to copy one sheet to another workbook not personal

Excel VBA. I wrote a macro, I have data in workbook1 (only one Sheet1), I have workbook2 (only one Sheet2 which contains a formatted table). Workbook1 is created by the application, it contains rows of data, each time the workbook name is slightly different. What do I want to do? in each new workbook1, run a macro, i.e. a personal macro, which will copy the table, i.e. sheet2 from workbook2, and then copy the value of the cells from workbook1(sheet1) to the appropriate cells of the workbook2(sheet2) table. I created lines of code and it was fine, but something went wrong:

Public Sub CopySheetFromClosedWorkbook()
   Dim sourceBook As Workbook
   Application.ScreenUpdating = False
   Set sourceBook = Workbooks.Open("C:\..........\test.xlsm")
   sourceBook.Sheets("Sheet2").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
   sourceBook.Close
   Application.ScreenUpdating = True

  
'
' Sheets("Sheet1").Range("B2").Copy
' Sheets("Sheet2").Range("C5").PasteSpecial Paste:=xlPasteValues
' ..........
'

  
' Worksheets("Sheet2").PrintOut
  
End Sub

now it looks like it copies Sheet2 as a new sheet to the PERSONAL file. when I changed to ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count) then the Sheet2 Table was copied as a new Sheet to Workbook2

I want it to create a copy of Sheet2 from Workbook2 to Workbook1 as a new Sheet2. Do you have any idea?

Solution

  • Import Sheet From Closed Workbook Using PERSONAL.xlsb

    Sub ImportSheetFromClosedWorkbook()
   
    Dim dwb As Workbook: Set dwb = ActiveWorkbook
    
    If dwb Is Nothing Then
        MsgBox "No workbook open!", vbExclamation
        Exit Sub
    End If
   
    Application.ScreenUpdating = False
    
    Dim swb As Workbook: Set swb = Workbooks.Open("C:\..........\test.xlsm")
    
    swb.Sheets("Sheet2").Copy After:=dwb.Sheets(dwb.Sheets.Count)
    swb.Close SaveChanges:=False
    
    Application.ScreenUpdating = True

    MsgBox "Sheet imported in workbook """ & dwb.Name & """.", vbInformation
  
End Sub