Skip to main content

Conas luach cille a chur i gceanntásc / buntásc in Excel?

Is féidir linn ceanntásc nó buntásc a chur isteach go héasca ina bhfuil cosán comhaid, ainm comhaid, dáta reatha nó faisnéis eile le gné Excel, ach, uaireanta, ba mhaith linn luach cille a úsáid mar an ceanntásc nó an buntásc in Excel. Conas is féidir linn ábhar cille a chur i gceanntásc nó buntásc sa leabhar oibre?

Cuir luach cille i gceanntásc nó buntásc bileog oibre le cód VBA

Cuir luach cille i gceanntásc nó buntásc gach bileog oibre le cód VBA

Cuir faisnéis chomhaid isteach sa cheanntásc / buntásc le Kutools for Excel smaoineamh maith3

Más mian leat ábhar cille a chur i gceanntásc nó buntásc na bileoige oibre reatha, is féidir leis an gcód VBA seo a leanas cabhrú leat. Déan na céimeanna seo a leanas le do thoil:

1. Gníomhach do bhileog oibre ar mhaith leat tréada nó buntásc a chur isteach le luach cille, ansin coinnigh síos an ALT + F11 eochracha, agus osclaíonn sé an Fuinneog Microsoft Visual Basic for Applications.

2. Cliceáil Ionsáigh > Modúil, agus greamaigh an cód seo a leanas i bhFuinneog an Mhodúil.

Cód VBA: cuir luach cille sonraithe i gceanntásc bileog oibre

Sub HeaderFrom()
'Update 20140318
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection.Range("A1")
Set WorkRng = Application.InputBox("Range (single cell)", xTitleId, WorkRng.Address, Type:=8)
Application.ActiveSheet.PageSetup.LeftHeader = WorkRng.Range("A1").Value
End Sub

3. Ansin brúigh F5 eochair chun an cód seo a rith, popfaidh dialóg amach le cur i gcuimhne duit cill a roghnú a theastaíonn uait a ábhar a chur sa cheanntásc.doc-insert-cill-luach-go-ceanntásc1

4. Agus ansin cliceáil OK, tá an luach cille sonraithe seo curtha isteach i gceanntásc clé reatha na bileoige oibre. Is féidir leat an ceanntásc a fheiceáil trí chliceáil Comhad > Print. Féach an pictiúr:doc-insert-cill-luach-go-ceanntásc1

Nótaí:

1. Más mian leat an t-ábhar cille a úsáid mar bhuntásc den bhileog oibre ghníomhach, is féidir leat an cód seo a chur i bhfeidhm:

Cód VBA: cuir luach cille sonraithe i buntásc bileog oibre

Sub FooterFrom()
'Update 20140318
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection.Range("A1")
Set WorkRng = Application.InputBox("Range (single cell)", xTitleId, WorkRng.Address, Type:=8)
Application.ActiveSheet.PageSetup.LeftFooter = WorkRng.Range("A1").Value
End Sub

2. Is féidir leat na cóid thuas a chur i bhfeidhm freisin chun luach cille a chur isteach sa cheanntásc / buntásc ceart nó ceanntásc / buntásc an ionaid, ach an ceann a athsholáthar LeftHeader / FootFooter Clé le CeartHeader / Footfooter ar dheis or LárionadHeader / CenterFooter sna cóid.

Uaireanta, ba mhaith leat ceanntásc nó buntásc a bhfuil ábhar cille roghnaithe ann a chur isteach i ngach bileog oibre de do leabhar oibre tosaigh, leis an gcód thuas, ní mór duit iad a athdhéanamh arís agus arís eile. Sa chás seo, is féidir leis an gcód VBA seo a leanas an t-ábhar cille a chur le tréada nó buntásc an leabhair oibre iomláin ag an am céanna.

1. Gníomhaigh do leabhar oibre ar mhaith leat tréada nó buntásc a chur isteach le luach cille, ansin coinnigh síos an ALT + F11 eochracha, agus osclaíonn sé an Fuinneog Microsoft Visual Basic for Applications.

