Results 1 to 9 of 9

Thread: VBA to copy excel worksheet and append into another workbook

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post VBA to copy excel worksheet and append into another workbook

    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.

  2. #2
    New Member
    Join Date
    Apr 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy excel worksheet and append into another workbook

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

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,724
    Post Thanks / Like
    Mentioned
    381 Post(s)
    Tagged
    40 Thread(s)

    Default Re: VBA to copy excel worksheet and append into another workbook

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  4. #4
    New Member
    Join Date
    Apr 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy excel worksheet and append into another workbook

    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?

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,724
    Post Thanks / Like
    Mentioned
    381 Post(s)
    Tagged
    40 Thread(s)

    Default Re: VBA to copy excel worksheet and append into another workbook

    Is the C9 value you're interested in on sheet1 or sheet2?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  6. #6
    New Member
    Join Date
    Apr 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy excel worksheet and append into another workbook

    Sheet1

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,724
    Post Thanks / Like
    Mentioned
    381 Post(s)
    Tagged
    40 Thread(s)

    Default Re: VBA to copy excel worksheet and append into another workbook

    I made a mistake it should be
    Code:
    If i > 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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  8. #8
    New Member
    Join Date
    Apr 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy excel worksheet and append into another workbook

    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.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,724
    Post Thanks / Like
    Mentioned
    381 Post(s)
    Tagged
    40 Thread(s)

    Default Re: VBA to copy excel worksheet and append into another workbook

    Glad to help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •