Newbie;VBA Advanced Filter Help :: MrExcel Message Board



 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Newbie;VBA Advanced Filter Help
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

parsec3
Welcome to the Board


Joined: 20 Sep 2003
Posts: 4

Flag: Usa

Status: Offline

 Reply with quote  

Newbie;VBA Advanced Filter Help

First off I am just learning VBA.
The code below is in a module for a workbook. What it does is send each individual row
of my database to it's own worksheet.(creates one if none exsist).
The problem is that it replaces the old data that is on the worksheets with the new data
every time it is run.(ie. I never have more than the header and ONE row of data)
What I need it to do is add a new row of data every time the macro is run.
From what I understand when I call AdvancedFilter(at least the way I have it now)
it is defaulted to replacing the existing data.
The last row code must only apply to the sheets I am sending the data to.
Thanks in advance for any help!

code:
Option Explicit

Sub FilterStocks()
Dim c As Range
Dim ws As Worksheet
For Each c In Range("STOCKLIST")
  If WksExists(c.Value) = False Then
    Set ws = Sheets.Add
    ws.Name = c.Value
    ws.Move After:=Sheets(Sheets.Count)
    Sheets(Range("STOCKLIST").Cells(1, 1).Value) _
      .Rows("1:1").Copy Destination:=ws.Rows("1:1")
  End If
  Sheets("CRIT").Range("D2").Value = c.Value
  Sheets("DATA").Range("DATABASE").AdvancedFilter _
    Action:=xlFilterCopy, _
      CriteriaRange:=Sheets("CRIT").Range("D1:D2"), _
      CopyToRange:=Sheets(c.Value).Range("A1:H1"), _
      Unique:=False
Next
MsgBox "Data has been sent"
End Sub


Function WksExists(wksName As String) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function


Post Sat Sep 20, 2003 5:22 pm 
 View user's profile Send private message

Yogi Anand
MrExcel MVP


Joined: 13 Mar 2002
Posts: 5749
Location: Michigan USA

Status: Offline

 Reply with quote  

Re: Newbie;VBA Advanced Filter Help

Hi parsec3:

Welcome to MrExcel Board!

The problem occurs when you write the data after AdvanceFiltering to a sheet where some data already exists -- this is because your CopyToRange is always

A1:H1

so you are always writing starting from cell A1

so one of the ways we can fix this is

a) by defining our CopyToRange to begin one row under the existing number of rows in the sheet being written to
b) and this means we will be writng the Header Row as well as the AdvanceFiltered data -- which means every time we encounter a situation that we are writing to a sheet that has some existing data, we also have to delete the row with the header data

Alternately, you AdvanceFilter the data in the original sheet and then copy only the AdvanceFiltered data (sans Field Headers) to the sheet of interest

Does this help? If I have misunderstood your question -- my apologies!
_________________
Regards!
Yogi Anand

Post Sat Sep 20, 2003 7:25 pm 
 View user's profile Send private message

parsec3
Welcome to the Board


Joined: 20 Sep 2003
Posts: 4

Flag: Usa

Status: Offline

 Reply with quote  

Re: Newbie;VBA Advanced Filter Help

Let me take this one at a time,

>>>this is because your CopyToRange is always ....A1:H1
Yes I see this, in fact this is the first place I tried to change
the code to do what I want. I tried several different ways but
still no go.
This is from memory (not at computer with workbook)
Tried xlup? to find last empty row and enter data one row below.
I also tried to use some of the code on this page to make it work,
http://www.rondebruin.nl/copy1.htm
Could not get anything to work, remember I am a newbie.

>>>b) and this means we will be writng the Header Row as well as the AdvanceFiltered data -- which means every time we encounter a situation that we are writing to a sheet that has some existing data, we also have to delete the row with the header data.
Not sure what you mean...
>delete the header from the sheets that it is filtered to
every time the data is sent?

>>>Alternately, you AdvanceFilter the data in the original sheet and then copy only the AdvanceFiltered data (sans Field Headers) to the sheet of interest
This would work...doable even for me.I am just trying to find
a way to do this without so many steps.

Post Sat Sep 20, 2003 8:47 pm 
 View user's profile Send private message

Yogi Anand
MrExcel MVP


Joined: 13 Mar 2002
Posts: 5749
Location: Michigan USA

Status: Offline

 Reply with quote  

Re: Newbie;VBA Advanced Filter Help

quote:
Originally posted by parsec3:

....
>>>Alternately, you AdvanceFilter the data in the original sheet and then copy only the AdvanceFiltered data (sans Field Headers) to the sheet of interest
This would work...doable even for me.I am just trying to find
a way to do this without so many steps.

Hi parsec3:

Looks like you like the Alternate method proposed in my earlier respons. Are you all set now? -- meaning do you have everything working now the way it should? otherwise post back and then let us take it from there.
_________________
Regards!
Yogi Anand

Post Sun Sep 21, 2003 3:47 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.