Run time error when copying rows based on word

jeffreybrown · Jan 10, 2009

Hi All,

Here is my table which is just a test sample of the larger table, but in the end, it is column 1 I want to base the new ws on.

Before Macro

Excel Workbook

A

B

1

Unit

Count

2

5

3

2

10

4

31 LGR SQ

10

5

8

4

6

31 LGR SQ

7

2

6

AY

The code below works fine through the first instance of the match and adds a new ws based on the name, but when it gets to the second match the macro tries to add the ws all over again and I get a run time error 1004 which states you can not add a ws and name it the same as one that already exist. I only have one sheet in my wb titled "AY". How can I also have the two column headers transfer to the new ws?

Code:

Public Sub CopyUnit()

Dim N As String
Dim i As Long
Dim ws As Worksheet
Set ws = Sheets("AY")

N = Worksheets("PAS Codes").Range("L14").Value

For i = Range("A65334").End(xlUp).Row To 1 Step -1

With ws
 If Cells(i, 1).Value = N Then
    .Rows(i).Copy
      Sheets.Add.Name = N
    Rows("1:1").Select
    ActiveSheet.Paste
 End If
 
 End With

Next i

Application.CutCopyMode = False
    
End Sub

After Macro

Excel Workbook

A

B

1

Unit

Count

2

31 LGR SQ

7

3

31 LGR SQ

10

31 LGR SQ

Peter_SSs · Jan 10, 2009

Jeff

I've taken quite a few guesses about what you have and what you are trying to do. Give this a whirl on a copy of your workbook. If it's not what you need , I'll probably have to come back and ask some questions to clarify the situation.

 Public Sub CopyUnit() Dim N As String Dim i As Long Dim ws As Worksheet, wsNew As Worksheet Set ws = Sheets("AY") N = Worksheets("PAS Codes").Range("L14").Value On Error Resume Next Sheets.Add.Name = N On Error GoTo 0 Set wsNew = Sheets(N) With ws For i = .Range("A65334").End(xlUp).Row To 1 Step -1 If .Cells(i, 1).Value = N Then .Rows(i).Copy Destination:= _ wsNew.Range("A" & Rows.Count).End(xlUp).Offset(1) End If Next i End With Application.CutCopyMode = False Set ws = Nothing Set wsNew = Nothing End Sub

dave3009 · Jan 10, 2009

Is it Error 438?

Despite the Macro recorder always writing .Paste it doesn't actually work, use .PasteSpecial instead or as Peter_Sss suggested do .Copy Destination:=.... which saves you a few lines of code/screen flicker and the need to set CutCopyMode to false.

HTH

Dave

jeffreybrown · Jan 10, 2009

Peter/Dave,

Thank you so much for the help. That works great Peter. The only thing it did not do is transfer the column headers. As you can see I am pulling the value from sheet "PAS Codes", but I would like to turn this into a DV which will trigger on Selection change. I added this to the "PAS Codes" code, but it doesn't do anything on change.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.CopyUnit
End Sub

Do I need some type of If Target.Address?

Peter_SSs · Jan 10, 2009

jrb said:
Peter/Dave,

Thank you so much for the help. That works great Peter. The only thing it did not do is transfer the column headers. As you can see I am pulling the value from sheet "PAS Codes", but I would like to turn this into a DV which will trigger on Selection change. I added this to the "PAS Codes" code, but it doesn't do anything on change.

Code:

Private Sub Worksheet_Change(ByVal Target As Range) ThisWorkbook.CopyUnit End Sub

Do I need some type of If Target.Address?

What do you expect to happen if, say, "31 LGR SQ" is selected in "PAS Codes" cell L14 and then later that same value is selected again?

jeffreybrown · Jan 10, 2009

Peter,

In cell L14 "PAS Codes" I have already created the DV with the list of names. If I select "31 LGR SQ" and it creates a sheet, I would expect if I select that name again and the sheet exist for the macro to exit because it already exists. I get new data once a week so chances are the sheet will not exist, but I guess like you say, "What if?".

sijpie · Jan 10, 2009

I think your original code was correct bar one line: You had the
Sheets.Add.Name = N
within your loop, so it is trying to create the sheet over and over again. If you took this line outside the loop (before it is started) then I think it works OK.

Peter_SSs · Jan 10, 2009

I'm suggesting a number of changes.

1. Change the code to be Worksheet_Change code for the "PAS Codes" sheet. (Post back if you need to know how to do that)

2. Instead of looping through every row and testing for a code match, use AutoFilter to get the lot at once. This has the added advantage of getting the headings at the same time.

3. The code is skipped if the value in cell L14 on PAS Codes is deleted.

4. The filter/copy is skipped and a message box is displayed if a particular code sheet already exists. That is, if the same value is selected more than once in L14.

See how this goes.

Private Sub Worksheet_Change(ByVal Target As Range) Dim N As String Dim wsAY As Worksheet, wsN As Worksheet N = Range("L14").Value If N = "" Then Exit Sub Application.ScreenUpdating = False Application.EnableEvents = False Set wsAY = Sheets("AY") On Error GoTo PreExit Sheets.Add.Name = N On Error GoTo 0 Set wsN = Sheets(N) With wsAY.UsedRange .AutoFilter Field:=1, Criteria1:=N .Copy Destination:=wsN.Range("A1") .AutoFilter End With Set wsAY = Nothing: Set wsN = Nothing Application.ScreenUpdating = True Application.EnableEvents = True Exit Sub PreExit: On Error GoTo 0 Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True MsgBox "Sheet " & N & " already exists. Copy skipped." Application.ScreenUpdating = True Application.EnableEvents = True End Sub

jeffreybrown · Jan 10, 2009

Peter,

This works extremely nice, I can't thank you enough. I sure appreciate your expertise along with all the other experts. Have a good day or maybe it is night for you?

Peter_SSs · Jan 10, 2009

jrb said:
Have a good day or maybe it is night for you?

Definitely night and nearly time to sign off. Getting on towards 11pm (Saturday) here.

Glad to have been able to help.

Run time error when copying rows based on word

jeffreybrown

Well-known Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

dave3009

Well-known Member

jeffreybrown

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

jeffreybrown

Well-known Member

sijpie

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

jeffreybrown

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Run time error when copying rows based on word

Well-known Member

Excel Facts

MrExcel MVP, Moderator

Well-known Member

Well-known Member

MrExcel MVP, Moderator

Well-known Member

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock