VBA code to paste based on date column

[RW11700]

I have a nightly report that is generated in excel and I am hoping to copy a cell range from sheet James and paste it to another sheet James2 based off the current date. In column A of James2 I have dates in mm/dd/yyyy format, I am hoping to take my copied cell range from James and paste them to James2 starting in column B based on TODAYS date in column A. I am able to copy the cells based on the below code but I am unable to figure out how to paste the data in sheet James2

Workbooks("Daily Availability TEST FILE.xlsm").Worksheets("James").Range("D8:X8").Copy

 

[Joe4]

Welcome to the Board!

Are both of these sheets in the same workbook?

I think it might be beneficial to show us a simple example.
Show us a small sample of the data in each sheet, and what you want the expected results to look like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[RW11700]

Both sheets are in the same workbook.

I want to have the cell range saved on another sheet so I can reference previous days. I am having trouble automating it to paste based off the date in column A.

 

[Joe4]

Do you need to check/verify that the dates match, or can you just paste it to the next available row on your second sheet?
If you need to match it, is the value "System Date: 07-07-2022" all found in cell A3, or is it split up over A3 and B3?
If it is split up, is the value entered in B3 a valid date, or is it text?
If you are unsure, enter this formula in any blank cell and see what it returns:
=ISNUMBER(B3)

If it is a valid date, it will return TRUE.

 

[RW11700]

Thank you for taking the time to help, cell B3 came back as False. The report opens and runs automatically each night through task scheduler, I would need the cell range to be able to find "todays" date from column A on sheet James2, then paste the data in the same row as todays date starting in column B.

 

[Joe4]

OK, but you still haven't answered this question:

If you need to match it, is the value "System Date: 07-07-2022" all found in cell A3, or is it split up over A3 and B3?

We need to know if you have:
- "System Date: 07-07-2022" all found in cell A3
or
- "System Date: " in cell A3 and "07-07-2022" in cell B3

If you are unsure, what does this formula return:
=LEN(B3)

 

[RW11700]

=LEN(B3) - this returned 0

So this is a report that I am pulling from our property management system, the date shown in cell A3 is the system date in our property management system. The entire "System Date: 07-07-2022" is in cell A3.

 

[Joe4]

OK, your original question and your screen images don't quite match up (regarding which columns you are copying), so this code may need to be altered slightly.
But this should do what you need:

Sub MyCopyMacro()

    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim curDate As Date
    Dim rng As Range
    Dim r As Long
   
'   Set worksheet variables
    Set sh1 = Sheets("James")
    Set sh2 = Sheets("James2")
   
'   Get date out of Range A3 on first sheet
    curDate = Right(Trim(sh1.Range("A3")), 10)
   
'   Find date in column A on second sheet
    Set rng = sh2.Columns("A:A").Find(What:=curDate, After:=sh2.Range("A1"), LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
   
'   Check to see if match found
    If rng Is Nothing Then
        MsgBox "Could not find date " & Format(curDate, "m/d/yyyy") & " on Sheet " & sh2.Name, vbOKOnly, "ERROR!"
        Exit Sub
    End If
   
'   Copy data over
    sh1.Range("C8:W8").Copy sh2.Cells(rng.Row, "B")

End Sub

 

[RW11700]

Daily Availability TEST FILE.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Carousel Hotel																						Page: 1 of 2
2
3	System Date: 07-07-2022																						Generated on: 07-07-2022 07:02 AM
4
5
6			Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed
7			7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27
8	Available		7	25	38	32	22	11	24	21	49	51	36	31	17	31	18	51	73	57	39	36	42
9	Pick Up		11	5	7	6	2	1	2	1	2	2	-1	-1	-1	3	0	1	1	1	-1	-2
10	Total Rooms		259	259	259	259	259	259	259	259	259	259	259	259	259	259	259	259	259	259	259	259	259
11	Occ %		97	90	85	88	92	96	91	92	81	80	86	88	93	88	93	80	72	78	85	86	84
12	OOO/DNS		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13	Total Sold		252	234	221	227	237	248	235	238	210	208	223	228	242	228	241	208	186	202	220	223	217
14	Arrivals		65	76	48	116	53	32	30	53	46	67	99	46	41	19	60	44	48	75	66	22	17
15	Departures		57	94	61	110	43	21	43	50	74	69	84	41	27	33	47	77	70	59	48	19	23
16	Total People		715	671	643	657	695	721	703	681	604	614	681	692	713	679	706	590	513	556	598	609	592
17	Grps Not P/U		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
18	Restriction				*		*	*				*		*	*		*	*	*		*	*
James

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C6:W18	Expression	=C$6="Sat"	text	NO
C6:W18	Expression	=C$6="Fri"	text	NO

 

[RW11700]

Daily Availability TEST FILE.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	7/5/2022
2	7/6/2022
3	7/7/2022
4	7/8/2022
5	7/9/2022
6	7/10/2022
7	7/11/2022
8	7/12/2022
9	7/13/2022
10	7/14/2022
11	7/15/2022
12	7/16/2022
13	7/17/2022
14	7/18/2022
15	7/19/2022
16	7/20/2022
17	7/21/2022
18	7/22/2022
19	7/23/2022
20	7/24/2022
21	7/25/2022
22	7/26/2022
23	7/27/2022
24	7/28/2022
25	7/29/2022
26	7/30/2022
27	7/31/2022
28	8/1/2022
29	8/2/2022
30	8/3/2022
31	8/4/2022
32	8/5/2022
33	8/6/2022
34	8/7/2022
35	8/8/2022
36	8/9/2022
37	8/10/2022
38	8/11/2022
39	8/12/2022
40	8/13/2022
41	8/14/2022
42	8/15/2022
43	8/16/2022
44	8/17/2022
45	8/18/2022
46	8/19/2022
47	8/20/2022
48	8/21/2022
49	8/22/2022
50	8/23/2022
51	8/24/2022
52	8/25/2022
53	8/26/2022
54	8/27/2022
55	8/28/2022
56	8/29/2022
57	8/30/2022
58	8/31/2022
James2