Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


I am the worst Vba programmer ever

Posted on 2006-06-01
Medium Priority
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
  • 2
  • 2

Author Comment

ID: 16807614
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

bruintje earned 900 total points
ID: 16808558
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

ID: 16808582
another thing you can try is using the specialcells type

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

Author Comment

ID: 16816944
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

564 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