Copy paste the value between two worksheets in different workbook only if they have same sheet name

[2020Rivalry]

Scenario: There have two excel files, one for 2pm and one for 3pm meeting. The files are not exact same, but currently we need to copy some of the data from 2pm file to 3pm one everyday.

Requirements: Only copy the value between two worksheet on two different workbook (2pm and 3pm) if they have the same sheet name. For example, copy paste value from sheet “WK1” workbook “2pm” to sheet “WK1” workbook “3pm”.

3pmTrial.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2	Wk	3			WK3
3
4	KPIs		Target	Accomplished
5				Mon	Tue	Wed	Thu	Fri	Sat	Sun
6	KPI1		0
7	KPI2		L
8	KPI3		2 per Day
9			0
10	KPI4		L
WK3

Cell Formulas
Range	Formula
E2	E2	=CONCAT("WK",B2)

I'm trying with code below but getting this error (Compile error: Invalid or unqualified reference)

Public Sub insert_Data()

Application.ScreenUpdating = False

Dim wb1, wb2 As Workbook
Set wb1 = Workbooks.Open("G:\DDS\TrialData\2pmTrial.xlsx")
Set wb2 = Workbooks.Open("G:\DDS\TrialData\3pmTrial.xlsx")

Dim sh1 As Worksheets
Set sh1 = Workbooks("3pmTrial.xlsx").Worksheets("WK*")

For Each sh1 In Worksheets
    With Workbooks("3pmTrial.xlsx")
        Workbooks("2pmTrial.xlsx").Worksheets(.Range("E2").Value).Range("D6:J10").Value = .Worksheets(.Range("E2").Value).Range("D6:J10").Value
    End With
Next

Application.ScreenUpdating = True
 
End Sub

Thank you!

 

[Alex Blakenburg]

we need to copy some of the data from 2pm file to 3pm one everyday.

Your code is trying to copy from 3pm to 2pm. Which is correct ?

 

[Alex Blakenburg]

Assuming you are copying from 2pm to 3pm this might work for you.

Public Sub insert_Data_Test()

Application.ScreenUpdating = False

Dim wb1 As Workbook, wb2 As Workbook                    ' XXX You need to specify the type for each parameter
Set wb1 = Workbooks.Open("G:\DDS\TrialData\2pmTrial.xlsx")
Set wb2 = Workbooks.Open("G:\DDS\TrialData\3pmTrial.xlsx")

Dim sh1 As Variant
'Set sh1 = Workbooks("3pmTrial.xlsx").Worksheets("WK*") ' XXX You can't use a wild card in the name like this
Dim sh2 As Worksheet

For Each sh1 In wb1.Worksheets

    If UCase(sh1.Name) Like "WK*" Then
        On Error Resume Next
            Set sh2 = wb2.Worksheets(sh1.Name)
        On Error GoTo 0
        
        If Not sh2 Is Nothing Then
            sh1.Range("D6:J10").Value = sh2.Range("D6:J10").Value
        End If
    End If
Next

Application.ScreenUpdating = True
 
End Sub

 

[2020Rivalry]

Your code is trying to copy from 3pm to 2pm. Which is correct ?

Yes, my bad, it should copy from 2pm to 3pm.

Assuming you are copying from 2pm to 3pm this might work for you.

Public Sub insert_Data_Test()

Application.ScreenUpdating = False

Dim wb1 As Workbook, wb2 As Workbook                    ' XXX You need to specify the type for each parameter
Set wb1 = Workbooks.Open("G:\DDS\TrialData\2pmTrial.xlsx")
Set wb2 = Workbooks.Open("G:\DDS\TrialData\3pmTrial.xlsx")

Dim sh1 As Variant
'Set sh1 = Workbooks("3pmTrial.xlsx").Worksheets("WK*") ' XXX You can't use a wild card in the name like this
Dim sh2 As Worksheet

For Each sh1 In wb1.Worksheets

    If UCase(sh1.Name) Like "WK*" Then
        On Error Resume Next
            Set sh2 = wb2.Worksheets(sh1.Name)
        On Error GoTo 0
       
        If Not sh2 Is Nothing Then
            sh1.Range("D6:J10").Value = sh2.Range("D6:J10").Value
        End If
    End If
Next

Application.ScreenUpdating = True
 
End Sub

