1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to Copy and Paste Rows if Condition is Met

Discussion in 'VBA Macros' started by MikeTE, Aug 17, 2018.

  1. MikeTE

    MikeTE New Member

    Messages:
    6
    hello people,

    I'm only starting with VBA, and I hit the wall a bit. Can anybody help me?
    Recently I was trying to create dynamic spreadsheet for expenses calculation which would allow me to see data in separate tabs. For instance I have tab for all data for entire year and I would like to copy lines with expenses in January to January tab and so on. I have managed to run code witch does copy entire line to another tab but my first problem is that the code is not dynamic also how can I add more syntaxes to the code so it would work for all months?


    See my code below:

    Code (vb):

    Sub CopyYes()
      Dim c As Range
      Dim j As Integer
      Dim Source As Worksheet
      Dim Target As Worksheet
      ' Change worksheet designations as needed
     Set Source = ActiveWorkbook.Worksheets("Dane")
      Set Target = ActiveWorkbook.Worksheets("January")
      j = 2  ' Start copying to row 1 in target sheet
     For Each c In Source.Range("b2:b1000")  ' Do 1000 rows
         If c = "January" Then
              Source.Rows(c.Row).Copy Target.Rows(j)
              j = j + 1
          End If
      Next c
    End Sub
     
    < Use Code Tags >
    Last edited by a moderator: Aug 17, 2018
  2. vletm

    vletm Excel Ninja

    Messages:
    4,299
    MikeTE
    Would You use Filtering with this?
    Case month-by-month, there would be also one solution.
    but ... is there needed sheets and so on?
    (Sample code is in Sheet1's code-page)

    Attached Files:

  3. MikeTE

    MikeTE New Member

    Messages:
    6
    hi, thanks for response, I realised I wasn't very clear with my question
    see print screen

    upload_2018-8-17_10-14-12.png

    so all the data listed at the "Dane" tab should be copied to responding month, but not duplicated. I managed to do it one month at a time but it doesn't solve my problem because I need it to be dynamic so if anybody adds new record it will be copied over.
  4. vletm

    vletm Excel Ninja

    Messages:
    4,299
    MikeTE
    Yes, that would be ... different case.
    So, You would like to copy only new row (range B:K ?) to correct month.
    (Copy means that old row will stay in 'Dane'; not move.)
    If 'copy' then which columns will make row like 'new'?
    You have there formulas too.
    Do those formulas need to work in 'monthly'-sheets?
    How about years? or is this 'one year workbook'?
  5. MikeTE

    MikeTE New Member

    Messages:
    6
    1. yes, you are right the rows should be intact in "dane" tab only copy to other tabs for summary
    2. this is the template for one year
    3. formulas don't need to work in other tabs.
    4. all the data for one month in "dane" tab has to be exactly represented in each month tab so no duplicates.
  6. vletm

    vletm Excel Ninja

    Messages:
    4,299
    MikeTE
    Next sample version ...
    There is [ Copy ]-button.
    You skipped few questions ... I gotta guess and do those 'my way'.
    Copied row's B-column cell is the key!
    After copy row (columns from B to K), B-column's font will be bold.
    Next times, only rows which B-column font is NOT bold will copy.
    There won't copy any duplicates, if You won't do those ...
    It's would be compare all cells values, but all depends ..

    Attached Files:

  7. MikeTE

    MikeTE New Member

    Messages:
    6
    Genius! that's exactly what I was trying to do. would it still work if new items will be added or I would have to change the range?

    sorry for lack of proper explanation just couldn't really express what I need to do. ^^
  8. vletm

    vletm Excel Ninja

    Messages:
    4,299
    MikeTE
    If new items means new rows then add add add as much as needed.
    If new items means new column then You gotta modify B:K-range as needed.
    Remember/figure that B-columns cells are the keys!
  9. MikeTE

    MikeTE New Member

    Messages:
    6
    thanks a million! you are truly a excel Ninja ^^

    can I bother you with one more thing?

    there was a bit of code which I was working on for my friend. It does similar thing but every time i run the macro it copies everything. i was trying to add If function to it so it will check all the values and copy over only the new ones without repeating entries.

    (that's the if function I was trying to add)
    If Application.WorksheetFunction.CountIf(c.Range("g:g"), G:G) > 0 Then
    end sub

    do you think it's possible to combine them? I really hit the wall on that one
    (that's the code which actually works but needs adjusting)
    Code (vb):

    Sub CopyYes()
        Dim c As Range
        Dim j As Integer
        Dim Source As Worksheet
        Dim Target As Worksheet
        Dim LastRow As Long
        Dim x As String
      ' Change worksheet designations as needed
       Set Source = ActiveWorkbook.Worksheets("Dane")
        With Sheets("Dane")
            LastRowDane = .Range("B" & .Rows.Count).End(xlUp).Row
        End With
        j = 2  ' Start copying to row 1 in target sheet
       For Each c In Source.Range("b2:b" & LastRowDane)
            x = c
            Set Target = ActiveWorkbook.Worksheets(x)
            With Sheets(x)
                LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
            End With
            Source.Rows(c.Row).Copy Target.Rows(LastRow + 1)
        Next c
    End Sub
     
  10. vletm

    vletm Excel Ninja

    Messages:
    4,299
    MikeTE
    My opinion is that won't work as You would like it work.
    What kind of values would be in G-column?
    Why You won't use 'my sample code'?
    ( as I asked... If 'copy' then which columns will make row like 'new'? in #4 Reply)
  11. MikeTE

    MikeTE New Member

    Messages:
    6
    see that's the s/s i'm creating. so it should allow to add new data all the time as new invoice will arrive and then be added to totals. Your code is brilliant just the only problem I have with it is that I have to have all the data in place before i run it. when i added new line after the data was copied and i tried to do it again it shows "copied 0/12" and nothing happened. I tried to add your code to my form in "zatwierdz" button but I couldn't get it to work. sorry for being a pain with this

    Attached Files:

  12. vletm

    vletm Excel Ninja

    Messages:
    4,299
    MikeTE
    I tested that file with 'my Do_It' ... it seems to works as it should!
    I copied rows from 2 to 9 and it give error msgs with the rest (missing sheet).
    I run it 'as with Your file'.
    Your file has 'some ActiveX-components' which won't work with my Excel!
    If You need to run that from 'Form'
    then You have to take care that
    Activesheet is 'Dane' or
    change that part of code! (( from with Activesheet to with Sheets("Dane") ))

    You wished that row can copy ONCE - okay?
    After You have run ONCE that code, it should copy 'non bold B's!
    After You add row (take care that B-column cell is NON-BOLD!)
    run code ... it will copy again ONLY new rows!
    if it gives 'copied 0/12' then there were all of those has already copied!

Share This Page