Sending a moving range of cells to different people

[Saschah]

Hey Guys,

Okay this is going to be difficult to explain...

First let me explain what I am trying to do.

I have made a big planning with 46 different people in. They all have their collum. (dates are on the rows)
They all need to get their piece of the planning.

That for the easy part..

The planning grows each week,
The days in the past remain in the planning while every week I add new weeks...
Each person needs to get their planning for the next 3 months.

So i'll try to set up an example:

We have a person C, D, F and G, named after their collums. (G's planning has multiple collums)

This week they need to get their planning as in:

C needs: Cell C5:C50
D needs: Cell D5:D50
F needs: Cell F5:F50
G needs: Cell G5:I50

Next week they need to get this planning:
C needs: Cell C10:C55
D needs: Cell D10:D55
F needs: Cell F10:F55
G needs: Cell G10:I55


So i think the moving part is the most difficult?

I have worked something out with different tabs per person but when i move stuff around it doesn't always changes which kind of makes my planning useless...

Could you guys/girls help me out?
If I didn't explained it clear enough, just let me know.

Thanks!!

 

[SpillerBD]

The moving Part is a filter.
Does it matter what output they receive. Could they receive a XPS/PDF or does it have to be Excel or table pasted to Email?

You could build a Pivot Table for Each recipient using only ROWs. Date and Column

 

[Saschah]

Hey SpillerBD,

Thanks for your quick response.
I prefer them to get a PDF.

What should happen is:

A B C D E F

Date Person 1 Person 2 Person 3 Person 4 Person 4

01/01/2017 PlaceA PlaceE PlaceB PlaceA PlaceC
02/01/2017 .......................................
....
...
.
...
.
.
.
.

And then they only need to get their colum with the range of the next ... weeks from "today"

 

[SpillerBD]

I'd use Pivot Table Reporting. What version of Excel are you using?

 

[Saschah]

Well, I'm fairly new to all the advanced features of Excel...
Could you explain what this Pivot Table is?

I'm using Excel 2016

 

[Saschah]

I'd use Pivot Table Reporting. What version of Excel are you using?

So....

I have been trying things with the Pivot table,
but i can't seem to get it to work.

How exactly were you thinking how it could work?

Thanks!

 

[FormR]

Hi, you could give the below macro a try:

I prefer them to get a PDF.

It creates PDF documents in the folder specified (see comments in code) named after the persons name.

A B C D E F

Date Person 1 Person 2 Person 3 Person 4 Person 4

01/01/2017 PlaceA PlaceE PlaceB PlaceA PlaceC
02/01/2017 .......................................
....

And assumes that this table begins in cell A1 of the active sheet.

Sub M1()
Dim i As Long, oldPrintA As String, dStart As Long, dEnd As Long
Const sFolder As String = "C:\Temp" 'Change folder path here
dStart = Date
dEnd = DateAdd("WW", 10, dStart) '10 = number of weeks
With Range("A1").CurrentRegion
    oldPrintA = .Parent.PageSetup.PrintArea
    For i = 2 To .Columns.Count '2 = Colum where the names start
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:=">=" & dStart, Operator:=xlAnd, Criteria2:="<=" & dEnd
        .AutoFilter Field:=i, Criteria1:="<>"
        .Parent.PageSetup.PrintArea = .Resize(, i).Address
        .Parent.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sFolder & "\" & Cells(1, i).Value & ".PDF", OpenAfterPublish:=False
        .Columns(i).Hidden = True
    Next i
    .Parent.PageSetup.PrintArea = oldPrintA
    .Columns.Hidden = False
    .AutoFilter
End With
End Sub

 

[Saschah]

Hi, you could give the below macro a try:



It creates PDF documents in the folder specified (see comments in code) named after the persons name.



And assumes that this table begins in cell A1 of the active sheet.

Sub M1()
Dim i As Long, oldPrintA As String, dStart As Long, dEnd As Long
Const sFolder As String = "C:\Temp" 'Change folder path here
dStart = Date
dEnd = DateAdd("WW", 10, dStart) '10 = number of weeks
With Range("A1").CurrentRegion
    oldPrintA = .Parent.PageSetup.PrintArea
    For i = 2 To .Columns.Count '2 = Colum where the names start
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:=">=" & dStart, Operator:=xlAnd, Criteria2:="<=" & dEnd
        .AutoFilter Field:=i, Criteria1:="<>"
        .Parent.PageSetup.PrintArea = .Resize(, i).Address
        .Parent.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sFolder & "\" & Cells(1, i).Value & ".PDF", OpenAfterPublish:=False
        .Columns(i).Hidden = True
    Next i
    .Parent.PageSetup.PrintArea = oldPrintA
    .Columns.Hidden = False
    .AutoFilter
End With
End Sub

Hey Formr,

Thanks for your response,
When I use this Macro it doesn't work.

Maybe if I give you an example it might help?

First an example of my schedule
https://ibb.co/eiQmYk

And the code with the error
https://ibb.co/cahYDk

 

[FormR]

Hi, pictures don't help much I'm afraid as they would require any potential helper to manually re-type all the data to test with and they are especially un-helpful when we can't see the column or row labels.

See here for ways to post example data directly in the thread:

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Below was my test set-up, if you re-create it in a new workbook and use the code in post#7 do you get the results you would expect?

Excel 2013/2016
	A	B	C	D	E	F
1	Date	Person1	Person2	Person3	Person4	Person5
2	01/09/2017	PlaceA	PlaceE	PlaceB	PlaceA	PlaceC
3	02/09/2017
4	03/09/2017		PlaceA	PlaceB
5	04/09/2017	PlaceA			PlaceA
6	05/09/2017		PlaceB			PlaceC
7	06/09/2017	PlaceE
8	07/09/2017	PlaceA	PlaceE	PlaceB	PlaceA	PlaceC
9	08/09/2017
10	09/09/2017		PlaceA	PlaceB
11	10/09/2017	PlaceA			PlaceA
12	11/09/2017		PlaceB			PlaceC
13	12/09/2017	PlaceE
14	13/09/2017	PlaceA	PlaceE	PlaceB	PlaceA	PlaceC
15	14/09/2017
16	15/09/2017		PlaceA	PlaceB
17	16/09/2017	PlaceA			PlaceA
18	17/09/2017		PlaceB			PlaceC
Sheet1

 

[Saschah]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Kolom1	Def. Contract	Datum	Fundering Bart	Fundering Bruynseraede	Fundering Ditro	Fundering Y. Haelen	Fundering WDB	Fundering Kelder	BT-Works	Montage Johan	Montage Miki	Montage Timo	Montage Roel	Montage V. Boxelaer
2	0		W 27
3	0		03 jul 17	Frans F3							Yperman M-01 (J)	Jondral M-01 (J)	Hurts M-01 (S)	Gevers M-01 (J)
4	0		04 jul 17								Yperman M-01 (J)	Jondral M-01 (J)	Hurts M-01 (S)	V. Ammel Ramen
5	0	Pinto (S)	05 jul 17	Hamerlinck F3							Yperman M-01 (J)	Jondral M-01 (J)	Hurts M-01 (S)	V. Ammel Ramen
6	0	Geerts (S)	06 jul 17	Pinto F3							Hurts M-01 (S)	Hurts M-01 (S)	Hurts M-01 (S)	Braspenning (S) Glas +
7	0		07 jul 17	Van Dessel uitzetten							Hurts M-01 (S)	Hurts M-01 (S)	Hurts M-01 (S)	Eco-Dop
8	0		W 31	Verlof								Verlof
9	0		31 jul 17	Pinto (S) muurbalken						Hamerlinck (J) Muurl.	Hurts M-01 (S)	De Jongh M-01 (J)	Hurts M-01 (S)	De Jongh M-01 (J)	VD Eeden M-01 (J)
10	0		01 aug 17	Geerts (S) F1							Frans M-01 (S)	Pinto M-01 (S)	Jondral M-01 (J)	De Jongh M-01 (J)	VD Eeden M-01 (J)
11	0	Van Dessel (J)	02 aug 17	Carpentier (J) Uitz. + VD Velde	Geerts (S) F2						Frans M-01 (S)	Pinto M-01 (S)	Verberckmoes (J) M-02	Maat (L)	VD Eeden M-01 (J)
12	0	Carpentier (J)	03 aug 17	V. Doninck (J) + VD Velde Riol.	Geerts (S) F2						Verberckmoes (J) M-02	Pinto M-01 (S)	Verberckmoes (J) M-02	Maat (L)	VD Eeden M-01 (J)
13	0	Gobeyn (J)	04 aug 17	Carpentier (J) F1							Pinto M-01 (S)	Pinto M-01 (S)	Verberckmoes (J) M-02	Maat (L)	VD Eeden M-01 (J)
14	0		W 32	Werkweek
15	0	Bellemans (M)	07 aug 17	De Hondt (S) Riolering	Geerts (S) F3					Bellemans (M) Muurl.	Frans M-01 (S)	Pinto M-01 (S)	Verberckmoes (J) M-02	Ceuppens	VD Eeden M-01 (J)
16	0		08 aug 17	Colp. F-1						Geerts (S) Muurl.	Frans M-01 (S)	Frans M-01 (S)	Hurts M-02 (S)	Hurts M-02 (S)	Hamerlinck (J) M-01
17	0	De Copin (S) 10u	09 aug 17	Van Dessel (J) F1							Frans M-01 (S)	Frans M-01 (S)	Hurts M-02 (S)	Hurts M-02 (S)	Hamerlinck (J) M-01
18	0	Laster (S) 10u	10 aug 17	De Copin (S) F1					Van Dessel (J) Kelder		Frans M-01 (S)	Frans M-01 (S)	Engelen (S) Ramen 10/8	Engelen (S) Ramen 10/8	Hamerlinck (J) M-01
19	0		11 aug 17	Carpentier (J) F3 + Muurl.					Van Dessel (J) Kelder	Dickens (S) Muurl.	Frans M-01 (S)	Bellemans (M) M-01	Bellemans (M) M-01	Engelen (S) M-02	Hamerlinck (J) M-01
20	0		W 33
21	0	Dickens (S) 13u	14 aug 17	De Copin (S) F-2					Van Dessel (J) Kelder		Bellemans (M) M-01	Bellemans (M) M-01	Geerts (S) M-01	Geerts (S) M-01
22	0		15 aug 17
23	0	Huysmans (P)	16 aug 17	Lasters (S) F1					Van Dessel (J) Kelder		Bellemans (M) M-01	Bellemans (M) M-01	Geerts (S) M-01	Geerts (S) M-01	Hamerlinck (J) M-01
Huidige planning