Copy and overwrite sheet from closed workbook to another closed workbook.

[i200yrs]

Hello All...I have 4 excel files.
1 = Excel1 have sheetname report1
2 = Excel2 have sheetname report2
3 = Excel3 (my main report excel file)
4 = Excel4 (my tracking excel file)

The excel number 1,2,3 are closed and only the Excel4 is open.
I want to make a VBA script that if click a button at Excel4 will copy/overwrite the sheetnames(report1 and report2) to Excel3 (my main report excel file).
Really appreciates if someone can help us guide me how to start the code or please share if have already VBA script available almost similar. thanks.

 

[i200yrs]

Hello All...hope someone can help me on this...thanks

 

[HaHoBe]

Hi i200years,

assuming that the workbooks concerned are located in the same directory as the workbook with code and that the ReportX-workbooks are called as and hold a worksheet named ReportX and all are macrofree:

Public Sub MrE_1229252_1702708()
' https://www.mrexcel.com/board/threads/copy-and-overwrite-sheet-from-closed-workbook-to-another-closed-workbook.1229252/
Dim wbThis As Workbook
Dim wbMain As Workbook
Dim wbReport As Workbook
Dim strPath As String
Dim astrSheets As Variant
Dim varSplit As Variant
Dim ws As Worksheet

Const cstrMain As String = "Main"
Const cstrExt As String = ".xlsx"

astrSheets = Array("Report1", "Report2")

Set wbThis = ThisWorkbook
strPath = wbThis.Path & "\"

If Dir(strPath & cstrMain & cstrExt) <> "" Then
  Set wbMain = Workbooks.Open(strPath & cstrMain & cstrExt)
  For Each varSplit In astrSheets
    If Dir(strPath & varSplit & cstrExt) <> "" Then
      For Each ws In wbMain.Worksheets
        If LCase(ws.Name) = LCase(varSplit) Then
          Application.DisplayAlerts = False
          wbMain.Worksheets(varSplit).Delete
          Application.DisplayAlerts = True
          Exit For
        End If
      Next ws
      Set wbReport = Workbooks.Open(strPath & varSplit & cstrExt)
      If Evaluate("ISREF('[" & wbReport.Name & "]" & varSplit & "'!A1)") Then
        wbReport.Worksheets(varSplit).Copy after:=wbMain.Worksheets(wbMain.Worksheets.Count)
      End If
      wbReport.Close False
    End If
  Next varSplit
  wbMain.Close True
End If

Set wbReport = Nothing
Set wbMain = Nothing
Set wbThis = Nothing
End Sub

Ciao,
Holger

 

[i200yrs]

I modified the code but it does not do the job...please help double check....thanks

