Adjusting print border with VBA

L

LordVoldetort_IV

New Member
Joined
Jun 10, 2021
Messages
38
Office Version
  1. 365
Platform
  1. Windows
Hello!

I am working on a template for the tax calculation for an individual and certain sections open when you select them, but this hiding and unhiding of sections is messing with the print borders (print preview view) is there a way that i can use VBA or something else to auto adjust the borders after they have been hidden / unhidden?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Probably, but we would need a bit more details to help you with that.

It may be helpful to show us what this sheet looks like, and what adjustments need to be made.

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.
 
Upvote 0
Joe4 said:
Probably, but we would need a bit more details to help you with that.

It may be helpful to show us what this sheet looks like, and what adjustments need to be made.

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.
Click to expand...
i'll give it a go and post it on here asap, thanks!
 
Upvote 0
Joe4 said:
Probably, but we would need a bit more details to help you with that.

It may be helpful to show us what this sheet looks like, and what adjustments need to be made.

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.
Click to expand...
Template for Individual tax calculation v0.1.xlsm
ABCDEFGHIJKLMNOPQRS
10 Intitial layout collum
2
3TAX CALCULATION
4For the year ended 28 February 2021
5
6Note(s)2021 Source link
7R
8
9INCOME
10FALSE
11Source of income 1 eg. Moore Southern Cape Inc. (rename)FALSE
12Type of income eg. Salary (rename)- FALSE
13Type of income (rename)20.00 <-- Link source in here FALSE
14Type of income (rename)60.00 <-- Link source in here FALSE
15Type of income (rename)- FALSE
16Type of income (rename)- FALSE
17Type of income (rename)- FALSE
18 FALSE
19Source of income 2 (rename) FALSE
20Type of income (rename)- FALSE
21Type of income (rename)- FALSE
22Type of income (rename)50.00 <-- Link source in here FALSE
23Type of income (rename)- FALSE
24Type of income (rename)- FALSE
25Type of income (rename)- FALSE
26 TRUE
27Source of income 3 (rename) TRUE
28Type of income (rename)- TRUE
29Type of income (rename)- TRUE
30Type of income (rename)- TRUE
31Type of income (rename) TRUE
32Type of income (rename)- TRUE
33Type of income (rename)- TRUE
34FALSE
35Investment IncomeFALSE
36Local interest100,000.00FALSE
37Foreign interest100.00TRUE
38Local dividends500.00FALSE
39Foreign dividends600.00FALSE
40REIT income800.00TRUE
41Other income950.00FALSE
42Less: 50% of investment income from spouseFALSE
43Add: 50% of investment income to spouseFALSE
44
45DEDUCTIONS AND EXEMPTIONSFALSE
46FALSE
47Less: Interest exemption(23,800.00)FALSE
48Less: Foreign dividend exemption(333.33)FALSE
49Less: Local dividends subject to dividends tax1(500.00)FALSE
50Less: Retirement fund and annuity contributions2(21,572.83)FALSE
51TRUE
52Add: Taxable capital gains3TRUE
53
54TAXABLE INCOME56,873.83
55
56Normal taxation10,237.29
57Less: Primary rebate (limited to tax payable)(10,237.29)
58Less: Secondary rebate-TRUE
59Less: Tertiary rebate-TRUE
60Less: Medical tax credits4-FALSE
61Less: Foreign tax (limited to R0)- FALSE
62-TRUE
63TRUE
64Less: Employees' tax-TRUE
65Less: First provisional tax paymentTRUE
66Less: Second provisional tax paymentTRUE
67TRUE
68TAX PAYABLE TO / (RECEIVABLE FROM) THE SARS-
69
700<----- Notes Start
71
72NOTES TO THE TAX CALCULATION
73For the year ended 28 February 2021
74
752021
76R
77
781Local dividends subject to dividends taxFALSE
79FALSE
80Gross local dividends receivedFALSE
81FALSE
82Dividends tax-FALSE
83Dividends tax paid-FALSE
84-FALSE
85FALSE
862Retirement fund and annuity contributionsFALSE
87FALSE
88Contributions not utilised in the previous year-FALSE
89Current year contributions360,000.00FALSE
90Less: Contributions utilised in current year(21,572.83)FALSE
91Contributions carried forward to the next year338,427.17FALSE
92FALSE
933Taxable capital gainsTRUE
94TRUE
95Name of institutionTRUE
96Proceeds-TRUE
97Base cost-TRUE
98Capital gain / loss-TRUE
99TRUE
100Name of institutionTRUE
101Proceeds-TRUE
102Base cost-TRUE
103Capital gain / loss-TRUE
104TRUE
105Name of institutionTRUE
106Proceeds-TRUE
107Base cost-TRUE
108Capital gain / loss-TRUE
109TRUE
110Name of institutionTRUE
111Proceeds-TRUE
112Base cost-TRUE
113Capital gain / loss-TRUE
114TRUE
115Name of institutionTRUE
116Proceeds-TRUE
117Base cost-TRUE
118Capital gain / loss-TRUE
119TRUE
120Total capital gain / loss-TRUE
121Annual exemption-TRUE
122Less: capital loss utilised-TRUE
123Taxable capital gains included @ 40%-TRUE
124TRUE
125Capital loss - brought over-TRUE
126Capital loss during the year-TRUE
127Less: capital loss utilised-TRUE
128TRUE
129Capital loss - carried forward-TRUE
130TRUE
Tax Calculation
Cell Formulas
RangeFormula
B1B1='Cover page'!D12
B3:B4B3='Cover page'!D15
L6,L75L6=RIGHT(B4,4)
O12O12=IF(L12<>0,"<-- Link source in here","")
O13:O33O13=IF(AND(L13<>0,N13=""),"<-- Link source in here","")
S10S10=T10
S11S11=IF(AND('Info Sheet'!D26="yes",'Info Sheet'!D31="yes",'Info Sheet'!D33>=1),FALSE,TRUE)
S123:S130,S121,S116:S119,S111:S114,S106:S109,S101:S104,S96:S99,S94,S87:S92,S79:S85,S66,S52,S43,S35,S27:S33,S19:S25,S12:S17S12=S11
S18S18=IF(AND('Info Sheet'!D26="yes",'Info Sheet'!D31="yes",'Info Sheet'!D33>=2),FALSE,TRUE)
S26S26=IF(AND('Info Sheet'!D26="yes",'Info Sheet'!D31="yes",'Info Sheet'!D33>=3),FALSE,TRUE)
S34S34=IF('Info Sheet'!D38="yes",FALSE,TRUE)
S36S36=IF(AND('Info Sheet'!D40="yes",S34=FALSE),FALSE,TRUE)
S37S37=IF(AND('Info Sheet'!D42="yes",S34=FALSE),FALSE,TRUE)
S38S38=IF(AND('Info Sheet'!D46="yes",S34=FALSE),FALSE,TRUE)
S39S39=IF(AND('Info Sheet'!D48="yes",S34=FALSE),FALSE,TRUE)
S40S40=IF(AND('Info Sheet'!D52="yes",S34=FALSE),FALSE,TRUE)
S41S41=IF(AND('Info Sheet'!D58="yes",S34=FALSE),FALSE,TRUE)
S42S42=IF(AND('Info Sheet'!D20="yes",S34=FALSE),FALSE,TRUE)
C50C50=C86
J49J49=B78
J50J50=B86
L47L47=IF(('Info Sheet'!K19<65),IF(L36<='Tax Tables 2021'!D17,-L36,-'Tax Tables 2021'!D17),IF(L36<='Tax Tables 2021'!D18,-L36,-'Tax Tables 2021'!D18))
L48L48=-L39*25/45
L49L49=-L38
L50L50=L90
J52J52=B93
S45,S62S45=S46
S46S46=IF(AND(S47=TRUE,S48=TRUE,S49=TRUE,S50=TRUE),TRUE,FALSE)
S47,S49S47=S36
S48S48=S39
S50S50=IF('Info Sheet'!D64="yes",FALSE,TRUE)
S51S51=IF('Info Sheet'!D54="yes",FALSE,TRUE)
L54L54=SUM(L12:L53)
J60J60=IF(S131=FALSE,B131,IF(S150=FALSE,B150,""))
C57C57=IF((L56+'Tax Tables 2021'!D13)>0,"Primary rebate","Primary rebate (limited to tax payable)")
C58C58=IF('Info Sheet'!K19>=65,(IF((SUM(L56:L57)+'Tax Tables 2021'!D14)>0,"Secondary rebate","Secondary rebate (limited to tax payable)")),"Secondary rebate")
C59C59=IF('Info Sheet'!K19>=75,(IF((SUM(L56:L58)+'Tax Tables 2021'!D15)>0,"Tertiary rebate","Tertiary rebate (limited to tax payable)")),"Tertiary rebate")
C60C60=IF(L60>(L148+L165),"Medical tax credits (limited to tax payable)","Medical tax credits")
C61C61=IF((ROUND(((L37+L39)/L54)*L56,2))>('Info Sheet'!D44+'Info Sheet'!D50),CONCATENATE("Foreign tax (limited to R",'Info Sheet'!D44+'Info Sheet'!D50,")"),CONCATENATE("Foreign tax (limited to R",-L61,")"))
L56L56='Tax Tables 2021'!H12
L57L57=IF((L56+'Tax Tables 2021'!D13)>0,'Tax Tables 2021'!D13,-L56)
L58L58=IF('Info Sheet'!K19>=65,(IF((SUM(L56:L57)+'Tax Tables 2021'!D14)>0,'Tax Tables 2021'!D14,-SUM(L56:L57))),0)
L59L59=IF('Info Sheet'!K19>=75,(IF((SUM(L56:L58)+'Tax Tables 2021'!D15)>0,'Tax Tables 2021'!D15,-SUM(L56:L58))),0)
L60L60=IF((L56+SUM(L57:L59)-(L148+L165))<=0,-(L56+SUM(L57:L59)),-(L148+L165))
L61L61=IF((ROUND(((L37+L39)/L54)*L56,2))>('Info Sheet'!D44+'Info Sheet'!D50),-('Info Sheet'!D44+'Info Sheet'!D50),-(ROUND(((L37+L39)/L54)*L56,2)))
L62L62=SUM(L56:L60)
S58S58=IF('Info Sheet'!K19<65,TRUE,FALSE)
S59S59=IF('Info Sheet'!K19<75,TRUE,FALSE)
S60S60=IF('Info Sheet'!D68="Yes",FALSE,TRUE)
S61S61=IF(OR('Info Sheet'!D42="yes",'Info Sheet'!D48="yes"),FALSE,TRUE)
S63S63=IF(OR(S64=FALSE,S65=FALSE,S66=FALSE),FALSE,TRUE)
S64S64=IF('Info Sheet'!D83="yes",FALSE,TRUE)
S65S65=IF('Info Sheet'!D85="yes",FALSE,TRUE)
S67S67=S63
L68L68=SUM(L62:L67)
B70B70='Cover page'!D12
B73B73='Cover page'!D16
L82L82=L80*0.2
L83L83=-L82
L84,L118,L113,L108,L103,L98L84=SUM(L82:L83)
L90L90=IF((IF(SUM(L88:L89)>(SUM(L12:L49)*'Tax Tables 2021'!D24),-(SUM(L12:L49)*'Tax Tables 2021'!D24),-SUM(L88:L89)))<-'Tax Tables 2021'!D25,-'Tax Tables 2021'!D25,(IF(SUM(L88:L89)>(SUM(L12:L49)*'Tax Tables 2021'!D24),-(SUM(L12:L49)*'Tax Tables 2021'!D24),-SUM(L88:L89))))
L91L91=SUM(L88:L90)
L120L120=L118+L113+L108+L103+L98
L121L121=IF(L120>40000,-40000,IF(L120<-40000,40000,-L120))
L122L122=L127
L123L123=IF(SUM(L120:L122)>0,SUM(L120:L122)*0.4,0)
L126L126=-IF(SUM(L120:L121)<0,SUM(L120:L121),0)
L127L127=IF(SUM(L120:L121)>0,(IF((SUM(L125:L126)>SUM(L120:L121)),-SUM(L120:L121),-SUM(L125:L126))),0)
L129L129=SUM(L125:L127)
S78S78=IF('Info Sheet'!D46="yes",FALSE,TRUE)
S86S86=IF('Info Sheet'!D64="yes",FALSE,TRUE)
S93S93=IF('Info Sheet'!D54="yes",FALSE,TRUE)
S95S95=IF(AND(S94=FALSE,'Info Sheet'!D56>=1),FALSE,TRUE)
S100S100=IF(AND(S94=FALSE,'Info Sheet'!D56>=2),FALSE,TRUE)
S105S105=IF(AND(S94=FALSE,'Info Sheet'!D56>=3),FALSE,TRUE)
S110S110=IF(AND(S94=FALSE,'Info Sheet'!D56>=4),FALSE,TRUE)
S115S115=IF(AND(S94=FALSE,'Info Sheet'!D56>=5),FALSE,TRUE)
S120S120=S94
S122S122=S94
Cells with Conditional Formatting
CellConditionCell FormatStop If True
L28:L33Cell Value=0textNO
L20:L25Cell Value=0textNO
L13:L17Cell Value=0textNO
L12Cell Value=0textNO
Cells with Data Validation
CellAllowCriteria
L96Whole number>=0
L97Whole number<=0
L101Whole number>=0
L102Whole number<=0
L106Whole number>=0
L107Whole number<=0
L111Whole number>=0
L112Whole number<=0
L116Whole number>=0
L117Whole number<=0
C86:F86List=$V$2:$V$4
 
