VBA to copy excel worksheet and append into another workbook

CubeLand

New Member
Joined
Apr 25, 2018
Messages
5
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:

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.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,164
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
Code:
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

New Member
Joined
Apr 25, 2018
Messages
5
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,164
Office Version
365
Platform
Windows
Is the C9 value you're interested in on sheet1 or sheet2?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,164
Office Version
365
Platform
Windows
I made a mistake it should be
Code:
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

New Member
Joined
Apr 25, 2018
Messages
5
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,164
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,096,455
Messages
5,450,561
Members
405,617
Latest member
Passi

This Week's Hot Topics

Top