Using VBA code to Copy and Paste Row

[Juan5]

I need to figure out a code to copy and paste an entire row when a keyword is typed into a specific column.
Starting in the Worksheet named "April", I use columns A thru T. When column S says "Closed" I need the entire row from A - T to be moved from Worksheet "April" to the worksheet named "Closed".
I will need this to happen from Rows 3 to 775.

Hopefully this makes sense. I am brand new to using VBA and Macros. I have done some research and tried to create my own codes but it hasn't worked.
Any help would be greatly appreciated.


- John

<colgroup><col><col><col><col><col><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col span="17"></colgroup><tbody>
</tbody>

 

[JoeMo]

Welcome to the forum!

This is sheet code for the Sheet "April".
To install sheet code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, i As Long, j As Long, D As Range
Set R = Intersect(Target, Range("S3:S775"))
If R Is Nothing Then Exit Sub
With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With
For i = 1 To R.Areas.Count
    For j = 1 To R.Areas(i).Cells.Count
        If R.Areas(i)(j) = "Closed" Then
            If D Is Nothing Then
                Set D = R.Areas(i)(j)
            Else
                Set D = Union(D, R.Areas(i)(j))
            End If
            With Range(Cells(R.Areas(i)(j).Row, "A"), Cells(R.Areas(i)(j).Row, "T"))
                .Copy Destination:=Sheets("Closed").Range("A" & Sheets("closed").Rows.Count).End(xlUp).Offset(1, 0)
            End With
        End If
    Next j
Next i
If Not D Is Nothing Then D.EntireRow.Delete
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

This will run automatically whenever a user enters "Complete" in a cell or multiple cells in the range S3:S775 on the worksheet April.

 

[vcoolio]

Hello Juan,

Starting in the Worksheet named "April".....

I suppose that we can assume that you have more than one month sheet to deal with so perhaps the following option may help:-

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim ws As Worksheet: Set ws = Sheets("Closed")

