VBA script to overwrite all contents from worksheet with same name from one workbook to another

Question

VBA amateur here, I am writing a code to automate some processes in my workplace, i am however stuck in trying to overwrite all contents from workseets of the same name from one workbook to another.

My current process is as follows.

I store the macro in workbook 1
I open the worksheet which i wish to run the macro on
I run the macro
Ideally the macro should run the code below onto every worksheet and following which overwrite the data of a similar worksheet name in my master workbook. It is likely to be 40 different sheets with static worksheet name.

example: copy contents in worksheet("Asia") of workbook A into worksheet("Asia") of master workbook.

Please advise if this can be done in vba.

I tried looking up solutions on this site but is unable to find a solution.

Dim wk As Worksheet
For Each wk In ActiveWorkbook.Worksheets
 wk.Activate


        Dim TR As Integer
        TR = Range("S" & Rows.Count).End(xlUp).Row
        Range("Z4").Formula = "=CONCATENATE(TEXT(D4,""mm/dd/yyyy""),S4,M4)"
        Range("Z4").Copy
        Range("Z4:Z" & TR).PasteSpecial xlPasteAll
        Application.CutCopyMode = False

        Dim UR As Integer
        UR = Range("S" & Rows.Count).End(xlUp).Row
        Range("AA4").Formula = "=CONCATENATE(TEXT(D4,""mm/dd/yyyy""),S4,F4)"
        Range("AA4").Copy
        Range("AA4:AA" & UR).PasteSpecial xlPasteAll
        Application.CutCopyMode = False

        Dim DR As Integer
        DR = Range("S" & Rows.Count).End(xlUp).Row
        Range("AB4").Formula = "=CONCATENATE(TEXT(A4,""mm/dd/yyyy""),S4,K4)"
        Range("AB4").Copy
        Range("AB4:AB" & DR).PasteSpecial xlPasteAll
        Application.CutCopyMode = False

        Dim FR As Integer
        FR = Range("S" & Rows.Count).End(xlUp).Row
        Range("AC4").Formula = "=CONCATENATE(TEXT(A4,""mm/dd/yyyy""),S4,K4)"
        Range("AC4").Copy
        Range("AC4:AC" & FR).PasteSpecial xlPasteAll
        Application.CutCopyMode = False

        ActiveSheet.Range("A1").Value = Date
        ActiveSheet.Range("A1").Formula = "=TEXT(TODAY(),""dd/mm/yyyy"")"

        Last = Cells(Rows.Count, "A").End(xlUp).Row
        For j = Last To 1 Step -1
             If ActiveSheet.Cells(j, 1) > ActiveSheet.Range("A1") Then

                ActiveSheet.Cells(j, 29).FormulaR1C1 = "=CONCATENATE(RC[-10], RC[-18])"


            End If

        Next j

Next wk

End Sub

My code for the spreadsheet above work, i am just figuring out how i can update the data in my master workbook.

want to know what problem are you facing and also one suggestion want to give by looking at your code , before range provide workbook and worksheet name for which you are referring — yashika vaish

yashika vaish yashika vaish · Accepted Answer · 2019-07-17T09:55:55

Sub so()
Dim wb As Workbook
Dim wb1 As Workbook
Set wb = Workbooks("SourceWorkbook.xlsx")
Set wb1 = Workbooks("MasterWorkbook.xlsx")
Dim wk As Worksheet
Dim wm As Worksheet
Set wm = wb1.Worksheets("Asia")
For Each wk In wb.Worksheets
 wk.Activate
If (wk.Name = "Asia") Then
        Dim TR As Integer
        TR = wk.Range("S" & Rows.Count).End(xlUp).Row
        wk.Range("Z4").Formula = "=CONCATENATE(TEXT(D4,""mm/dd/yyyy""),S4,M4)"
        wk.Range("Z4:Z" & TR).Copy wm.Range("Z4")
        Application.CutCopyMode = False

        Dim UR As Integer
        UR = wk.Range("S" & Rows.Count).End(xlUp).Row
        wk.Range("AA4").Formula = "=CONCATENATE(TEXT(D4,""mm/dd/yyyy""),S4,F4)"
        wk.Range("AA4:AA" & UR).Copy wm.Range("AA4")
        Application.CutCopyMode = False

        Dim DR As Integer
        DR = wk.Range("S" & Rows.Count).End(xlUp).Row
        wk.Range("AB4").Formula = "=CONCATENATE(TEXT(A4,""mm/dd/yyyy""),S4,K4)"
       wk.Range("AB4:AB" & DR).Copy wm.Range("AB4")
        Application.CutCopyMode = False

        Dim FR As Integer
        FR = wk.Range("S" & Rows.Count).End(xlUp).Row
        wk.Range("AC4").Formula = "=CONCATENATE(TEXT(A4,""mm/dd/yyyy""),S4,K4)"
        wk.Range("AC4:AC" & FR).Copy wm.Range("AC4")
        Application.CutCopyMode = False

        ActiveSheet.Range("A1").Value = Date
        ActiveSheet.Range("A1").Formula = "=TEXT(TODAY(),""dd/mm/yyyy"")"

        Last = Cells(Rows.Count, "A").End(xlUp).Row
        For j = Last To 1 Step -1
             If ActiveSheet.Cells(j, 1) > ActiveSheet.Range("A1") Then

                ActiveSheet.Cells(j, 29).FormulaR1C1 = "=CONCATENATE(RC[-10], RC[-18])"


            End If

        Next j
End If
Next wk

End Sub

VBA script to overwrite all contents from worksheet with same name from one workbook to another

1 Answers