팁 : 다른 언어는 Google 번역입니다. 방문하실 수 있습니다. English 이 링크의 버전.
로그인
x
or
x
x
회원가입
x

or

Excel에서 데이터를 복사하고 붙여 넣을 수있는 명령 단추를 만드는 방법?

데이터를 변경 한 후 셀 범위를 다른 곳으로 자주 복사해야한다면 수동 복사 및 붙여 넣기 방법이 까다로워지고 시간이 오래 걸릴 것입니다. 이 복사하여 붙여 넣기 업무를 자동으로 실행하는 방법은 무엇입니까? 이 문서에서는 한 번의 클릭으로 명령 단추를 사용하여 데이터를 복사하고 붙여 넣는 방법을 보여줍니다.

VBA 코드를 사용하여 데이터를 복사하고 붙여 넣을 수있는 명령 단추 만들기

Office 탭 Office에서 탭 편집 및 찾아보기를 사용하고 훨씬 쉽게 작업 할 수 있습니다 ...
Excel 용 Kutools-최고의 Office 생산성 도구로 대부분의 Excel 문제를 해결할 수 있습니다.
  • 무엇이든 재사용하십시오 : 가장 많이 사용되거나 복잡한 수식, 차트 및 기타 항목을 즐겨 찾기에 추가하고 나중에 빠르게 재사용하십시오.
  • 20 이상의 텍스트 기능 : 텍스트 문자열에서 숫자 추출; 텍스트 일부 추출 또는 제거; 숫자와 통화를 영어 단어로 변환 ...
  • 병합 도구: 여러 통합 문서 및 시트를 하나로 통합합니다. 데이터 손실없이 여러 셀 / 행 / 열 병합 중복 행 및 합계 병합 ...
  • 분할 도구: 값을 기준으로 데이터를 여러 시트로 분할; 하나의 통합 문서에서 여러 Excel, PDF 또는 CSV 파일로; 하나의 열에서 여러 열로 ...
  • 건너 뛰기 붙여 넣기 숨겨진 / 필터링 된 행; 수와 합계 배경색 별; 메일 링리스트 생성 셀의 가치로 이메일 보내기...
  • 수퍼 필터 : 고급 필터 구성표를 작성하고 모든 시트에 적용하십시오. 종류 주별, 일별, 빈도 등으로; FILTER 굵게, 수식, 주석으로 ...
  • 300 이상의 강력한 기능; Office 2007-2019 및 365와 호환됩니다. 모든 언어를 지원합니다. 회사에서 쉽게 배포; 60 일 무료 평가판 전체 기능.

VBA 코드를 사용하여 데이터를 복사하고 붙여 넣을 수있는 명령 단추 만들기


커맨드 버튼을 클릭하면 데이터를 복사하여 붙여 넣기하려면 다음과 같이하십시오.

1. 클릭하여 명령 단추 삽입 개발자 > 끼워 넣다 > 명령 단추 (ActiveX 컨트롤). 스크린 샷보기 :

2. 워크 시트에 명령 단추를 그리고 마우스 오른쪽 단추로 클릭하십시오. 고르다 코드보기 컨텍스트 메뉴에서.

3. 갑자기 나타나기 시작했다. 응용 프로그램 용 Microsoft Visual Basic 창에서 VBA 코드 아래의 코드 창에서 원래 코드를 바꾸십시오.

VBA 코드 : 명령 단추를 사용하여 Excel에서 데이터를 복사하고 붙여 넣습니다.

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

주의 사항코드에서 CommandButton1은 삽입 된 명령 단추의 이름입니다. A1 : C17은 복사해야하는 범위이고 J1 : L17은 데이터를 붙여 넣을 대상 범위입니다. 필요에 따라 변경하십시오.

4. 프레스 다른 + Q 열쇠를 닫으십시오. 응용 프로그램 용 Microsoft Visual Basic 창문. 그리고 개발자 탭에서 디자인 모드를 해제하십시오.

5. 이제 명령 단추를 클릭하여 범위 내의 모든 데이터를 표시합니다. A1 : C17은 셀 서식없이 J1 : L17 범위에 복사되고 붙여 넣어집니다.


