Non contiguous cells

[SWEETJONNYCRASH]

Hi I am wondering if there is a way to take a non contiguous range of cells from sheet1 of my workbook and have them printed on every page? So if I want a1-a10 and g5-g10 to print on every page it will? Any help would be greatly appreciated.

 

[sheetspread]

Sub copyrangetosheets()
    Dim ws As Worksheet
 
    For Each ws In Worksheets
        
        ws.Range("A1:A10").Value = Sheets("SourceData").Range("A1:A10").Value
        ws.Range("G5:G10").Value = Sheets("SourceData").Range("G5:G10").Value
                     
        
    Next ws
        
End Sub

Change "SourceData" to the name of your original sheet

 

[SWEETJONNYCRASH]

That copies it to each Sheet in the workbook. What I want to do is basically take the range of cells and have it printed on each page that is printed. So if I print sheet1 and it goes on multiple pages these cells are printed on each page. I'm sorry I am new to this forum thing.

 

[sheetspread]

Are the corresponding ranges on each following page blank? Or do you wish to replace text/values just when printing?

 

[SWEETJONNYCRASH]

They are blank.

 

[sheetspread]

I don't know how to alter Excel's function in the print page setup so that segments are repeated instead of whole rows or columns. I can give you a macro to detect page breaks and paste the ranges n cells above, but it will replace the blanks unless you or the macro creates a copy first.

 

[SWEETJONNYCRASH]

Thanks. If you could post that I may be able to use it

 

[sheetspread]

Excel 2003
	A	B	C	D	E	F	G
1	Name	Number	NextName	NextNumber
2	Billings	104,170	Yellowstone	408,698
3	Missoula	66,789	Missoula	746,367
4	Great Falls	58,505	Cascade	974,535
5	Bozeman	37,280	Gallatin	757,299			Gallatin
6	Butte	34,525	Silver Bow	553,122			Silver Bow
7	Helena	28,190	Lewis and Clark	663,335			Lewis and Clark
8	Kalispell	19,927	Flathead	23,253			Flathead
9	Havre	9,310	Hill	712,138			Hill
10	Anaconda	9,298	Deer Lodge	146,202			Deer Lodge
11		8,123	Custer	615,015
12		7,389	Gallatin	818,323
13		7,044	Park	843,439
14		6,781	Yellowstone	390,148
15		6,357	Flathead	323,615
16		5,901	Fergus	504,941
17		5,191	Richland	111,206
18		4,935	Dawson	578,118
19		4,688	Flathead	310,916
20		4,598	Lake	766,566
21		4,348	Ravalli	381,227
22		4,134	Beaverhead	855,205
23		3,505	Big Horn	896,694
24		3,376	Toole	709,113
25		3,250	Valley	280,084
26		3,111	Powell	347,472
27		2,869	Glacier	211,660
28		2,628	Lincoln	474,379
29		2,621	Roosevelt	411,596
30		2,570	Pondera	822,763
31		2,214	Rosebud	512,101
32		2,125	Carbon	928,540
33		1,997	Phillips	558,792
34		1,984	Lewis and Clark	987,166
35		1,893	Stillwater	678,179
36		1,878	Broadwater	43,877
37		1,871	Lake	445,993
38		1,869	Gallatin	274,830
39		1,809	Ravalli	468,798
40		1,788	Musselshell	298,098
41		1,777	Rosebud	827,919
42		1,741	Fallon	791,472
43		1,641	Sweet Grass	327,908
44		1,684	Teton	475,151
45		1,520	Gallatin	627,192
46		1,734	Sheridan	19,940
47		1,464	Chouteau	668,769
48		1,313	Sanders	67,553
49		1,271	Gallatin	932,138
50		1,203	Blaine	723,260
51		1,183	Jefferson	473,585
52		1,048	Sanders	363,092
53		1,038	Jefferson	911,226
54		1,037	Lincoln	272,206
55		1,017	Daniels	318,310
56		1,016	Glacier	469,174
57		997	Wheatland	474,154
58		939	Meagher	866,421
59		938	Lincoln	427,030
60		917	Ravalli	27,832
61		847	Liberty	356,726
62		842	Lake	613,509
63		840	Richland	882,682
64		838	Madison	334,243
65		820	Granite	956,811
66		812	Mineral	761,528
67		810	Roosevelt	561,256
68		808	Blaine	759,127
69		720	Ravalli	811,715
70		714	Roosevelt	892,608
71		708	Carbon	39,814
72		708	Teton	868,966
73		685	Cascade	928,645
74		675	Silver Bow	863,180
75		642	Madison	932,403
76		615	McCone	547,023
77		605	Prairie	998,654
78		598	Chouteau	759,782
79		597	Cascade	683,857
80		595	Carbon	605,970
81		589	Wibaux	31,292
82		544	Sanders	860,737
83		509	Pondera	771,994
84		468	Powder River	777,889
85		438	Carbon	908,762
86		428	Big Horn	214,068
87		420	Mineral	169,076
88		401	Judith Basin	104,699
89		375	Toole	577,529
90		375	Madison	650,573
91		343	Garfield	31,696
92		332	Carter	48,674
93		316	Teton	800,623
94		312	Treasure	213,946
95		309	Granite	126,042
96		290	Valley	747,363
97		288	Park	907,318
98		261	Chouteau	670,683
99		255	Roosevelt	92,096
100		255	Fergus	337,661
Sheet4 (4)

