Namig: drugi jeziki so prevedeni v Google. Lahko obiščete English različico te povezave.
Vpiši se
x
or
x
x
Registracija
x

or

Kako ustvariti gumb za urejanje za kopiranje in lepljenje podatkov v programu Excel?

Če menite, da morate po spreminjanju podatkov pogosto kopirati vrsto celic na drug kraj, bo ročna kopiranje in prilepitev metoda pretirana in dolgotrajna. Kako narediti to kopiranje in lepljenje afero teče samodejno? Ta članek vam bo pokazal, kako uporabiti ukazni gumb za kopiranje in lepljenje podatkov z enim samim klikom.

Ustvarite gumb za urejanje in kopirajte in prilepite podatke s kodo VBA


Ustvarite gumb za urejanje in kopirajte in prilepite podatke s kodo VBA


Ko kliknete gumb za ukaz, naredite naslednje, če želite samodejno kopirati in prilepiti podatke.

1. Vstavite gumb za ukaz s klikom Razvojni > Vstavi > Gumb za urejanje (nadzor ActiveX). Prikaz slike:

2. Nariši gumb na svojem delovnem listu in ga kliknite z desnim gumbom. Izberite Ogled kode iz kontekstnega menija.

3. Pri pojavu Microsoft Visual Basic za aplikacije okno, zamenjajte izvorno kodo v oknu Code z spodnjo kodo VBA.

VBA koda: Uporabite gumb za urejanje, če želite kopirati in prilepiti podatke v Excelu

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim xSheet As Worksheet
    Set xSheet = ActiveSheet
        If xSheet.Name <> "Definitions" And xSheet.Name <> "fx" And xSheet.Name <> "Needs" Then
            xSheet.Range("A1:C17 ").Copy
            xSheet.Range("J1:L17").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End If

    Application.ScreenUpdating = True
End Sub

Opombe: V kodi je CommandButton1 ime vašega vstavljenega ukaznega gumba. A1: C17 je obseg, ki ga želite kopirati, in J1: L17 je ciljno območje za prilepitev podatkov. Prosimo, spremenite jih po potrebi.

4. Pritisnite druga + Q tipke za zapiranje Microsoft Visual Basic za aplikacije okno. Izklopite načrtni način na kartici Razvijalec.

5. Zdaj kliknite gumb za ukaz, vsi podatki v območju A1: C17 bo kopiran in prilepen v obseg J1: L17 brez oblikovanja celic.


Sorodni članki:


Priporočena orodja za produktivnost za Excel

zavihek kte 201905

Kutools za Excel vam pomaga, da vedno končate delo pred časom in izstopite iz množice

  • Več kot zmogljive napredne funkcije 300, zasnovane za 1500 delovne scenarije, ki povečujejo produktivnost z 70%, vam dajejo več časa za skrb za družino in uživanje v življenju.
  • Ne potrebujete več pomnilniških formul in VBA kod, od zdaj naprej pa dajate svojim možganom počitek.
  • Postanite strokovnjak za Excel v minutah 3, zapletene in ponavljajoče se operacije lahko opravite v nekaj sekundah,
  • Vsak dan zmanjšajte število operacij tipkovnice in miške, zdaj se poslovite od poklicnih bolezni.
  • 110,000 visoko učinkovite ljudi in 300 + svetovno priznanih podjetij izbiro.
  • Brezplačna preizkusna različica 60 dneva. 60-dnevno jamstvo vračila denarja. 2 let brezplačne nadgradnje in podpore.

