VBA copy data from another workbook and paste in current workbook

[TaskMaster]

Hi all,

I am wondering if you can help me with the following. I want to copy data from the workbook named Daily Stats saved in a specific location and copy into my current workbook called summary. The issue that im having is that the range of data that I want to copy changes with the date. I have tried to search for yesterdays date in the rows to determine the locations to copy and paste but not quite getting it correct. If anyone knows an easy fix I would appreciate it highly, thanks in advance.

Daily Stats.xlsx
	A	B	C	D
1	Date	Sale	Purchases	Total
2	03/10/2022	38,328	4,841	33,487
3	04/10/2022	7,927	10,680	-2,753
4	05/10/2022	25,750	8,085	17,665
5	06/10/2022	24,132	8,381	15,751
6	07/10/2022	37,749	495	37,254
7	10/10/2022	42,598	5,821	36,777
8	11/10/2022	6,337	248	6,089
9	12/10/2022	17,394	4,453	12,941
10	13/10/2022	48,422	2,828	45,594
11	14/10/2022	10,703	2,455	8,248
12	17/10/2022	31,790	7,028	24,762
13	18/10/2022	22,622	7,479	15,143
14	19/10/2022	43,525	8,334	35,191
15	20/10/2022	30,611	618	29,993
16	21/10/2022	20,263	10,191	10,072
17	24/10/2022	43,806	2,943	40,863
18	25/10/2022	29,234	2,103	27,131
19	26/10/2022	19,488	9,068	10,420
20	27/10/2022	12,604	7,149	5,455
21	28/10/2022	40,720	10,284	30,436
22	31/10/2022	19,440	3,330	16,110
Oct 22

Cell Formulas
Range	Formula
A3:A22	A3	=WORKDAY(A2,1)

Summary.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	30/09/2022	Oct 22
2	Total	SLA	03/10/2022	04/10/2022	05/10/2022	06/10/2022	07/10/2022	10/10/2022	11/10/2022	12/10/2022	13/10/2022	14/10/2022	17/10/2022	18/10/2022	19/10/2022	20/10/2022	21/10/2022	24/10/2022	25/10/2022	26/10/2022	27/10/2022	28/10/2022	31/10/2022
3	456,629	Sale/Purchases	33,487	-2,753	17,665	15,751	37,254	36,777	6,089	12,941	45,594	8,248	24,762	15,143	35,191	29,993	10,072	40,863	27,131	10,420	5,455	30,436	16,110
Data

Cell Formulas
Range	Formula
B1	B1	=TEXT(A1+1,"MMM YY")
C2	C2	=WORKDAY(A1,1)
D2:W2	D2	=WORKDAY(C2,1)
A3	A3	=SUM(C3:W3)

Dim ActiveWorkbook As String
Dim TodaysDate As String
Dim TName As String
Dim DailyStats As String
Dim FindRow As Range
Dim DStats As Range
Dim ColRef As String
Dim DS As String
Dim Wbk As Worksheet

TodaysDate = Evaluate(ThisWorkbook.Names("TDate").RefersTo) 'Defined Name containing previous wd date
TName = Evaluate(ThisWorkbook.Names("TabName").RefersTo) 'Defined Name containing tab name for daily state wb
ActiveWorkbook = ThisWorkbook.Worksheets("Data").Range("AC1") 'Name of workbook
DailyStats = "C:\Users\Flow\Desktop\Test\Daily Stats.xlsx"


Set FindRow = ThisWorkbook.Worksheets("Data").Rows(2).Find(What:=TodaysDate, LookIn:=xlValues, LookAt:=xlWhole)
ColRef = Split(Cells(, FindRow.Column).Address, "$")(1)


 With Workbooks(Worksheets("Data").Range("AC1").Value).ActiveSheet
 Set Wbk = Workbooks.Open(DailyStats, ReadOnly:=True)
 Set DStats = Wbk.Sheets(TName).Columns("A").Find(What:=TodaysDate, LookIn:=xlValues, LookAt:=xlWhole)
 DS = DStats.Row

.Range(ColRef & "2").Value = Wbk.Sheets("TName").Range("D" & DS).Value

Wbk.Close False

End With

 

[Alex Blakenburg]

It depends a little on how you are storing the date but on a copy of your workbook give this a try in a copy of your workbook.

    ' Remove these 3 lines
    Dim DS As String
    Set DStats = Wbk.Sheets(TName).Columns("A").Find(What:=TodaysDate, LookIn:=xlValues, LookAt:=xlWhole)
    DS = DStats.Row
    
    ' Replace with these line
    Dim DS As Long
    With Application
       DS = .IfError(.Match(TodaysDate, Wbk.Sheets(TName).Columns("A"), 0), 0)
    End With
    
    If DS = 0 Then
        MsgBox "Date not found, exiting procedure"
        Exit Sub
    End If

 

[TaskMaster]

It depends a little on how you are storing the date but on a copy of your workbook give this a try in a copy of your workbook.

    ' Remove these 3 lines
    Dim DS As String
    Set DStats = Wbk.Sheets(TName).Columns("A").Find(What:=TodaysDate, LookIn:=xlValues, LookAt:=xlWhole)
    DS = DStats.Row
  
    ' Replace with these line
    Dim DS As Long
    With Application
       DS = .IfError(.Match(TodaysDate, Wbk.Sheets(TName).Columns("A"), 0), 0)
    End With
  
    If DS = 0 Then
        MsgBox "Date not found, exiting procedure"
        Exit Sub
    End If

Hi thank you for your reply.

I am getting the MsgBox "Date not found, exiting procedure". I don't quite understand as the date is clearly listed there date that is currently being searched for is 31/10/2022.

 With Workbooks(Worksheets("Data").Range("AC1").Value).ActiveSheet
   
      Set Wbk = Workbooks.Open(DailyStats, ReadOnly:=True)
     
    With Application
       DS = .IfError(.Match(TodaysDate, Wbk.Sheets(TName).Columns("A"), 0), 0)
    End With
   
       If DS = 0 Then
        MsgBox "Date not found, exiting procedure"
        Exit Sub
    End If
  
        .Range(ColRef & "10").Value = Wbk.Sheets(TName).Range("G" & DS).Value
        
      Wbk.Close False
     
End With

 

[Alex Blakenburg]

Can you post a picture of the date

 

[TaskMaster]

The spreadsheet with the dates in column A is the sheet im wanting to copy data from and the workbook with the dates in row 2 is the sheet I want the data to be copied to.

 

[TaskMaster]

I have managed to find the reason why this wasn't working after your amendments

I added

Dim fDate As Date

and replaced TodaysDate with fDate

Thank you for your assistance with this you really pushed me in the right direction.

 

[Alex Blakenburg]

I have managed to find the reason why this wasn't working after your amendments

I added

Dim fDate As Date

and replaced TodaysDate with fDate

Thank you for your assistance with this you really pushed me in the right direction.

I am not sure if you are still using Find or Match but if you are using a variable dimensioned as Date it will be safer to not just use fDate but to use CLng(fDate)