Sub printsectionbeforeeachpagebreak()
Dim x As Integer
Dim LR As Integer
LR = Range("B" & Rows.Count).End(xlUp).Row 'or whatever column/value equals the number of rows
For x = 1 To LR
If Cells(x, 1).EntireRow.PageBreak <> -4142 Then Range("G" & x + 4 & ":" & "G" & (x + 9)) = Range("G5:G10").Value
If Cells(x, 1).EntireRow.PageBreak <> -4142 Then Range("A" & x & ":" & "A" & (x + 9)) = Range("A1:A10").Value
Next
End Sub

Excel 2003
	A	B	C	D	E	F	G
1	Name	Number	NextName	NextNumber
2	Billings	104,170	Yellowstone	408,698
3	Missoula	66,789	Missoula	746,367
4	Great Falls	58,505	Cascade	974,535
5	Bozeman	37,280	Gallatin	757,299			Gallatin
6	Butte	34,525	Silver Bow	553,122			Silver Bow
7	Helena	28,190	Lewis and Clark	663,335			Lewis and Clark
8	Kalispell	19,927	Flathead	23,253			Flathead
9	Havre	9,310	Hill	712,138			Hill
10	Anaconda	9,298	Deer Lodge	146,202			Deer Lodge
11		8,123	Custer	615,015
12		7,389	Gallatin	818,323
13		7,044	Park	843,439
14		6,781	Yellowstone	390,148
15		6,357	Flathead	323,615
16		5,901	Fergus	504,941
17		5,191	Richland	111,206
18		4,935	Dawson	578,118
19		4,688	Flathead	310,916
20		4,598	Lake	766,566
21		4,348	Ravalli	381,227
22		4,134	Beaverhead	855,205
23		3,505	Big Horn	896,694
24		3,376	Toole	709,113
25		3,250	Valley	280,084
26		3,111	Powell	347,472
27		2,869	Glacier	211,660
28		2,628	Lincoln	474,379
29		2,621	Roosevelt	411,596
30		2,570	Pondera	822,763
31		2,214	Rosebud	512,101
32		2,125	Carbon	928,540
33		1,997	Phillips	558,792
34		1,984	Lewis and Clark	987,166
35		1,893	Stillwater	678,179
36		1,878	Broadwater	43,877
37		1,871	Lake	445,993
38		1,869	Gallatin	274,830
39		1,809	Ravalli	468,798
40		1,788	Musselshell	298,098
41		1,777	Rosebud	827,919
42		1,741	Fallon	791,472
43		1,641	Sweet Grass	327,908
44		1,684	Teton	475,151
45		1,520	Gallatin	627,192
46		1,734	Sheridan	19,940
47		1,464	Chouteau	668,769
48		1,313	Sanders	67,553
49		1,271	Gallatin	932,138
50		1,203	Blaine	723,260
51		1,183	Jefferson	473,585
52		1,048	Sanders	363,092
53	Name	1,038	Jefferson	911,226
54	Billings	1,037	Lincoln	272,206
55	Missoula	1,017	Daniels	318,310
56	Great Falls	1,016	Glacier	469,174
57	Bozeman	997	Wheatland	474,154			Gallatin
58	Butte	939	Meagher	866,421			Silver Bow
59	Helena	938	Lincoln	427,030			Lewis and Clark
60	Kalispell	917	Ravalli	27,832			Flathead
61	Havre	847	Liberty	356,726			Hill
62	Anaconda	842	Lake	613,509			Deer Lodge
63		840	Richland	882,682
64		838	Madison	334,243
65		820	Granite	956,811
66		812	Mineral	761,528
67		810	Roosevelt	561,256
68		808	Blaine	759,127
69		720	Ravalli	811,715
70		714	Roosevelt	892,608
71		708	Carbon	39,814
72		708	Teton	868,966
73		685	Cascade	928,645
74		675	Silver Bow	863,180
75		642	Madison	932,403
76		615	McCone	547,023
77		605	Prairie	998,654
78		598	Chouteau	759,782
79		597	Cascade	683,857
80		595	Carbon	605,970
81		589	Wibaux	31,292
82		544	Sanders	860,737
83		509	Pondera	771,994
84		468	Powder River	777,889
85		438	Carbon	908,762
86		428	Big Horn	214,068
87		420	Mineral	169,076
88		401	Judith Basin	104,699
89		375	Toole	577,529
90		375	Madison	650,573
91		343	Garfield	31,696
92		332	Carter	48,674
93		316	Teton	800,623
94		312	Treasure	213,946
95		309	Granite	126,042
96		290	Valley	747,363
97		288	Park	907,318
98		261	Chouteau	670,683
99		255	Roosevelt	92,096
100		255	Fergus	337,661
Sheet4 (4)

 

[SWEETJONNYCRASH]

Well actually I can use the whole rows. My problem is that when you do the range in print setup they can't be non contiguous. I actually want rows 1-7 and rows 16&17 to print at the top of each page. Is that possible?

 

[sheetspread]

The code above can be changed for any range, but it will still replace info in the sheet rather than only appear when printing.