I am the worst Vba programmer ever

Posted on 2006-06-01
Last Modified: 2008-02-26
I can't figure anything out in VBA and I plan on reading a book on VBA programming but until then.....

Below is the code that I received in a previous post.   I dont' want to copy the first row from the "All Data" worksheet to the other worksheets or the column "N".

I was able to hide column "N" in the "All Data" worksheet and it will not be copied over.  But the first row is being copied over to the worksheets, even after i tried hiding it and changing the range to E2.

Sub MoveToTabs()
'this code moves the data from the All Data worksheet to the indv. worksheets

Dim DateToday As Date
DateToday = Format(Now, "mm/dd/yyyy")
Application.ScreenUpdating = False

Sheets("All Data").Columns("N:N").Hidden = True
Sheets("All Data").Rows("1:1").EntireRow.Hidden = True ' This still gets copied over to the other worksheets

With Sheets("All Data").Range("E2")
    .AutoFilter field:=11, Criteria1:="<" & DateToday
    .AutoFilter field:=10, Criteria1:="1 IM -Req"
    .Parent.AutoFilter.Range.Copy Sheets("IM Req").Range("A2")
    .AutoFilter field:=10, Criteria1:="2 IM -App + Model"
    .Parent.AutoFilter.Range.Copy Sheets("IM App").Range("A2")
    .AutoFilter field:=10, Criteria1:="3 IM -MTS"
    .Parent.AutoFilter.Range.Copy Sheets("IM MTS").Range("A2")
    .AutoFilter field:=10, Criteria1:="4 Phase 4 -Complete"
    .Parent.AutoFilter.Range.Copy Sheets("PH 4").Range("A2")
    'changed to copy all phase 5 gates
    .AutoFilter field:=10, Criteria1:="5 Design -Complete"
    '.Parent.AutoFilter.Range.Copy Sheets("PH 5").Range("A1")
    .Parent.AutoFilter.Range.Copy Sheets("PH 5").Range("A65536").End(xlUp).Offset(1, 0)
End With

Sheets("All Data").Columns("N:N").Hidden = False
Sheets("All Data").Rows("1:1").Hidden = False

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Question by:Virge57

    Author Comment

    I didn't state the question: "How do i stop row 1 from the All data from being copied to the other worksheets?
    LVL 44

    Accepted Solution

    Hi Virge57,

    it's the filter row so it gets copied over even if you hide it
    you can however remove row 2 from the sheet you copied the data into

    .Parent.AutoFilter.Range.Copy Sheets("IM Req").Range("A2")
    Sheets("IM Req").Rows(2).Delete shift:=xlShiftUp 'will lift the filtered data one row up

    share what you know, learn what you don't
    LVL 44

    Expert Comment

    another thing you can try is using the specialcells type

        .Parent.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy Sheets("IM Req").Range("A2")

    Author Comment

    That worked great, but i couldn't use it since i would also delete a column that was farther to the right.  The second post gave some odd error.  thanks!!

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
    No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
    This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
    This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now