VBA copy and paste between worksheets with a variable

Hi,

i've tried to put a code together below but ive got quite confused so hoping someone can correct it or write a new one that is correct.

Basically i want to copy data from one worksheet to another based on a variable.

The data i want to copy is in worksheet "Labour Payments" ranging from column E6 to E57 (however this will eventually extend past E57, hence me trying to count the last cell).

the variable data column is in worksheet "Labour Payments" alongside the data above ranging from column D6 to D57 (however this will eventually extend as stated above)

the defined variable for the VBA copy and paste data required will be set in worksheet "CIS Return" cell D2 (below i just set as "15" as temporary whilst trying to work out the code)

the location to paste is in worksheet "CIS Return" column B10 onwards.

Any help would be much appreciated.

Kind Regards

Danny

Below is the incorrect code i was attempting to get working:

PHP Code: 

Private Sub CommandButton1_Click()

a = Worksheets("Labour Payments").Cells(Rows.Count, 5).End(xlUp).Row - 5

For i = 6 To a

    If Worksheets("Labour Payments").Cells(i, 4).Value = "15" Then
        
        Worksheets("Labour Payments").Rows(i).Copy
        Worksheets("CIS Return").Activate
        b = Worksheets("CIS Return").Cells(Rows.Count, 2).End(xlUp).Row
        Worksheets("CIS Return").Cells(b + 1, 2).Select
        activeworksheet.Paste
        Worksheets("CIS Return").Activate
        

    End If

Next

Application.CutCopyMode = False


End Sub 


Hi all, thanks for the comments.

bebo021999 - your code doesn't seem to be working.

beyond Excel - Please see attached template document Attachment 811012

Apologies, hopefully it's attached correctly now.

Kind Regards

Danny

According to the post #7 attachment an Excel basics VBA demonstration for starters :

PHP Code: 

Sub Demo1()
        Sheet22.[B8].CurrentRegion.Offset(2).ClearContents
    With Sheet2.[_FilterDatabase].Rows
        If .Parent.FilterMode Then .Parent.ShowAllData
       .AutoFilter 3, Format(Sheet22.[D2], .Cells(2, 3).NumberFormat)
        If Application.Subtotal(103, .Columns(1)) > 1 Then .Item("2:" & .Count).Columns(4).Copy Sheet22.[B10]
        If .Parent.FilterMode Then .Parent.ShowAllData
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Hi Marc,

Great that worked perfect. Thanks for your help. As you can probably tell i dont have much knowledge of VBA coding but i do attempt to try, just very badly atm lol.
i have extended your code for the next steps of what im trying to achieve. i have written VBA "sumifs" to pull through data from "sheet2" onto "sheet22". please see below:

PHP Code: 

Sub Demo1()

Dim Name As String
Dim Period_Number As String
Dim total_quantity As Long



        Sheet22.[B8].CurrentRegion.Offset(2).ClearContents
    With Sheet2.[_FilterDatabase].Rows
        If .Parent.FilterMode Then .Parent.ShowAllData
       .AutoFilter 3, Format(Sheet22.[D2], .Cells(2, 3).NumberFormat)
        If Application.Subtotal(103, .Columns(1)) > 1 Then .Item("2:" & .Count).Columns(4).Copy Sheet22.[B10]
        If .Parent.FilterMode Then .Parent.ShowAllData
    End With
    Range("B9:B100").RemoveDuplicates Columns:=Array(1), Header:=xlYes
    
 'Total payments made (do not include VAT - whole £ only)
    total_quantity = 0
    Name = Worksheets("CIS Return").Cells(10, 2).Value
    Period_Number = Worksheets("CIS Return").Cells(2, 4).Value
    
    lastrow = Worksheets("Labour Payments").Cells(Rows.Count, 5).End(xlUp).Row
    
    For i = 6 To lastrow
    
    If Worksheets("Labour Payments").Cells(i, 5).Value = Name And Worksheets("Labour Payments").Cells(i, 4).Value = Period_Number Then
    total_quantity = total_quantity + Worksheets("Labour Payments").Cells(i, 10).Value
    End If
   
   Next
      
    Worksheets("CIS RETURN").Cells(10, 3).Value = total_quantity
    
    Worksheets("CIS RETURN").Cells(1, 1).Select
    
 'Cost of materials (paid for by subcontractor - whole £ only)
    total_quantity = 0
    Name = Worksheets("CIS Return").Cells(10, 2).Value
    Period_Number = Worksheets("CIS Return").Cells(2, 4).Value
    
    lastrow = Worksheets("Labour Payments").Cells(Rows.Count, 5).End(xlUp).Row
    
    For i = 6 To lastrow
    
    If Worksheets("Labour Payments").Cells(i, 5).Value = Name And Worksheets("Labour Payments").Cells(i, 4).Value = Period_Number Then
    total_quantity = total_quantity + Worksheets("Labour Payments").Cells(i, 21).Value
    End If
   
   Next
      
    Worksheets("CIS RETURN").Cells(10, 4).Value = total_quantity
    Worksheets("CIS RETURN").Cells(1, 1).Select
    
 'Total tax deducted (£ and pence)
    total_quantity = 0
    Name = Worksheets("CIS Return").Cells(10, 2).Value
    Period_Number = Worksheets("CIS Return").Cells(2, 4).Value
    
    lastrow = Worksheets("Labour Payments").Cells(Rows.Count, 5).End(xlUp).Row
    
    For i = 6 To lastrow
    
    If Worksheets("Labour Payments").Cells(i, 5).Value = Name And Worksheets("Labour Payments").Cells(i, 4).Value = Period_Number Then
    total_quantity = total_quantity + Worksheets("Labour Payments").Cells(i, 18).Value
    End If
   
   Next
      
    Worksheets("CIS RETURN").Cells(10, 5).Value = total_quantity
    Worksheets("CIS RETURN").Cells(1, 1).Select
    
       
    
End Sub 


All is working, for the first row within "Sheet22" but i need it to repeat for the other rows below (again the number of rows may fluctuate on "Sheet22" cells B10 onwards from your initial code).

please can you confirm how to adjust.

Kind Regards

Danny