```

Public Sub MrE_1229252_1702708()
' https://www.mrexcel.com/board/threads/copy-and-overwrite-sheet-from-closed-workbook-to-another-closed-workbook.1229252/
Dim wbThis As Workbook
Dim wbMain As Workbook
Dim wbReport As Workbook
Dim strPath As String
Dim astrSheets As Variant
Dim varSplit As Variant
Dim ws As Worksheet

Const cstrMain As String = "Main"
Const cstrExt As String = ".xlsm"

astrSheets = Array("Report1", "Report2", "Report3")

Set wbThis = ThisWorkbook
strPath = wbThis.Path & "\"

If Dir(strPath & cstrMain & cstrExt) <> "" Then
  Set wbMain = Workbooks.Open(strPath & cstrMain & cstrExt)
  For Each varSplit In astrSheets
    If Dir(strPath & varSplit & cstrExt) <> "" Then
      For Each ws In wbMain.Worksheets
        If LCase(ws.Name) = LCase(varSplit) Then
          Application.DisplayAlerts = False
          wbMain.Worksheets(varSplit).Delete
          Application.DisplayAlerts = True
          Exit For
        End If
      Next ws
      Set wbReport = Workbooks.Open(strPath & varSplit & cstrExt)
      If Evaluate("ISREF('[" & wbReport.Name & "]" & varSplit & "'!A1)") Then
        wbReport.Worksheets(varSplit).Copy after:=wbMain.Worksheets(wbMain.Worksheets.Count)
      End If
      wbReport.Close False
    End If
  Next varSplit
  wbMain.Close True
End If

Set wbReport = Nothing
Set wbMain = Nothing
Set wbThis = Nothing
End Sub

 

[HaHoBe]

Hi i200yrs,

it does not do the job

is not the best description for what you expect the code to do and what has not been done.

Although the extension for the workbooks was switched to being macroenabled and a new workbook/worksheet was introduced - after adapting the files accordingly everything was the same to me as for the original code (all four workbooks are located in the same folder). If that is not the case

strPath = wbThis.Path & "\"

needs to be altered to the path and folder where the three files which are to be opened are located like

strPath = "C:\Result\Test\"

Holger

 

[i200yrs]

Here is the latest, but still not working:

Const cstrMain As String = "Main"
Const cstrExt As String = ".xlsm"

astrSheets = Array("Report1", "Report2", "Report3")

Set wbThis = ThisWorkbook
strPath = "D:\Practical\VBA\ExcelVBA\"

Sorry for not being clear...I have total 5 excel files....4 are closed, only one is open.
filename1 = report1.xlsx (containing report1 sheet) (closed)
filename2 = report2.xlsx (containing report2 sheet) (closed)
filename3 = report3.xlsx (containing report3 sheet) (closed)

filename4 = main.xlsm (closed)

filename5 = tracking.xlsm (open)

I want to put the vba script into filename5....When run, it will copy the report1/2/3 sheets into filename4(main.xlsm).
If sheet name exist, it will overwrite....pls let me know anything i missed....hoping for your support...thanks

 

[HaHoBe]

Hi i200yrs,

if you have one constant for the extension all workbooks must be of the same kind. If you have different extensions you should use either two constants or apply the dot and extension independently for the one workbook not matching.

Public Sub MrE_1229252_1702708Update()
' https://www.mrexcel.com/board/threads/copy-and-overwrite-sheet-from-closed-workbook-to-another-closed-workbook.1229252/
' Updated: 20230208
' Reason:  different fileformats for workbooks

Dim wbThis            As Workbook
Dim wbMain            As Workbook
Dim wbReport          As Workbook

Dim strPath           As String

Dim astrSheets        As Variant
Dim varSplit          As Variant

Dim ws                As Worksheet

Const cstrMain        As String = "Main"
Const cstrExtXLSX     As String = ".xlsx"
Const cstrExtMAKRO    As String = ".xlsm"


astrSheets = Array("Report1", "Report2", "Report3")

Set wbThis = ThisWorkbook
strPath = wbThis.Path & "\"

If Dir(strPath & cstrMain & cstrExtMAKRO) <> "" Then
  Set wbMain = Workbooks.Open(strPath & cstrMain & cstrExtMAKRO)
  For Each varSplit In astrSheets
    If Dir(strPath & varSplit & cstrExtXLSX) <> "" Then
      For Each ws In wbMain.Worksheets
        If LCase(ws.Name) = LCase(varSplit) Then
          Application.DisplayAlerts = False
          wbMain.Worksheets(varSplit).Delete
          Application.DisplayAlerts = True
          Exit For
        End If
      Next ws
      Set wbReport = Workbooks.Open(strPath & varSplit & cstrExtXLSX)
      If Evaluate("ISREF('[" & wbReport.Name & "]" & varSplit & "'!A1)") Then
        wbReport.Worksheets(varSplit).Copy after:=wbMain.Worksheets(wbMain.Worksheets.Count)
      End If
      wbReport.Close False
    End If
  Next varSplit
  wbMain.Close True
End If

Set wbReport = Nothing
Set wbMain = Nothing
Set wbThis = Nothing
End Sub

Holger

 

[i200yrs]

Hi i200yrs,

if you have one constant for the extension all workbooks must be of the same kind. If you have different extensions you should use either two constants or apply the dot and extension independently for the one workbook not matching.

Public Sub MrE_1229252_1702708Update()
' https://www.mrexcel.com/board/threads/copy-and-overwrite-sheet-from-closed-workbook-to-another-closed-workbook.1229252/
' Updated: 20230208
' Reason:  different fileformats for workbooks

Dim wbThis            As Workbook
Dim wbMain            As Workbook
Dim wbReport          As Workbook

Dim strPath           As String

Dim astrSheets        As Variant
Dim varSplit          As Variant

Dim ws                As Worksheet

Const cstrMain        As String = "Main"
Const cstrExtXLSX     As String = ".xlsx"
Const cstrExtMAKRO    As String = ".xlsm"


astrSheets = Array("Report1", "Report2", "Report3")

Set wbThis = ThisWorkbook
strPath = wbThis.Path & "\"

If Dir(strPath & cstrMain & cstrExtMAKRO) <> "" Then
  Set wbMain = Workbooks.Open(strPath & cstrMain & cstrExtMAKRO)
  For Each varSplit In astrSheets
    If Dir(strPath & varSplit & cstrExtXLSX) <> "" Then
      For Each ws In wbMain.Worksheets
        If LCase(ws.Name) = LCase(varSplit) Then
          Application.DisplayAlerts = False
          wbMain.Worksheets(varSplit).Delete
          Application.DisplayAlerts = True
          Exit For
        End If
      Next ws
      Set wbReport = Workbooks.Open(strPath & varSplit & cstrExtXLSX)
      If Evaluate("ISREF('[" & wbReport.Name & "]" & varSplit & "'!A1)") Then
        wbReport.Worksheets(varSplit).Copy after:=wbMain.Worksheets(wbMain.Worksheets.Count)
      End If
      wbReport.Close False
    End If
  Next varSplit
  wbMain.Close True
End If

Set wbReport = Nothing
Set wbMain = Nothing
Set wbThis = Nothing
End Sub

Holger

THIS ONE WORK AS I EXPECTED....OHHH THANK YOU SO MUCH @HaHoBe