Copy certain lines from active sheet and paste it to book 1 and 2

[zone709]

HI,

Trying to do something like this


If "Yards" is in column A then copy and paste all the rows to a new book

If "Grass" is in Column A then copy and paste all the rows to a new book.

The only thing is Grass or Yard wont start in Column A till row 2. Row 1 has my headers and I need to take always row 1 to any new book.

 

[zone709]

I just opened the sheet LCP. I then changed the tab to Sheet1. Now it skips above break and breaks here.

counter = .Columns(c).SpecialCells(xlCellTypeVisible).count

 

[My Aswer Is This]

In your original post you only gave these details:

If "Yards" is in column A then copy and paste all the rows to a new book

If "Grass" is in Column A then copy and paste all the rows to a new book.​

When asking for help we need the name of the Workbook where this data is.
We need to know the name of the sheet where this data is.

And the script will then create two new workbooks and paste the rows into Sheet(1) of the new Workbooks.

When working with things we need specific details.

Stepping through each line of the code and trying to tell you why it does not work is very difficult for me to do.

 

[Trebor76]

Trebor i think i see whats going on with your code. I think it searches threw all my modules of codes and is picking up other stuff to do and pasting it on the new sheets. Instead of looking at just the Activesheet i am using.

No this is not what my code is doing

It's looping through each item (Yards and Grass) in the array I coded.

Have you changed this line of code...

Set ws = ThisWorkbook.Sheets("Sheet1")

...to this like I said to do in my comments:

Set ws = ThisWorkbook.Sheets("LCP")

As I always do I test my code before posting and it worked for me.

 

[Dryver14]

Rows.count,c ? will that work?


lastrow = Workbooks(WB).Sheets(WS).Cells(Rows.Count, c).End(xlUp).Row

 

[My Aswer Is This]

Try this:

Sub Copy_Me()
'Modified  8/26/2019  5:29:49 PM  EDT
Application.ScreenUpdating = False
Cells.AutoFilter
Dim lastrow As Long
Dim c As Long
Dim WB As String
Dim WS As String
WB = ThisWorkbook.Name
WS = ActiveSheet.Name
c = 1
lastrow = Workbooks(WB).Sheets(WS).Cells(Rows.Count, c).End(xlUp).Row
With Workbooks(WB).Sheets(WS).Cells(1, c).Resize(lastrow)
    .AutoFilter 1, "Yards"
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
    Workbooks.Add
    Workbooks(WB).Sheets(WS).Rows(1).Copy Rows(1)
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(1).Rows(2)
        
    Else
        MsgBox "No value  Yards  Found"
    End If
    .AutoFilter
End With
With Workbooks(WB).Sheets(WS).Cells(1, c).Resize(lastrow)
    .AutoFilter 1, "Grass"
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
    Workbooks.Add
    Workbooks(WB).Sheets(WS).Rows(1).Copy Rows(1)
        
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(1).Rows(2)
        
    Else
        MsgBox "No value Grass Found"
    End If
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

 

[zone709]

Hi,

Thanks for the reply, but again i am getting debug on both codes and i dont know why.

Trebor76 it debugs at this spot

Set WS = ThisWorkbook.Sheets("LCP")

My workbook and tab is LCP. So i have no idea why. It says Run time error 9

Dryver14 i changed what you said and used My Answer is This new code with this

[LEFT][COLOR=#333333][FONT=monospace]lastrow = Workbooks(WB).Sheets(WS).Cells(Rows.Count, c).End(xlUp).Row[/FONT][/COLOR][/LEFT]

It also gives me debug with run time error 9.

Something is wrong and my sheet looks fine.

 

[Trebor76]

My workbook and tab is LCP. So i have no idea why. It says Run time error 9

That error generally occurs when you haven't referenced the tab name exactly.

Right click on the LCP tab and from the shortcut menu select Rename. Check for leading or trailing spaces and remove them if they're there.

 

[My Aswer Is This]

My script looks at the Active sheet and the Active Workbook.

Since I did not know either.

 

[zone709]

My script looks at the Active sheet and the Active Workbook.

Since I did not know either.

Hi yeah i agree with you guys. There is something with my sheet. I am going to review it today and figure it out and will get back to you guys on what it was.

Thanks again

 

[zone709]

HI Trebor76 there are no spaces nothing. Your code is actually not breaking, but what its doing is looking threw my modules as i see them flash. It is taking info from different modules i have and pasting them. No clue why fustrating.

My Answer is This: I started a new book with the same data. It not started to break at this point.

counter = .Columns(c).SpecialCells(xlCellTypeVisible).count

It now passes lastrow=

Again frustrating on why both codes are not working right. I been looking at it and changing stuff, but still cant get it and have no clue why.