set range as today

[tex1966]

Hi all,

My sheet gets production numbers via a getpivotdata formula. That part works fine.
My problem is that the original Pivot is only valid for today and I need to track numbers for a whole year.

So I use getpivotdata to show me what we did today, but this only shows me what we did today. Yesterday is blank because there is no yesterday in the pivot table. That kills historical productivity data. I can't format the pivot to include data for the year because that would bog everything down.

I'm trying to get around this problem by hard-copying the results as only a value. I only want to do this in the row for today. All older dates should be ignored and the historical value remains unchanged.
Calendar dates are listed in Range A3:A367

So the question is: How can I set the range to only be for today?

My macro looks like this but I need to adjust the range part for the selection and copy. Any help would be appreciated.

Range("C3:C367").Select
Selection.Copy
Range("D3:d367").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Cheers
Tex1966

 

[oldbrewer]

if you have all the historic data why not pull the info you want from that - show us a few rows of data and what output you want....

 

[tex1966]

I don't yet have the historical data as I'm building the file as we speak.

I can't figure out how to show rows of data. This forum isn't very flexible with things like inserting a table.

In Column A are dates
In Column B is the GetPivotData
In Column C I want to copy the data from Column B - but only if the date in Colum A is today. Otherwise nothing should be done as this data was provided yesterday

The "active" range should be for the cells with today's date.
i.e., Cell A100 has today's date. This cell should the be the active range.


Another way of doing this would be to find today's date and let the formulas in the related rows to be active. All previous dates would have the formulas removed and replaced with the values that were generated. I hope that I've explained this clearly enough.

Find today, use the formula.
Older than today, remove formula and replace with values.

cheers

 

[oldbrewer]

date	data	extraction		today=	21/03/2018
20/03/2018	11
19/03/2018	17
21/03/2018	23	23
20/03/2018	29
19/03/2018	35
21/03/2018	41	41
20/03/2018	47
19/03/2018	53
21/03/2018	59	59
		do you want to add 23,41,59
		or explain further in terms
		of this example data

<colgroup><col><col span="4"><col><col></colgroup><tbody>
</tbody>

 

[oldbrewer]

	01/03/2018	04/03/2018	07/03/2018	10/03/2018	13/03/2018	16/03/2018	19/03/2018	22/03/2018	25/03/2018	28/03/2018
dept1	31	23	16	26	10	19	34	34	13	48
dept2	49	33	14	32	45	26	0	25	28	1
dept3	14	39	4	43	29	15	18	3	5	42
			13/03/2018
				1	10
				2	45
				3	29
				4
				5
			alternatively pull the column that matches the date in D8

<colgroup><col><col span="2"><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>