VBA to copy excel worksheet and append into another workbook

[CubeLand]

Hi guys,

I was just wondering if anyone is able to help me out with this code?

Currently my code copies all the rows from Sheet1 where column A contains the word "Audit" (this is the value specified in cell T1 in the code). and it appends these rows to the rows in Sheet2.

However I now need to amend this code so that it not only copies across those rows with "Audit" specified in column A but it also only copies across x number of rows which matches the value specified in C9. C9 is a formula which calculates how many records to audit, so if the value in C9 says 9, I would like to only copy across the first 9 rows in Sheet1 where column A contains the word 'Audit'.

Here is my current code:

Sub CopyCells()

  Dim AB As Long, CD As Long, MyRange As Range
    
   AB = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    
        For Each MyRange In Sheets("Sheet1").Range("A15:A" & AB)      
        If MyRange.Value = Sheets("Sheet1").[T1] Then
                
          With Sheets("Sheet2")
            CD = .Cells(Rows.Count, 1).End(xlUp).Row
            .Cells(CD + 1, 1).Resize(, 113).Value = Sheets("Sheet1").Cells(MyRange.Row, 1).Resize(, 113).Value
          End With
        End If
      Next MyRange


End Sub

Thank you so much for any help you are able to provide.

 

[CubeLand]

Hi there, are there any experts out there who may be able to help me?

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub CopyCells()
   
   Dim AB As Long, CD As Long, MyRange As Range, i As Long
   
   AB = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
   i = 1
   For Each MyRange In Sheets("Sheet1").Range("A15:A" & AB)
      If MyRange.Value = Sheets("Sheet1").[T1] Then
         With Sheets("Sheet2")
            CD = .Cells(Rows.Count, 1).End(xlUp).Row
            .Cells(CD + 1, 1).Resize(, 113).Value = Sheets("Sheet1").Cells(MyRange.Row, 1).Resize(, 113).Value
            i = i + 1
            If i = Sheets("Sheet1").Range("C9").Value Then Exit For
         End With
      End If
   Next MyRange
End Sub

 

[CubeLand]

Thank you so much for your reply Fluff, I really appreciate it!
Unfortunately your new syntax still sends all the 'Audit' files across and does not limit the rows sent across to the value stored in C9.

Is there anything else we could try?

 

[Fluff]

Is the C9 value you're interested in on sheet1 or sheet2?

 

[CubeLand]

Sheet1

 

[Fluff]

I made a mistake it should be

If i [COLOR=#ff0000]>[/COLOR] Sheets("Sheet1").Range("C9").Value Then Exit For

but that wouldn't explain what is happening.
Try stepping through the code using F8 & check the value of i as you go, check that it's updating every time a row is copied over.

 

[CubeLand]

Fluff that seems to work a treat now!
Thank you so much for your time in helping me, hopefully as I get more competent - I can return the help to someone else.

 

[Fluff]

Glad to help & thanks for the feedback