If Intersect(Target, Sh.Range("S3:S775")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Sh.Name = "Closed" Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False

If Target.Value = "Closed" Then
Target.EntireRow.Copy ws.Range("A" & Rows.Count).End(3)(2)
Target.EntireRow.Delete
End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

This code needs to be placed in the Workbook module. To implement this code:-

- Open the VB Editor by pressing Alt + F11.
- Over to the left in the Project Explorer, double click on ThisWorkbook.
- In the big white code field, paste the above code.

The code will work on any sheet except the "Closed" sheet. If any other sheet needs to be excluded from the process then this can be included in the code as well.

I hope that this helps.

Cheerio,
vcoolio.


@ Joe:-

Just a typo in your final sentence and my apologies for being picky. I think you meant to type "Closed".

This will run automatically whenever a user enters "Complete" in a cell or multiple cells in the range S3:S775 on the worksheet April.

 

[JoeMo]

Hello Juan,



I suppose that we can assume that you have more than one month sheet to deal with so perhaps the following option may help:-

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim ws As Worksheet: Set ws = Sheets("Closed")

If Intersect(Target, Sh.Range("S3:S775")) Is Nothing Then Exit Sub
[COLOR=#ff0000][B]If Target.Count > 1 Then Exit Sub[/B][/COLOR]
If Sh.Name = "Closed" Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False

If Target.Value = "Closed" Then
Target.EntireRow.Copy ws.Range("A" & Rows.Count).End(3)(2)
Target.EntireRow.Delete
End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

This code needs to be placed in the Workbook module. To implement this code:-

- Open the VB Editor by pressing Alt + F11.
- Over to the left in the Project Explorer, double click on ThisWorkbook.
- In the big white code field, paste the above code.

The code will work on any sheet except the "Closed" sheet. If any other sheet needs to be excluded from the process then this can be included in the code as well.

I hope that this helps.

Cheerio,
vcoolio.


@ Joe:-

Just a typo in your final sentence and my apologies for being picky. I think you meant to type "Closed".

@vcoolio. Yes, I meant "Closed" - not a typo just a memory lapse.

BTW: the code you provided has a shortcoming (see bold red font in Quote). If a user decides to copy and paste multiple instances of "Closed" in the target range, your code will not execute and none of the rows containing "Closed" in col S will be moved.

 

[vcoolio]

@ Joe:-

@vcoolio. Yes, I meant "Closed" - not a typo just a memory lapse:wink:.

Haha. I know how you feel! Hence the wisdom in your signature quote:-

When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

Thanks also for the heads-up. That's why you're the MVP and I'm not!

Actually, if you don't mind, and I'm sure that the OP would be interested as part of the learning curve in VBA, could you clarify the use of the statement:-

If Target.Count > 1 Then Exit Sub

Over the years, I've had some conflicting advice on its use but I noticed the key words in your reply in post #4 are "copy and paste":-

If a user decides to copy and paste multiple instances......

With change event codes, I've noticed that most Users have a drop down in the target column and then, once a selection is made, the expectation is that the relevant row of data is immediately transferred to the relevant sheet. Basically the same applies to those Users who just type in the criteria as the last entry. Hence, the transfer of data is done row by row. But, without the line of code above, if a User attempts multiple entries at once (say using copy and paste) the code will error. Thus, the line of code as I have used prevents the error and the User just moves on to the next row.
This would then suggest that for multiple entries of a criterion, a "normal" code assigned to a button using AutoFilter or a loop would be the best option.

I would be interested, as I'm sure would the OP and anyone else who may come by this thread, to know your thoughts on this.

Thanks for your time Joe.

Cheerio,
vcoolio.

 

[Roopak1982]

Hi

I am looking for a VBA to open a workbook (FX rate log) from a specific file path and the look for a specific date (Today()-1) and copy the rows including the date and various currency rates to another workbook (Working model) within worksheet (FX Rate).
Data layout in FX Rate log – (Worksheet “Sheet 1)
Date AUD CAD USD ZAR
05/06/2019 0.9456 0.7665 0.7789 5.4321
06/06/2019 0.9457 0.7890 0.7898 5.6543
07/06/2019 0.9467 0.7564 0.6987 5.3215
Working Model – (Worksheet “FX Rate”)
Date AUD CAD USD ZAR
05/06/2019 0.9456 0.7665 0.7789 5.4321
06/06/2019 0.9457 0.7890 0.7898 5.6543
I would like to get the entire row of 07/06/2019 from FX rate log without the headings under the working sheet . In the working model workbook I have the date 07/06/2019 in cell “B2” of sheet 1 which I change daily for reporting. So if the macro could use that date as a reference it would be even better so I can ignore Today()-1
Thanks
Roo​

​

 

[JoeMo]

Actually, if you don't mind, and I'm sure that the OP would be interested as part of the learning curve in VBA, could you clarify the use of the statement:-

If Target.Count > 1 Then Exit Sub

Over the years, I've had some conflicting advice on its use but I noticed the key words in your reply in post #4 are "copy and paste":-



With change event codes, I've noticed that most Users have a drop down in the target column and then, once a selection is made, the expectation is that the relevant row of data is immediately transferred to the relevant sheet. Basically the same applies to those Users who just type in the criteria as the last entry. Hence, the transfer of data is done row by row. But, without the line of code above, if a User attempts multiple entries at once (say using copy and paste) the code will error. Thus, the line of code as I have used prevents the error and the User just moves on to the next row.
This would then suggest that for multiple entries of a criterion, a "normal" code assigned to a button using AutoFilter or a loop would be the best option.

I would be interested, as I'm sure would the OP and anyone else who may come by this thread, to know your thoughts on this.

Thanks for your time Joe.

Cheerio,
vcoolio.

Generally, when I write change event code, I try to make it comprehend possible copy/paste by the user. As you know, copy/paste will override a Data Validation drop down, and even when there's no DV in place, the user may decide to kill multiple birds with a single stone by, for example, in the present thread, copying the word "Closed" and pasting it to several places in the appropriate column with no malicious intent, but simply to save time. If the event code uses the line: If Target.Count > 1 Then Exit Sub, the code writer has avoided a run time error when multiple cells are changed simultaneously, but at the expense of having quelled the code's ability to react to clear and relevant change events. That doesn't strike me as a desirable trade off. Neither does using a standard module that must be triggered manually when what is really needed is change event code. So, I opt for the most part to write code that can deal with copy/paste "multiple" simultaneous changes.

 

[Juan5]

Thank you very much Joe!

 

[Juan5]

This worked great vcoolio. Thank you very much for the help!
I appreciate everyone's response. Helped me big time.


- John

 

[JoeMo]

Thank you very much Joe!

You are welcome - glad we could help.