Copy and Paste in specific sheet only

[Frank Bacchus]

I am using the following code to copy and paste the content from a ROW to another ROW, using a command button. This code seems to go thru all sheets in the workbook, which I do not want to do. I want this to operate on a specific sheet.

So, only a specific sheet should be affected by this code. How can I ‘tell’ the code only to do the copy macro on sheet xxxxx? Thanks.

Here is the code:


VBA code: Use Command Button to copy and paste data in Excel

1

2

3

4

5

6

7

8

9

10

11

PrivateSubCommandButton1_Click()
    Application.ScreenUpdating = False
    Dim xSheet As Worksheet
    For Each xSheet InThisWorkbook.Worksheets
        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
        EndIf
    Next
    Application.ScreenUpdating = True
EndSub

< use code tags >

 

[Logit]

Option Explicit

Sub CommandButton1_Click()


    Application.ScreenUpdating = False
     
    With ActiveSheet
        If .Name <> "Definitions" And .Name <> "fx" And .Name <> "Needs" Then
            .Range("A1:C17 ").Copy
            .Range("J1:L17").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            .Range("A1").Select
        End If
    End With
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
  
End Sub

 

[Frank Bacchus]

Thanks, Logit. Sorry for the lengthy delay. I was out of the country. Can you explain the two sets of coding and how do I incorporate this into my spreadsheet? Would be so much appreciative. Thanks.

frank

 

[Logit]

Option Explicit

Sub CommandButton1_Click()  '<-- This is the name of the CommandButton on the sheet.
                            'If you intend to utilize this macro on more than one sheet
                            'it would be best to name this macro differently. Say "Sub CopyPasteRow()"
                            'That will eliminate any errors related to the CommandButton Numbers.


    Application.ScreenUpdating = False  '<-- Turns off viewing any changes to the existing data until
                                        'all changes have been made. Then you would use the line below
                                        ' "Application.ScreenUpdating = True" to see the changes
   
    With ActiveSheet        '<-- This macro code that follows will pertain to any worksheet you are presently
                            'viewing.
                           
        If .Name <> "Definitions" And .Name <> "fx" And .Name <> "Needs" Then  '<-- Excludes the sheets named here.
            .Range("A1:C17 ").Copy  '<-- Copies the cells from A1 to C17
           
            ' The following line will paste only the values seen in the cells listed above. It will not paste
            'any formulas or other unseen items within those cells that were copied.
            .Range("J1:L17").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
           
            .Range("A1").Select '<-- All the work is done, now I'm going to place focus on cell A1 of the worksheet.
        End If
    End With
   
    Application.ScreenUpdating = True  'Show all the changes that were made
   
    Application.CutCopyMode = False
 
End Sub

TO utilize this macro, open the VB Editor window; from the menu bar select "INSERT" / "MODULE". Then copy and paste the macro code above into the large window on the right side.

Go to the worksheet you intend to use this macro on, click on the DEVELOPER
tab / INSERT / BUTTON. Paste the button on the worksheet. Another small
window opens, select the name of the macro from the list, click OK.

 

[Frank Bacchus]

Option Explicit

Sub CommandButton1_Click()  '<-- This is the name of the CommandButton on the sheet.
                            'If you intend to utilize this macro on more than one sheet
                            'it would be best to name this macro differently. Say "Sub CopyPasteRow()"
                            'That will eliminate any errors related to the CommandButton Numbers.


    Application.ScreenUpdating = False  '<-- Turns off viewing any changes to the existing data until
                                        'all changes have been made. Then you would use the line below
                                        ' "Application.ScreenUpdating = True" to see the changes
  
    With ActiveSheet        '<-- This macro code that follows will pertain to any worksheet you are presently
                            'viewing.
                          
        If .Name <> "Definitions" And .Name <> "fx" And .Name <> "Needs" Then  '<-- Excludes the sheets named here.
            .Range("A1:C17 ").Copy  '<-- Copies the cells from A1 to C17
          
            ' The following line will paste only the values seen in the cells listed above. It will not paste
            'any formulas or other unseen items within those cells that were copied.
            .Range("J1:L17").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
          
            .Range("A1").Select '<-- All the work is done, now I'm going to place focus on cell A1 of the worksheet.
        End If
    End With
  
    Application.ScreenUpdating = True  'Show all the changes that were made
  
    Application.CutCopyMode = False

End Sub

TO utilize this macro, open the VB Editor window; from the menu bar select "INSERT" / "MODULE". Then copy and paste the macro code above into the large window on the right side.

Go to the worksheet you intend to use this macro on, click on the DEVELOPER
tab / INSERT / BUTTON. Paste the button on the worksheet. Another small
window opens, select the name of the macro from the list, click OK.

Great Logit! Thanks. Appreciate it.

 

[Logit]

You are welcome