VBA to: On click of Command Button - copy range, paste range, enter 1 in last cell of range, repeat this but increase value by 1 each time

[BlondieC]

Hi and thank you for taking a look at this.

• This tool allows each site to customize a site specific hardware configuration.
• The worksheet also contains text and functions as an order form.
• The button to action the copy/paste will be clicked multiple times by various users depending on the hardware configuration requirements.
• The range is a range of cells (some are merged), outlined to resemble hardware that will be installed on site.
• The range copied will always be the same.
• The range pasted will be offset by one to the right each time.
• The code works to copy/paste, and insert the value 1 in the bottom right cell of the range. What I cannot figure out (tried a loop), is how to get the 2nd copy/paste to insert the value 2 in the bottom right cell of that pasted range and so on.
• I also think the line the section of code eliminating worksheet names can or should be changed to just naming the specific worksheet where the command button is and where the copy/paste of the range will take place which is the Schematic worksheet. But I'm not sure.

Private Sub CommandButton1_Click()
       
    Application.ScreenUpdating = False
    
    Dim xSheet As Worksheet
    Set xSheet = ActiveSheet
         If xSheet.Name <> "Order Form" And xSheet.Name <> "Information" And xSheet.Name <> "New Sku" Then
            xSheet.Range("C15:D27 ").Copy
            Sheets("Schematic").Range("ZZ31").End(xlToLeft).Offset(, 2).PasteSpecial xlAll
        End If
    Application.CutCopyMode = False
    
    Range("D43").Value = Range("D43").Value + 1
       
    Application.ScreenUpdating = True
      
    Range("A15").Select

End Sub

 

[BlondieC]

Hi and thank you for taking a look at this.

• This tool allows each site to customize a site specific hardware configuration.
• The worksheet also contains text and functions as an order form.
• The button to action the copy/paste will be clicked multiple times by various users depending on the hardware configuration requirements.
• The range is a range of cells (some are merged), outlined to resemble hardware that will be installed on site.
• The range copied will always be the same.
• The range pasted will be offset by one to the right each time.
• The code works to copy/paste, and insert the value 1 in the bottom right cell of the range. What I cannot figure out (tried a loop), is how to get the 2nd copy/paste to insert the value 2 in the bottom right cell of that pasted range and so on.
• I also think the line the section of code eliminating worksheet names can or should be changed to just naming the specific worksheet where the command button is and where the copy/paste of the range will take place which is the Schematic worksheet. But I'm not sure.

Private Sub CommandButton1_Click()
      
    Application.ScreenUpdating = False
   
    Dim xSheet As Worksheet
    Set xSheet = ActiveSheet
         If xSheet.Name <> "Order Form" And xSheet.Name <> "Information" And xSheet.Name <> "New Sku" Then
            xSheet.Range("C15:D27 ").Copy
            Sheets("Schematic").Range("ZZ31").End(xlToLeft).Offset(, 2).PasteSpecial xlAll
        End If
    Application.CutCopyMode = False
   
    Range("D43").Value = Range("D43").Value + 1
      
    Application.ScreenUpdating = True
     
    Range("A15").Select

End Sub

I found a solution. I just had to keep looking at it. ?