• 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...

  • 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

MikeTE

New Member
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:
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:
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)
 

Attachments

  • MikeTE.xlsb
    40.4 KB · Views: 19
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.
 
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'?
 
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.
 
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 ..
 

Attachments

  • MikeTE.xlsb
    47.1 KB · Views: 64
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. ^^
 
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!
 
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:
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
 
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)
 
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
 

Attachments

  • Copy of faktury JOH.xlsm
    151.5 KB · Views: 31
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!
 
Back
Top