2. Cliceáil Ionsáigh > Modúil, agus greamaigh an cód seo a leanas sa Fuinneog an Mhodúil.

Cód VBA: cuir luach cille sonraithe i buntásc gach bileog oibre

Sub AddFooterToAll()
'Update 20140318
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection.Range("A1")
Set WorkRng = Application.InputBox("Range (single cell)", xTitleId, WorkRng.Address, Type:=8)
For Each ws In Application.ActiveWorkbook.Worksheets
    ws.PageSetup.LeftFooter = WorkRng.Range("A1").Value
Next
End Sub

3. Ansin brúigh F5 eochair chun an cód seo a fhorghníomhú, popfaidh dialóg amach le cur i gcuimhne duit cill a roghnú a theastaíonn uait a ábhar a chur i buntásc an leabhair oibre iomláin.

doc-insert-cill-luach-go-ceanntásc1

4. Agus ansin cliceáil OK, cuireadh an luach cille roghnaithe seo le buntásc clé na mbileog oibre go léir. Is féidir leat an buntásc a fheiceáil trí chliceáil Comhad > Print. Féach an pictiúr:

doc-insert-cill-luach-go-ceanntásc1

Nótaí:

1. Más mian leat an t-ábhar cille a úsáid mar cheanntásc ar an leabhar oibre iomlán, is féidir leat an cód seo a chur i bhfeidhm:

Cód VBA: cuir luach cille sonraithe i gceanntásc na mbileog oibre go léir

Sub AddHeaderToAll()
'Update 20140318
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection.Range("A1")
Set WorkRng = Application.InputBox("Range (single cell)", xTitleId, WorkRng.Address, Type:=8)
For Each ws In Application.ActiveWorkbook.Worksheets
    ws.PageSetup.LeftHeader = WorkRng.Range("A1").Value
Next
End Sub

2. Ionadach RightHeader / Buntásc or CenterHeader / Buntásc do LeftHeader / Buntásc sna cóid thuas más mian leat do cheanntásc nó buntásc a bheith suite ar bhealach difriúil.

Más mian leat faisnéis maidir le comhdaitheoir a chur isteach sa cheanntásc nó sa bhuntásc, mar shampla bileog oibre / ainm an leabhair oibre, cosán an leabhair oibre agus mar sin de, is féidir leat í a úsáid Kutools le haghaidh Excel'S Cuir isteach Eolas faoin Leabhar Oibre fóntais má tá Kutools suiteáilte agat le haghaidh Excel.

Kutools le haghaidh Excel, le níos mó ná 300 feidhmeanna úsáideacha, déanann sé do phoist níos éasca. 
Free Download
saor in aisce lán-feiceáil i 30 lá

Tar éis suiteáil saor in aisce Kutools for Excel, déan mar atá thíos le do thoil:

1. cliceáil Kutools Plus > Leabhar Oibre > Cuir isteach Eolas faoin Leabhar Oibre. Féach an pictiúr:
duga kte 1

2. Sa Cuir isteach Eolas faoin Leabhar Oibre dialóg, seiceáil an fhaisnéis a chaithfidh tú a chur isteach faoi Eolas alt, agus seiceáil ceanntásc or Buntásc mar is maith leat.

Leid: is féidir leat an fhaisnéis a chur isteach ag trí shuíomh ceanntásc nó buntásc: lár, clé nó deas.
cuir isteach cosán leabhar oibre 1

3. cliceáil Ok. Ansin is féidir leat dul chuig Amharc > Leagan Amach Page chun an ceanntásc a fheiceáil.
duga kte 3

Le Faisnéis Ionsáigh Leabhar Oibre, is féidir leat faisnéis chomhaid a chur isteach i gcill nó i raon cealla. Cliceáil anseo chun níos mó eolais a fháil faoin bhfóntas seo.



Leid.Más mian leat leabhar oibre a roinnt go tapa i iliomad leabhar oibre / comhad pdf nó comhaid csv ar leithligh, déan iarracht na Kutools a úsáid le haghaidh Excel's Leabhar Oibre Scoilt mar a thaispeántar sa phictiúr a leanas. Tá sé ag feidhmiú go hiomlán gan teorainn i 30 lá, íoslódáil agus triail saor in aisce anois.

