Copy/Paste If...

[H_Milli]

I am fairly new to loops in macros (and novice in macros in general) and am having trouble creating a macro to copy and paste information from one sheet to another. Here's the example- on Sheet1 of the workbook, I have:

Col A Col B Col C
A 124 Complete
B 27 Incomplete
C 58 Complete
D 11 Incomplete
E 223 Incomplete


Sheet 2 has the same type of information (same data structure) except it contains all items that have been completed in the past. The macro should look on sheet 1, find all of the rows that have "Complete" in column C, select the whole row, then paste it at the bottom of the data range in Sheet2 (do not overwrite existing data). The macro should then go back and delete any rows on Sheet1 that have "Complete" in column c. This way all complete items are stored in Sheet 2 and all incomplete items are in Sheet1 after the macro completes.

Any help or guidance on this is much appreciated. Thanks in advance!

 

[eibi]

H_Milli,

Welcome to the forums. I too am new to VBA -- perhaps that means that my suggestion will be right on par with your needs??

I've attached a file with a macro that does what you have asked. The code I've written is here:

Sub StatusUpdate()

Dim i As Integer
Dim TotalEntries As Integer
Dim CompleteCount As Integer
Dim Prev_Entries As Integer

TotalEntries = WorksheetFunction.CountA(Worksheets("Incomplete").Range("C:C"))

For i = 2 To TotalEntries

    If Worksheets("Incomplete").Range("C" & i) = "Complete" Then
   
        Prev_Entries = WorksheetFunction.CountA(Worksheets("Complete").Range("C:C"))
   
        Worksheets("Complete").Range("A" & Prev_Entries + 1 & ":C" & Prev_Entries + 1).Value = Worksheets("Incomplete").Range("A" & i & ":C" & i).Value
       
        Worksheets("Incomplete").Range(i & ":" & i).Delete
   
    End If

Next i

End Sub

All best...

 

[Marc L]

Hi !

No needs a loop : directly with a filter maybe in less than 10 codelines …
​

 

[H_Milli]

Hi eibi- Thanks for the response! The macro seems to work for the most part, but if I have a lot of entries in the table, it seems to only do a few of them at a time. Any thoughts? Thank you so much for your help! At the very least I can run the macro multiple times to get the desired result.

 

[eibi]

A few issues:

First, I assumed that for every entry, there would be a non-blank value in column C; so I set the loop to run a fixed number of times based on the count of non-blank cells in column C:

TotalEntries = WorksheetFunction.CountA(Worksheets("Incomplete").Range("C:C"))​

If my assumption was wrong, and you have entries with blank cells in column C, we'll can re-configure this line of code accordingly.

Perhaps counting non-blanks in column A would work better?

TotalEntries = WorksheetFunction.CountA(Worksheets("Incomplete").Range("A:A"))
​

Second, your question helped me realize that I've created a problem in the way that I'm deleting the rows after they are moved...(when the row is deleted, the loop count [i and TotalEntries] must also be adjusted for the shorter list)

One solution for this is to insert the following lines immediately before the End If.

        i = i - 1
     
        TotalEntries = TotalEntries - 1

See attached.

 

[Marc L]

In case of a lot of lines, a filter is the faster way at once …
​

 

[H_Milli]

Thank you!​