관련 기사:


Excel 용 Kutools-최고의 사무 생산성 도구 80 % 생산성 향상

  • 재사용: 빠르게 삽입 복잡한 수식, 차트 그리고 당신이 전에 사용했던 것; 셀 암호화 비밀번호로; 메일 링리스트 만들기 그리고 이메일을 보내 ...
  • 슈퍼 포뮬러 바 (여러 줄의 텍스트와 수식을 쉽게 편집); 레이아웃 읽기 (많은 셀을 쉽게 읽고 편집); 필터링 된 범위에 붙여 넣기...
  • 셀 / 행 / 열 병합 데이터 손실없이; 분할 셀 내용; 중복 행 / 열 결합... 중복 세포 방지; 범위 비교...
  • 복제 또는 고유를 선택하십시오. 행; 빈 행 선택 (모든 세포는 비어있다); 슈퍼 찾기 및 퍼지 찾기 많은 통합 문서에서; 랜덤 선택 ...
  • 정확한 사본 공식 참조를 변경하지 않는 다중 셀; 참조 자동 작성 여러 장에; 글 머리 기호 삽입, 확인란 등 ...
  • 텍스트 추출, 텍스트 추가, 위치 별 제거, 공간 제거; 페이징 소계 생성 및 인쇄; 셀 내용과 주석 간 변환...
  • 수퍼 필터 (필터 구성표를 저장하고 다른 시트에 적용); 고급 정렬 월 / 주 / 일별, 빈도 등; 특수 필터 대담하고 기울임 꼴로
  • 통합 문서와 WorkSheets 결합; 키 열을 기준으로 테이블 병합 데이터를 여러 시트로 분할; 일괄 변환 xls, xlsx 및 PDF...
  • 300 이상의 강력한 기능. Office / Excel 2007-2019 및 365를 지원합니다. 모든 언어를 지원합니다. 기업이나 조직에 쉽게 배포 할 수 있습니다. 60 일 무료 평가판 전체 기능.
kte 탭 201905

Office 탭 Office에 탭 인터페이스를 제공하여 작업을 훨씬 쉽게

  • Word, Excel, PowerPoint에서 탭 편집 및 읽기 사용, 게시자, 액세스, Visio 및 프로젝트.
  • 새 창보다는 동일한 창에서 새 탭으로 여러 문서를 열고 만들 수 있습니다.
  • 50 %만큼 생산성을 높이고 매일 수백 번의 마우스 클릭을 줄입니다!
