Ok try this
This macro needs to goto to the destination workbook. Destination workbook will open the source workbook. You need to correct the path of the source workbook. The macro will copy the data in P1:W15 from the source worbook to every single worksheet in destination workbook
Steps:
1. Open the destination workbook (Data_Reciever.xlsx)
2. Press ALT + F11 to launch VBE
3. Click on Insert and add a new module
4. copy and paste the code below
5. To run the code, press F5
Sub ImportData()
Dim WBsrc As Workbook
Dim WBdes As Workbook
Dim WSsrc As Worksheet
Dim WSdes As Worksheet
Set WBdes = ThisWorkbook
Set WBsrc = Workbooks.Open("C:\Users\shamikh\Downloads\Counting Box.xlsx")
'wbsrc.
Set WSsrc = WBsrc.Sheets("Sheet1")
WSsrc.Select
Range("P1:W15").Copy
WBdes.Activate
Set WSdes = ActiveSheet
Range("M2:T16").PasteSpecial
Application.CutCopyMode = xlCopy
WBsrc.Close
WBdes.Activate
For Each Sheet In Sheets
If Sheet.Name = WSdes.Name Then GoTo Next_Sheet
WSdes.Select
Range("M2:T16").Copy
Sheet.Select
Range("M2:T16").PasteSpecial
Next_Sheet:
Next Sheet
Set WSsrc = Nothing
Set WSdes = Nothing
Set WBsrc = Nothing
Set WBdes = Nothing
End Sub
I'll tell you if any problems come up.
thanks for all your help!