Skip to main content

Conas srian a chur le luachanna greamaigh amháin (formáidiú a chosc) in Excel?

De ghnáth, greamaíonn muid sonraí cóipeáilte gan ach na heochracha Ctrl + V a bhrú ag an am céanna. Agus greamóidh sé sin na luachanna cóipeáilte mar aon leis an bhformáidiú cille go léir. Mura gá duit ach na luachanna a ghreamú agus formáidiú na gcealla a shrianadh, is féidir leis na modhanna seo a leanas cabhrú leat.

Cuir srian le luachanna a ghreamú amháin (formáidiú a chosc) le gné luachanna greamaigh
Cuir srian le luachanna a ghreamú amháin (formáidiú a chosc) le cód VBA
Cuir srian le luachanna a ghreamú le Kutools le haghaidh Excel amháin

Cuir srian le luachanna a ghreamú amháin (formáidiú a chosc) le gné luachanna greamaigh

Bealach éasca le srian a chur ar luachanna greamaigh amháin is ea na sonraí cóipeáilte a ghreamú mar luachanna in Excel amháin.

1. Tar éis duit sonraí a chóipeáil, cliceáil ar dheis ar an gcill chinn scríbe a ghreamóidh tú na sonraí ann.

2. Ansin cliceáil ar an luachanna cnaipe faoi Greamaigh Roghanna alt sa roghchlár cliceáil ar dheis. Féach an pictiúr:

Ní fheiceann tú ach go ndéantar luachanna de chealla cóipeáilte a ghreamú isteach sna cealla cinn scríbe.

Cuir srian le luachanna a ghreamú amháin (formáidiú a chosc) le cód VBA

Cuidíonn an cód VBA seo a leanas leat ach luachanna cealla cóipeáilte a ghreamú, agus srian a chur ar gach formáidiú cille. Déan mar a leanas le do thoil.

1. Brúigh Eile + F11 eochracha ag an am céanna chun an Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

2. Sa Microsoft Visual Basic d’Fheidhmchláir fuinneog, cliceáil faoi dhó ar an An Leabhar Oibre seo sa tionscadal pána ceart chun an An Leabhar Oibre seo fuinneog cód. Ansin cóipeáil agus greamaigh an cód VBA thíos san fhuinneog.

Cód VBA: Cuir srian le luachanna a ghreamú in Excel amháin

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    Target.PasteSpecial xlPasteValues
    Application.CutCopyMode = True
End Sub

3. Brúigh an Eile + Q eochracha ag an am céanna chun an fhuinneog Microsoft Visual Basic for Applications a dhúnadh.

Anois, cóipeáil do chuid sonraí, agus téigh chuig an mbileog oibre ceann scríbe. Ní dhéanfaidh ach cliceáil amháin nó cliceáil ar dheis ar an gcill chinn scríbe luachanna na gcealla cóipeáilte a ghreamú gan aon fhormáidiú láithreach.

Cuir srian le luachanna a ghreamú le Kutools le haghaidh Excel amháin

Kutools for Excel 's Cóipeanna Ranganna cuidíonn fóntais leat ach luachanna raon roghnaithe nó ilréimsí roghnaithe a ghreamú in Excel. Déan mar a leanas le do thoil.

Roimh iarratas a dhéanamh Kutools le haghaidh Excel, Le do thoil é a íoslódáil agus a shuiteáil ar dtús.

1. Roghnaigh raon nó raonta iolracha agus an Ctrl eochair, ansin cliceáil Kutools > Cóipeanna Ranganna. 

2. Sa Cóipeáil Il-Ranganna bosca dialóige, roghnaigh ach an luachanna rogha sa Greamaigh speisialta alt, agus ansin cliceáil ar an OK cnaipe.

nótaí: Is féidir leat an dá cheann nó ceann amháin de na Lena n-áirítear airde as a chéile agus an Leithead an cholúin san áireamh roghanna de réir mar is gá duit.

3. Sa chéad cheann eile Cóipeáil Il-Ranganna bosca dialóige, roghnaigh cill bhán chun na cealla cóipeáilte a ghreamú, agus ansin cliceáil ar an OK cnaipe.

Ansin ní ghreamaítear ach luachanna na gcealla roghnaithe go léir i raon nó i raonta iolracha.

  Más mian leat triail saor in aisce (30 lá) a bheith agat ar an bhfóntas seo, cliceáil le do thoil chun é a íoslódáil, agus ansin téigh chun an oibríocht a chur i bhfeidhm de réir na gcéimeanna thuas.

