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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
 
Upvote 0
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?
 
Upvote 0
Is the C9 value you're interested in on sheet1 or sheet2?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top