오피셜
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.
    Jonas · 6 days ago
    Hello!
    Is there a way to make a code so i can have a button copy a range (lets say A1:D5).
    And then paste it to A6:D10.

    And if i press the button once more it will copy A1:D5 to A11:D15.

    And so on?
  • To post as a guest, your comment is unpublished.
    Lexi · 20 days ago
    Is it possible to make a button that just copies one selected cell on one sheet to another sheet? Thanks!
    • To post as a guest, your comment is unpublished.
      crystal · 12 days ago
      Hi lexi,
      The below VBA code can help you solve the problem. Please change "Sheet3" to the sheet name as you need.
      Select a cell and press the command button to make it work.

      Private Sub CommandButton1_Click()
      Dim xSWName As String
      Dim xSheet As Worksheet
      Dim xPSheet As Worksheet
      Dim xIntR As Integer
      xSWName = "Sheet3"
      On Error Resume Next
      Application.ScreenUpdating = False
      Set xSheet = ActiveSheet
      If xSheet.Name <> "Definitions" And xSheet.Name <> "fx" And xSheet.Name <> "Needs" Then
      Selection.CurrentRegion.Select
      Selection.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.
    S J · 1 months ago
    please send VBAcode for copy single cell content using commmand button click without using text box so tha will be able to paste in any application like notepad,msword
  • To post as a guest, your comment is unpublished.
    angela · 3 months ago
    master bagaimana jika berbeda sheet dan copy data yg diinginkan
    contoh sheet1 hanya data kolom B & kolom D copy ke sheet2
    terimakasih
  • To post as a guest, your comment is unpublished.
    Ted Jillett · 4 months ago
    I'm looking for some help on that a user can click on a row number and then hit the command button that it will make a copy of that row and insert it into the row below it.
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Good day,
      Sorry can help you with that yet. Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Yesaya · 5 months ago
    hi, help my case, i want have a button to generate macro where i have excel and export the data where in the <" "> to word, thanks
    • To post as a guest, your comment is unpublished.
      crystal · 4 months ago
      Hi,
      Sorry I didn't get your point. Would be nice if you could explain with more details of what you are trying to do.
  • To post as a guest, your comment is unpublished.
    Kyaw Ye Min · 5 months ago
    Hi Author,

    The article you mentioned above is very helpful to me. I am not used to excel code and command. I still need to know rather than this article. I am ok with the copy and paste into next sheet. But I still need to know "how to paste and add into new line in next sheet every time I press the button". Otherwise, my data on next sheet will be replaced every time. I would really be grateful and looking forward to see your reply.
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Hi Kyaw Ye Min,
      Sorry for replying so late. Follow the steps and replace the code with the below. In the code, Sheet4 is the destination worksheet you will copy data into, please change it and the copied range A1:C17 as you need. Thanks for your comment.

      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.
        Chris · 1 months ago
        Hi there please help. I require the same as above but i just need to paste whatever the active cell is into a new line (if possible above previous entries) in a different worksheet. Really appreciate any help with this. Best Regards
  • To post as a guest, your comment is unpublished.
    Jim · 6 months ago
    I have a qusrion I have sheet 1 with a column of date b3:b33 and I want that to be copied to sheet 2 B33:b63 its text but I need the same format for text ie colour size
  • To post as a guest, your comment is unpublished.
    Robbert · 6 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 · 6 months ago
      Hi Robbert,
      Sorry can't help you with that. Thanks for commenting.
  • To post as a guest, your comment is unpublished.
    MangkaNorr · 8 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 · 7 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 · 10 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 · 7 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 · 7 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 · 10 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 · 9 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 · 11 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 · 10 months ago
      Hi Magnus,
      Sorry can't help you with that. Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Azam as · 1 years ago
    how is the formula for us to copy files
    then paste it to the next sheet. and paste the next one make a space or keep going down without deleting the previous paste.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good day,
      The below VBA code can help you solve the problem. Please have a try. Thank you.

      Private Sub CommandButton1_Click()
      Dim xSheet, xDWS As Worksheet
      Dim xFNum As Integer
      Dim xSRg As Range

      On Error Resume Next
      Set xSRg = Application.InputBox("Please select cell to paste the range:", "Kutools for Excel", xTxt, , , , , 8)
      If xSRg Is Nothing Then Exit Sub

      Application.ScreenUpdating = False
      Range("A1:C17 ").Copy
      xSRg.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Application.ScreenUpdating = True

      End Sub
  • To post as a guest, your comment is unpublished.
    Jason · 1 years ago
    How can you alter the code to paste the results to another page with in the workbook? and how do you get the insert a row before putting the new data in so it does not overwrite it
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Jason,
      If you want to paste the results to another worksheets within the workbook, please try the below VBA code.

      Private Sub CommandButton1_Click()
      Dim xSheet, xDWS As Worksheet
      Dim xFNum As Integer
      Dim xSRg As Range

      On Error Resume Next
      Set xSRg = Application.InputBox("Please select cell to paste the range:", "Kutools for Excel", xTxt, , , , , 8)
      If xSRg Is Nothing Then Exit Sub

      Application.ScreenUpdating = False
      Range("A1:C17 ").Copy
      xSRg.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Application.ScreenUpdating = True

      End Sub
  • To post as a guest, your comment is unpublished.
    Dom · 2 years ago
    I have used this function in my workbook. I am using active x buttons to copy and paste data within the same sheet on multiple sheets. However there are sheets without active x buttons or macros that get pasted data from the respective fields on the sheet which I don't want. Help?