I put some values in 2pm files as I want to test the code. However, after I ran this code, the values in my 2pm files disappear. Any helps? Can I ask another question? Why I cannot use wild card in that name?

 

[Alex Blakenburg]

after I ran this code, the values in my 2pm files disappear.

Sorry I swapped your sh1 from being 3pm to being 2pm but forgot to swap the line in the code.
(this was more consistent with the wb1 & wb2 naming)
Use this (sh1 sh2 swapped around):

sh2.Range("D6:J10").Value = sh1.Range("D6:J10").Value

Why I cannot use wild card in that name?

This line Workbooks("3pmTrial.xlsx").Worksheets("WK*") is expecting the name of a worksheet.
It will not return an array of worksheets which is what you are expecting when you use wildcards.
You need to loop through all the sheets and check the name of each sheet to see if it matches your WK*.

 

[2020Rivalry]

Thank you very much, it works!! Can I make 3pm workbook to be updated regularly instead of putting a macro button and press it everytime for update.

 

[Alex Blakenburg]

Something needs to trigger the macro. I have seen posts regarding scheduling a macro to run but I have no experience in that area.
If you think that you want to pursue that perhaps start a new thread so with an appropriate subject line to encourage people with experience in scheduling a macro to run to assist.
Microsoft Power Automate may also be an option for you.

 

[2020Rivalry]

Something needs to trigger the macro. I have seen posts regarding scheduling a macro to run but I have no experience in that area.
If you think that you want to pursue that perhaps start a new thread so with an appropriate subject line to encourage people with experience in scheduling a macro to run to assist.
Microsoft Power Automate may also be an option for you.

Ok, thank you very much!!

 

[2020Rivalry]

Alex, I need your help. Currently, my code is like this

Public Sub insert_Data()

Application.ScreenUpdating = False

Dim wb1 As Workbook, wb2 As Workbook                    ' Need to edit every year (the new files location)
Set wb1 = Workbooks.Open("G:\DDS\Daily DDS (8.45am) Yr2022 v2.xlsx")
Set wb2 = Workbooks.Open("G:\DDS\Daily DDS (9.45am) Yr2022.xlsm")

Dim sh1 As Variant
Dim sh2 As Worksheet

For Each sh1 In wb1.Worksheets

    If UCase(sh1.Name) Like "WK*" Then
        On Error Resume Next
            Set sh2 = wb2.Worksheets(sh1.Name)
        On Error GoTo 0
        
        If Not sh2 Is Nothing Then
            sh2.Range("B17:D21").Value = sh1.Range("B4:D8").Value
            sh2.Range("F17:L21").Value = sh1.Range("E4:K8").Value ' safety incident, safety trigger, pulsar, quality trigger
            sh2.Range("B22:D41").Value = sh1.Range("B11:D30").Value
            sh2.Range("F22:L41").Value = sh1.Range("E11:K30").Value ' quality performance
            sh2.Range("B62:S74").Value = sh1.Range("B73:S85").Value ' main losses, need to adjust the row height manually
            sh2.Range("B78:S80").Value = sh1.Range("B89:S91").Value ' daily action plan
            sh2.Range("B84:S91").Value = sh1.Range("B95:S102").Value ' follow up
        End If
    End If
Next

Application.ScreenUpdating = True
 
End Sub

As I have mention earlier, 9.45am excel is taking data from 8.45am file. After that, my colleague need to add few more data. However, every time I run the macro, the cells are overwrite. And the data that my colleague added disappear.. Is there a way to check how many rows in 8.45 files is used, then only copy those data to 9.45. For example, only B73:S76 is used in 8.4 5file. And I want to copy them to B62:S65 in 9.45 file. My colleague can then input B66:Sxx data in 9.45 file if required.

sh2.Range("B62:S74").Value = sh1.Range("B73:S85").Value ' main losses, need to adjust the row height manually
sh2.Range("B78:S80").Value = sh1.Range("B89:S91").Value ' daily action plan
sh2.Range("B84:S91").Value = sh1.Range("B95:S102").Value ' follow up

Thank you very much.

 

[Alex Blakenburg]

You seem to have multiple "blocks" of data on each sheet. This makes working out the ranges much more challenging.
We would really need to see a sample of what sh1 & sh2 look like before doing anything and sh2 "after" the process is run.
eg main loses starts on row 73 what is on row 72.