leabhar oibre scoilte doc

Earraí gaolmhara:

Conas ainm nó cosán an chomhaid a chur isteach i gcill / ceanntásc nó buntásc in Excel?

Conas ceanntásc, buntásc, agus pictiúr ceanntásc a chur isteach in Excel?

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup  |   VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle   |  Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla   |  Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí:  12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (9)
No ratings yet. Be the first to rate!
Neil
Neil
This comment was minimized by the moderator on the site
This doesn't appear to make a dynamic link, i.e., the value entered into the header doesn't change when the value of the cell does. So what is the purpose of the VBA code, when a copy/paste will do the same?
Reply
0
0
skyyang
skyyang     Neil
This comment was minimized by the moderator on the site
Hello, Neil,
If you want to link the cell value to the header or footer dynamically, please apply the following VBA code:

Note: You should insert the code into the sheet code not the normal Module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim WorkRng As Range
Dim xStR As String
On Error Resume Next
xStR = "A1" '
Set WorkRng = Intersect(Application.ActiveSheet.Range("A1"), Target)
If WorkRng Is Nothing Then Exit Sub
Application.ActiveSheet.PageSetup.RightHeader = WorkRng.Range("A1").Value
End Sub


Please have a try, hope it can help you
Reply
Report
0
0
DJ
DJ     skyyang
This comment was minimized by the moderator on the site
maksudnya bagaimana?
Reply
0
0
Dani
Dani
This comment was minimized by the moderator on the site
Hi,

This appears to only work once.
What if the value of the cell changes?
Is there a way to link the cell so the header changes when the cell value changes?

I have a workbook where I have three sheets.

Sheet 1 labeled "Deletion Sheet" - Sheet we send to the warehouse with info what to palatalize for the order
Sheet 2 labeled "OA" - My order acknowledgement to the customer which pulls most of the data from the first sheet including the Order number which I need to have in my header. So I am trying to link the Header to the cell in this page with the order number (F5) which gets it's value from (C7) in the first work sheet ("Deletion Sheet")
Sheet 3 labeled "Invoice - Invoice which also pulls most of the same information from the first sheet which would also need the header to include the value (F5) of this sheet taken from (C7) in "Deletion Sheet"
Reply
0
0
Mickey
Mickey
This comment was minimized by the moderator on the site
When using your "VBA code: put a specified cell value in header of all worksheets", I would like the value placed to be formatted: Tahoma, bold, in font size 12.
How can this be added to your code?
Reply
0
0
Claire
Claire
This comment was minimized by the moderator on the site
Hi There, Is there a way that you can add a cell value which takes account of applied filters? For example... A1 = Monday A2 = Tuesday A3 = Wednesday. Using the VBA code to display cell A1 will work initially, but once I apply a filter on days of the week, the "top" cell value is no longer A1. Is there a way to pick up the variable? Many thanks,
Reply
0
0
saeid mohammadi
saeid mohammadi
This comment was minimized by the moderator on the site
I want to insert four cells in header.This VB is only for one cell. How can I do it. Thanks
Reply
0
0
Ridhwan
Ridhwan     saeid mohammadi
This comment was minimized by the moderator on the site
You can try Concatenating the value of rht four cells into a single cell and then use the single cell as the header.
Reply
0
0
Jeffrey Hulbert
Jeffrey Hulbert
This comment was minimized by the moderator on the site
I needed to insert an active payroll date range into multiple sheets. The user opens the Payroll Date sheet, enters the date range and before she prints it updates all the sheets in the workbook. Here is how I am able to insert multiple cells into the range with a bit less code 2010 compatible: Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim WorkRng As Range On Error Resume Next For Each ws In Application.ActiveWorkbook.Worksheets ws.PageSetup.RightHeader = Range("'Payroll Date'!A1").Value & vbCr & Range("'Payroll Date'!A2").Value Next End Sub
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations