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]

If the data is input into the same sections then you would need to add a column that distinguishes between what copied in and what was entered.