Prinaša kartično brskanje in urejanje za Microsoft Office, veliko močnejši od zavihkov brskalnika

  • Office Tab je namenjen za Word, Excel, PowerPoint in druge Office aplikacije: Založnik, Dostop, Visio in Projekt.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Z 50% poveča vašo produktivnost in vsak dan zmanjša na stotine klikov z miško!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Robbert · 3 months ago
    If I want to Copy this not in the current workbook but into another workbook (which is still not opened) in excel. How do I change this VBA code accordingly?
    • To post as a guest, your comment is unpublished.
      crystal · 3 months ago
      Hi Robbert,
      Sorry can't help you with that. Thanks for commenting.
  • To post as a guest, your comment is unpublished.
    MangkaNorr · 5 months ago
    sir how to copy the range of the cell in current worksheet then paste to another worksheets ?
    • To post as a guest, your comment is unpublished.
      crystal · 4 months ago
      Hi,
      The below VBA code can help you solve the problem.
      You need to replace the "Sheet4" and "A1:C17" in the code with your specified sheet and range.

      Private Sub CommandButton1_Click()
      Dim xSWName As String
      Dim xSheet As Worksheet
      Dim xPSheet As Worksheet
      Dim xIntR As Integer
      xSWName = "Sheet4"
      On Error Resume Next
      Application.ScreenUpdating = False
      Set xSheet = ActiveSheet
      If xSheet.Name <> "Definitions" And xSheet.Name <> "fx" And xSheet.Name <> "Needs" Then
      xSheet.Range("A1:C17 ").Copy
      Set xPSheet = Worksheets.Item(xSWName)
      xIntR = xPSheet.UsedRange.Rows.count
      xPSheet.Cells(xIntR + 1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      End If
      Application.ScreenUpdating = True
      End Sub
  • To post as a guest, your comment is unpublished.
    Adam · 7 months ago
    Would it be possible to for the command button to instantly paste the selected range to a specified sheet (in the next empty cell) rather than having to input each time where you want to paste the data?
    • To post as a guest, your comment is unpublished.
      crystal · 4 months ago
      Hi Adam,
      The below VBA code can help you solve the problem.
      You need to replace the "Sheet4" and "A1:C17" in the code with your specified sheet and range.

      Private Sub CommandButton1_Click()
      Dim xSWName As String
      Dim xSheet As Worksheet
      Dim xPSheet As Worksheet
      Dim xIntR As Integer
      xSWName = "Sheet4"
      On Error Resume Next
      Application.ScreenUpdating = False
      Set xSheet = ActiveSheet
      If xSheet.Name <> "Definitions" And xSheet.Name <> "fx" And xSheet.Name <> "Needs" Then
      xSheet.Range("A1:C17 ").Copy
      Set xPSheet = Worksheets.Item(xSWName)
      xIntR = xPSheet.UsedRange.Rows.count
      xPSheet.Cells(xIntR + 1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      End If
      Application.ScreenUpdating = True
      End Sub
      • To post as a guest, your comment is unpublished.
        Mohamed shoman · 4 months ago
        very good , i need to copy and paste many time is it possible to make a choice to paste a row of data to 3 rows or 5 rows whatever i like ?
  • To post as a guest, your comment is unpublished.
    Jeff McCollum · 7 months ago
    I would like to expand this one step... After pasting the actual values not formulas... I need to copy the new cell and paste in another program that's not excel, should simply I need to click the button copy the formula from a specific cell and past the actual value in another, then copy that new value to windows clipboard to past in another app. Hope this make sense, and appreciate your help in knowing how to add the new code... I know what I want it to do, just not familiar with how to code it...
    Thanks
    • To post as a guest, your comment is unpublished.
      crystal · 6 months ago
      Hi,
      The code has been updated in the article, please have a try. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Magnus · 8 months ago
    What if I would like to make a paste queue?
    To explain further. I click CommandButton1 then it copy A1. When I have used paste, the copy from A1 - Then I want to copy A2 without clicking another button, so that I can paste A2 right away, somewhere else. Then When I have used paste, the copy from A2 then copy A3. If that is possible? Can this also work in the background when use a normal Excel sheet, and try to do this action where I copy from Excel, and then paste in a completely different program like a internet browser, another program, word, txt file, and so on?

    This article was anyway extremely helpful, thank you so much!
    • To post as a guest, your comment is unpublished.
      crystal · 7 months ago
      Hi Magnus,
      Sorry can't help you with that. Thanks for your comment.