Earraí gaolmhara:

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 (14)
No ratings yet. Be the first to rate!
bengiz30
bengiz30
This comment was minimized by the moderator on the site
Hello, there are a problem here, values are automatically paste when you click on the cell
Reply
0
0
Rafael
Rafael
This comment was minimized by the moderator on the site
Hi, any chance to add this feature only in some sheets?
Reply
0
0
Hei-KO
Hei-KO
This comment was minimized by the moderator on the site
Hallo,
ich habe eine Excel-Datei (100 Tabellenregister) für die ich eine Formel gesucht habe, die es erlaubt, beim Kopieren nur Werte einfügen, möglich zu machen.

Ausschneiden+Drag&Drop habe ich bereits per Makro ausgeschaltet. Ich habe jetzt im Netz folgenden Code gefunden, den ich in
"Diese Arbeitsmappe" geschrieben habe:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Target.PasteSpecial xlPasteValues
Application.CutCopyMode = True
End Sub

(Der Code verursacht, dass ich nach kopieren, den Inhalt sofort als Wert in eine angeklickte Zelle kopiert bekomme)
Das funktioniert sehr gut, jedoch habe ich noch ein Problem dabei:

Wenn ich Werte aus einer Zelle kopiere, kann ich diese auch in geschützte
Zellen kopieren und das soll nicht sein. In vielen geschützten Zellen stehen Formeln die nicht überschrieben werden sollen.

Kann mir jemand helfen, wie ich den Code erweitern oder ändern kann, dass er es nur erlaubt in nicht geschützte Zellen zu kopieren?

Gruß Heiko
Reply
0
0
syazahisham91@gmail.com
This comment was minimized by the moderator on the site
Hi! for Kutools 'Copy Ranges', I realized there is limit on number of columns that can be copy at a time. I am working with 7,500 columns. I select 7,500 columns as the range, but it can only copy and paste partial of what I had selected, not all. I am planning to copy all these columns in one go and paste. Is there any way I can do that ? just to speed things up. Any help would highly be appreciated. Thanks!
Reply
Report
0
0
Dean
Dean
This comment was minimized by the moderator on the site
Hi, is there code for working with ctrl+v. This code paste automatically when selecting cell. I found module code below link but couldn't find code for workbook like this.
https://www.extendoffice.com/documents/excel/2545-excel-set-paste-values-as-default.html
Reply
0
0
Adriana
Adriana     Dean
This comment was minimized by the moderator on the site
It's sad no one answers. I need this information too.
Reply
0
0
Travis
Travis
This comment was minimized by the moderator on the site
This code was great, but it was pasting items in the system clipboard that were copied from other programs. I put it in an 'If' statement which does nothing, unless Excel cells are actually in Cut or Copy mode. i.e. moving border around the cell(s).

Application.CutCopyMode has three modes: False = Not in Cut or Copy mode. | xlCopy = In Copy mode. | xlCut = In Cut mode.

'If in Cut or Copy mode, Paste Values Only
'If Not in Cut or Copy mode, skip and do nothing.

On Error Resume Next
If Not Application.CutCopyMode = False Then
Target.PasteSpecial xlPasteValues
Application.CutCopyMode = True
End If

This was a bit of a bugger for me to figure out. Hope it helps someone else.
-Travis (IT Professional since 1996)
Reply
0
0
Nikki
Nikki     Travis
This comment was minimized by the moderator on the site
This addition was a great help! Thank you so much for posting!
Reply
0
0
Gav
Gav     Travis
This comment was minimized by the moderator on the site
Hi Travis, Thank you very much for posting this.
I never respond to posts but i appreciate you posting this answer.
Reply
0
0
Dean
Dean     Gav
This comment was minimized by the moderator on the site
Hi Travis, is there code for working with ctrl+v. This code paste automatically when selecting cell. I found module code below link but couldn't find code for workbook like this. I hope what I have requested is possible.
https://www.extendoffice.com/documents/excel/2545-excel-set-paste-values-as-default.html
Reply
0
0
Eduardo
Eduardo     Dean
This comment was minimized by the moderator on the site
Dean, it is very useful! Thanks!
Reply
0
0
Me
Me
This comment was minimized by the moderator on the site
Thank you so much!! It works well and its so useful for the project that I am working on.
Reply
0
0
Bram Verschoren
Bram Verschoren
This comment was minimized by the moderator on the site
This works for me:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Target.PasteSpecial xlPasteValues
Application.CutCopyMode = True
End Sub
Reply
0
0
Wishmaster
Wishmaster
This comment was minimized by the moderator on the site
Would this work on Worksheet level only? I am trying to restrict this only on one worksheet (not entire workbook), but it seems there is a problem.
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