Upvote 0
LordVoldetort_IV said:
Template for Individual tax calculation v0.1.xlsm
ABCDEFGHIJKLMNOPQRS
10 Intitial layout collum
2
3TAX CALCULATION
4For the year ended 28 February 2021
5
6Note(s)2021 Source link
7R
8
9INCOME
10FALSE
11Source of income 1 eg. Moore Southern Cape Inc. (rename)FALSE
12Type of income eg. Salary (rename)- FALSE
13Type of income (rename)20.00 <-- Link source in here FALSE
14Type of income (rename)60.00 <-- Link source in here FALSE
15Type of income (rename)- FALSE
16Type of income (rename)- FALSE
17Type of income (rename)- FALSE
18 FALSE
19Source of income 2 (rename) FALSE
20Type of income (rename)- FALSE
21Type of income (rename)- FALSE
22Type of income (rename)50.00 <-- Link source in here FALSE
23Type of income (rename)- FALSE
24Type of income (rename)- FALSE
25Type of income (rename)- FALSE
26 TRUE
27Source of income 3 (rename) TRUE
28Type of income (rename)- TRUE
29Type of income (rename)- TRUE
30Type of income (rename)- TRUE
31Type of income (rename) TRUE
32Type of income (rename)- TRUE
33Type of income (rename)- TRUE
34FALSE
35Investment IncomeFALSE
36Local interest100,000.00FALSE
37Foreign interest100.00TRUE
38Local dividends500.00FALSE
39Foreign dividends600.00FALSE
40REIT income800.00TRUE
41Other income950.00FALSE
42Less: 50% of investment income from spouseFALSE
43Add: 50% of investment income to spouseFALSE
44
45DEDUCTIONS AND EXEMPTIONSFALSE
46FALSE
47Less: Interest exemption(23,800.00)FALSE
48Less: Foreign dividend exemption(333.33)FALSE
49Less: Local dividends subject to dividends tax1(500.00)FALSE
50Less: Retirement fund and annuity contributions2(21,572.83)FALSE
51TRUE
52Add: Taxable capital gains3TRUE
53
54TAXABLE INCOME56,873.83
55
56Normal taxation10,237.29
57Less: Primary rebate (limited to tax payable)(10,237.29)
58Less: Secondary rebate-TRUE
59Less: Tertiary rebate-TRUE
60Less: Medical tax credits4-FALSE
61Less: Foreign tax (limited to R0)- FALSE
62-TRUE
63TRUE
64Less: Employees' tax-TRUE
65Less: First provisional tax paymentTRUE
66Less: Second provisional tax paymentTRUE
67TRUE
68TAX PAYABLE TO / (RECEIVABLE FROM) THE SARS-
69
700<----- Notes Start
71
72NOTES TO THE TAX CALCULATION
73For the year ended 28 February 2021
74
752021
76R
77
781Local dividends subject to dividends taxFALSE
79FALSE
80Gross local dividends receivedFALSE
81FALSE
82Dividends tax-FALSE
83Dividends tax paid-FALSE
84-FALSE
85FALSE
862Retirement fund and annuity contributionsFALSE
87FALSE
88Contributions not utilised in the previous year-FALSE
89Current year contributions360,000.00FALSE
90Less: Contributions utilised in current year(21,572.83)FALSE
91Contributions carried forward to the next year338,427.17FALSE
92FALSE
933Taxable capital gainsTRUE
94TRUE
95Name of institutionTRUE
96Proceeds-TRUE
97Base cost-TRUE
98Capital gain / loss-TRUE
99TRUE
100Name of institutionTRUE
101Proceeds-TRUE
102Base cost-TRUE
103Capital gain / loss-TRUE
104TRUE
105Name of institutionTRUE
106Proceeds-TRUE
107Base cost-TRUE
108Capital gain / loss-TRUE
109TRUE
110Name of institutionTRUE
111Proceeds-TRUE
112Base cost-TRUE
113Capital gain / loss-TRUE
114TRUE
115Name of institutionTRUE
116Proceeds-TRUE
117Base cost-TRUE
118Capital gain / loss-TRUE
119TRUE
120Total capital gain / loss-TRUE
121Annual exemption-TRUE
122Less: capital loss utilised-TRUE
123Taxable capital gains included @ 40%-TRUE
124TRUE
125Capital loss - brought over-TRUE
126Capital loss during the year-TRUE
127Less: capital loss utilised-TRUE
128TRUE
129Capital loss - carried forward-TRUE
130TRUE
Tax Calculation
Cell Formulas
RangeFormula
B1B1='Cover page'!D12
B3:B4B3='Cover page'!D15
L6,L75L6=RIGHT(B4,4)
O12O12=IF(L12<>0,"<-- Link source in here","")
O13:O33O13=IF(AND(L13<>0,N13=""),"<-- Link source in here","")
S10S10=T10
S11S11=IF(AND('Info Sheet'!D26="yes",'Info Sheet'!D31="yes",'Info Sheet'!D33>=1),FALSE,TRUE)
S123:S130,S121,S116:S119,S111:S114,S106:S109,S101:S104,S96:S99,S94,S87:S92,S79:S85,S66,S52,S43,S35,S27:S33,S19:S25,S12:S17S12=S11
S18S18=IF(AND('Info Sheet'!D26="yes",'Info Sheet'!D31="yes",'Info Sheet'!D33>=2),FALSE,TRUE)
S26S26=IF(AND('Info Sheet'!D26="yes",'Info Sheet'!D31="yes",'Info Sheet'!D33>=3),FALSE,TRUE)
S34S34=IF('Info Sheet'!D38="yes",FALSE,TRUE)
S36S36=IF(AND('Info Sheet'!D40="yes",S34=FALSE),FALSE,TRUE)
S37S37=IF(AND('Info Sheet'!D42="yes",S34=FALSE),FALSE,TRUE)
S38S38=IF(AND('Info Sheet'!D46="yes",S34=FALSE),FALSE,TRUE)
S39S39=IF(AND('Info Sheet'!D48="yes",S34=FALSE),FALSE,TRUE)
S40S40=IF(AND('Info Sheet'!D52="yes",S34=FALSE),FALSE,TRUE)
S41S41=IF(AND('Info Sheet'!D58="yes",S34=FALSE),FALSE,TRUE)
S42S42=IF(AND('Info Sheet'!D20="yes",S34=FALSE),FALSE,TRUE)
C50C50=C86
J49J49=B78
J50J50=B86
L47L47=IF(('Info Sheet'!K19<65),IF(L36<='Tax Tables 2021'!D17,-L36,-'Tax Tables 2021'!D17),IF(L36<='Tax Tables 2021'!D18,-L36,-'Tax Tables 2021'!D18))
L48L48=-L39*25/45
L49L49=-L38
L50L50=L90
J52J52=B93
S45,S62S45=S46
S46S46=IF(AND(S47=TRUE,S48=TRUE,S49=TRUE,S50=TRUE),TRUE,FALSE)
S47,S49S47=S36
S48S48=S39
S50S50=IF('Info Sheet'!D64="yes",FALSE,TRUE)
S51S51=IF('Info Sheet'!D54="yes",FALSE,TRUE)
L54L54=SUM(L12:L53)
J60J60=IF(S131=FALSE,B131,IF(S150=FALSE,B150,""))
C57C57=IF((L56+'Tax Tables 2021'!D13)>0,"Primary rebate","Primary rebate (limited to tax payable)")
C58C58=IF('Info Sheet'!K19>=65,(IF((SUM(L56:L57)+'Tax Tables 2021'!D14)>0,"Secondary rebate","Secondary rebate (limited to tax payable)")),"Secondary rebate")
C59C59=IF('Info Sheet'!K19>=75,(IF((SUM(L56:L58)+'Tax Tables 2021'!D15)>0,"Tertiary rebate","Tertiary rebate (limited to tax payable)")),"Tertiary rebate")
C60C60=IF(L60>(L148+L165),"Medical tax credits (limited to tax payable)","Medical tax credits")
C61C61=IF((ROUND(((L37+L39)/L54)*L56,2))>('Info Sheet'!D44+'Info Sheet'!D50),CONCATENATE("Foreign tax (limited to R",'Info Sheet'!D44+'Info Sheet'!D50,")"),CONCATENATE("Foreign tax (limited to R",-L61,")"))
L56L56='Tax Tables 2021'!H12
L57L57=IF((L56+'Tax Tables 2021'!D13)>0,'Tax Tables 2021'!D13,-L56)
L58L58=IF('Info Sheet'!K19>=65,(IF((SUM(L56:L57)+'Tax Tables 2021'!D14)>0,'Tax Tables 2021'!D14,-SUM(L56:L57))),0)
L59L59=IF('Info Sheet'!K19>=75,(IF((SUM(L56:L58)+'Tax Tables 2021'!D15)>0,'Tax Tables 2021'!D15,-SUM(L56:L58))),0)
L60L60=IF((L56+SUM(L57:L59)-(L148+L165))<=0,-(L56+SUM(L57:L59)),-(L148+L165))
L61L61=IF((ROUND(((L37+L39)/L54)*L56,2))>('Info Sheet'!D44+'Info Sheet'!D50),-('Info Sheet'!D44+'Info Sheet'!D50),-(ROUND(((L37+L39)/L54)*L56,2)))
L62L62=SUM(L56:L60)
S58S58=IF('Info Sheet'!K19<65,TRUE,FALSE)
S59S59=IF('Info Sheet'!K19<75,TRUE,FALSE)
S60S60=IF('Info Sheet'!D68="Yes",FALSE,TRUE)
S61S61=IF(OR('Info Sheet'!D42="yes",'Info Sheet'!D48="yes"),FALSE,TRUE)
S63S63=IF(OR(S64=FALSE,S65=FALSE,S66=FALSE),FALSE,TRUE)
S64S64=IF('Info Sheet'!D83="yes",FALSE,TRUE)
S65S65=IF('Info Sheet'!D85="yes",FALSE,TRUE)
S67S67=S63
L68L68=SUM(L62:L67)
B70B70='Cover page'!D12
B73B73='Cover page'!D16
L82L82=L80*0.2
L83L83=-L82
L84,L118,L113,L108,L103,L98L84=SUM(L82:L83)
L90L90=IF((IF(SUM(L88:L89)>(SUM(L12:L49)*'Tax Tables 2021'!D24),-(SUM(L12:L49)*'Tax Tables 2021'!D24),-SUM(L88:L89)))<-'Tax Tables 2021'!D25,-'Tax Tables 2021'!D25,(IF(SUM(L88:L89)>(SUM(L12:L49)*'Tax Tables 2021'!D24),-(SUM(L12:L49)*'Tax Tables 2021'!D24),-SUM(L88:L89))))
L91L91=SUM(L88:L90)
L120L120=L118+L113+L108+L103+L98
L121L121=IF(L120>40000,-40000,IF(L120<-40000,40000,-L120))
L122L122=L127
L123L123=IF(SUM(L120:L122)>0,SUM(L120:L122)*0.4,0)
L126L126=-IF(SUM(L120:L121)<0,SUM(L120:L121),0)
L127L127=IF(SUM(L120:L121)>0,(IF((SUM(L125:L126)>SUM(L120:L121)),-SUM(L120:L121),-SUM(L125:L126))),0)
L129L129=SUM(L125:L127)
S78S78=IF('Info Sheet'!D46="yes",FALSE,TRUE)
S86S86=IF('Info Sheet'!D64="yes",FALSE,TRUE)
S93S93=IF('Info Sheet'!D54="yes",FALSE,TRUE)
S95S95=IF(AND(S94=FALSE,'Info Sheet'!D56>=1),FALSE,TRUE)
S100S100=IF(AND(S94=FALSE,'Info Sheet'!D56>=2),FALSE,TRUE)
S105S105=IF(AND(S94=FALSE,'Info Sheet'!D56>=3),FALSE,TRUE)
S110S110=IF(AND(S94=FALSE,'Info Sheet'!D56>=4),FALSE,TRUE)
S115S115=IF(AND(S94=FALSE,'Info Sheet'!D56>=5),FALSE,TRUE)
S120S120=S94
S122S122=S94
Cells with Conditional Formatting
CellConditionCell FormatStop If True
L28:L33Cell Value=0textNO
L20:L25Cell Value=0textNO
L13:L17Cell Value=0textNO
L12Cell Value=0textNO
Cells with Data Validation
CellAllowCriteria
L96Whole number>=0
L97Whole number<=0
L101Whole number>=0
L102Whole number<=0
L106Whole number>=0
L107Whole number<=0
L111Whole number>=0
L112Whole number<=0
L116Whole number>=0
L117Whole number<=0
C86:F86List=$V$2:$V$4
Click to expand...
The first page should always include up to Row 71 and collum L - not all the data will be shown since as i explained some of it will be hidden per your choices on the another sheet, is there a way to keep the page break fixed on this part, and make it dynamic on another part below this data set (was to many cells to include)
 
Upvote 0
Thank you for the sample. Can you now explain how you want this Print Border to change/behave?

I am also a little confused in that your original question mentioned borders, but your last post mentioned a page break.
I am not sure if that is two separate questions, or you really mean the same thing.
 
Upvote 0
Joe4 said:
Thank you for the sample. Can you now explain how you want this Print Border to change/behave?

I am also a little confused in that your original question mentioned borders, but your last post mentioned a page break.
I am not sure if that is two separate questions, or you really mean the same thing.
Click to expand...
When i use the page break preview view, everything that will print vs that will not print is seperated by a blue border (which is also a page break) basically, i want Row 1 to 71 and Collum A to L to always be Page 1, then the other part that will be below that will be dynamic
 
Upvote 0
So then this really has nothing to do with borders, but is a page break question then?

I would put a manual page break at row 71 then.
You may have to play around with the Scaling percentage in order to get everything from form A1:L71 to print to one page.
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

Nuke_It_Newport
  • Question
Modify worksheets vs. create temporary worksheets for printing?
Replies
2
Views
292
Nuke_It_Newport
Nuke_It_Newport
Z
  • Question
Print-to-PDF is corrupted in WIN11
Replies
1
Views
111
zombiemaster
Z
J
  • Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
931
dmt32
D
H
  • Question
How to hide some columns while allowing users to 'Format Columns'
Replies
2
Views
269
hcgood
H
C
  • Question
Adding Horizontal and/or Vertical borders with VBA.
Replies
0
Views
353
cr130
C

Forum statistics

Threads
1,214,881
Messages
6,122,074
Members
449,064
Latest member
MattDRT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top