Using VBA code to Copy and Paste Row

J

Juan5

New Member
Joined
Jun 11, 2019
Messages
4
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
[TABLE="width: 2920"]
<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>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
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.
Code: 
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.
 
Upvote 0
Hello Juan,

Starting in the Worksheet named "April".....
Click to expand...

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


Code: 
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.
Click to expand...
 
Upvote 0
vcoolio said:
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:-


Code: 
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".
Click to expand...
@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.
 
Last edited:
Upvote 0
@ Joe:-
@vcoolio. Yes, I meant "Closed" - not a typo just a memory lapse:wink:.
Click to expand...

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!
Click to expand...

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:-
Code: 
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......
Click to expand...

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.
 
Upvote 0
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​





​
 
Upvote 0
vcoolio said:
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:-
Code: 
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.
Click to expand...
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.
 
Upvote 0
This worked great vcoolio. Thank you very much for the help!
I appreciate everyone's response. Helped me big time.


- John
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Macro code to copy & paste cell data
Replies
1
Views
150
Maverick27
M
F
  • Question Question
VBA code that finds a value in a col. then returns entire row
Replies
3
Views
142
Fudging
F
T
  • Solved
This seems like a numeric error to me, but does anybody have an explanation.
Replies
2
Views
273
TomCon
T
M
  • Question Question
[VBA]Copy data from specify cell and input value to another sheet with time stamp
Replies
2
Views
400
maiwarits
M
W
  • Solved
Formula Error: Error applying formula with VBA even though it works manually
Replies
3
Views
97
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top