Multiple macros in one workbook
 

I have two worksheets in a workbook that generate pivot tables. I have found
that if I run the macro associated with one of them, and then run the macro
for the other, the second one loses data. I think that the problem may be
from the Advanced Filters that I use in both of these macros. They use
different filters, and are thus using separate procedures. However, much of
the rest of the code is the same for each macro, with just an if statement in
there to determine some small differences, so in those cases they use the
same procedures. I do not know why I am losing data here. If I just run the
second macro, then it does not lose any data.

Multiple macros in one workbook
 

Hello,

Your explanation is all very confusing. To see exactly what your code is
doing, open your VBE and keep it about half-screen with your Excel workbook
open in the background (you can move around the VBE while the code runs as
you see fit). Now, in the procedure you want to run, put your cursor
anywhere in that sub routine, then press F8 to begin stepping through your
code.

The first time you press F8 your code will compile. Everytime thereafter it
will execute the next (highlighted) line of code. With your windows
positioned just right you should be able to see what your code is doing.
Diagnose your code this way and amend your code as needed. Post back if you
need more help.

HTH

--
Regards,
Zack Barresse, aka firefytr



"CypherSmith" wrote in message
...
I have two worksheets in a workbook that generate pivot tables. I have
found
that if I run the macro associated with one of them, and then run the
macro
for the other, the second one loses data. I think that the problem may be
from the Advanced Filters that I use in both of these macros. They use
different filters, and are thus using separate procedures. However, much
of
the rest of the code is the same for each macro, with just an if statement
in
there to determine some small differences, so in those cases they use the
same procedures. I do not know why I am losing data here. If I just run
the
second macro, then it does not lose any data.

Multiple macros in one workbook
 

Thanks Zack,

I am sorry if my explanation was confusing. My code is pretty extensive,
and I was trying to simplify.

My macros basically go something like this (generally, not using code):

find out what data is needed
filter data to get only the needed data
copy data to temporary workbook
manipulate the data
copy new data into new sheet in original workbook
close old workbook
use new data to do pivot table and chart

I use the same procedures for everything except the filter and determining
what data is needed.

Let's call them macro A and macro B. Macro A determines what data it needs
using radio buttons which tell me which site and which organization to pull
the data for. Macro B determines what data it needs using a text box wherein
the user inputs the plans he wants to get information on.

If you run macro B on its own, the pivot table and chart will have all of
the information.

However, if you run macro A first, then run macro B, it will only get some
of the information.

Cameron

"Zack Barresse" wrote:

Hello,

Your explanation is all very confusing. To see exactly what your code is
doing, open your VBE and keep it about half-screen with your Excel workbook
open in the background (you can move around the VBE while the code runs as
you see fit). Now, in the procedure you want to run, put your cursor
anywhere in that sub routine, then press F8 to begin stepping through your
code.

The first time you press F8 your code will compile. Everytime thereafter it
will execute the next (highlighted) line of code. With your windows
positioned just right you should be able to see what your code is doing.
Diagnose your code this way and amend your code as needed. Post back if you
need more help.

HTH

--
Regards,
Zack Barresse, aka firefytr



"CypherSmith" wrote in message
...
I have two worksheets in a workbook that generate pivot tables. I have
found
that if I run the macro associated with one of them, and then run the
macro
for the other, the second one loses data. I think that the problem may be
from the Advanced Filters that I use in both of these macros. They use
different filters, and are thus using separate procedures. However, much
of
the rest of the code is the same for each macro, with just an if statement
in
there to determine some small differences, so in those cases they use the
same procedures. I do not know why I am losing data here. If I just run
the
second macro, then it does not lose any data.

Multiple macros in one workbook
 

Try stepping through it as I laid forth in the last post I made. You'll be
able to "see" what's going on.

--
Regards,
Zack Barresse, aka firefytr



"CypherSmith" wrote in message
...
Thanks Zack,

I am sorry if my explanation was confusing. My code is pretty extensive,
and I was trying to simplify.

My macros basically go something like this (generally, not using code):

find out what data is needed
filter data to get only the needed data
copy data to temporary workbook
manipulate the data
copy new data into new sheet in original workbook
close old workbook
use new data to do pivot table and chart

I use the same procedures for everything except the filter and determining
what data is needed.

Let's call them macro A and macro B. Macro A determines what data it
needs
using radio buttons which tell me which site and which organization to
pull
the data for. Macro B determines what data it needs using a text box
wherein
the user inputs the plans he wants to get information on.

If you run macro B on its own, the pivot table and chart will have all of
the information.

However, if you run macro A first, then run macro B, it will only get some
of the information.

Cameron

"Zack Barresse" wrote:

Hello,

Your explanation is all very confusing. To see exactly what your code is
doing, open your VBE and keep it about half-screen with your Excel
workbook
open in the background (you can move around the VBE while the code runs
as
you see fit). Now, in the procedure you want to run, put your cursor
anywhere in that sub routine, then press F8 to begin stepping through
your
code.

The first time you press F8 your code will compile. Everytime thereafter
it
will execute the next (highlighted) line of code. With your windows
positioned just right you should be able to see what your code is doing.
Diagnose your code this way and amend your code as needed. Post back if
you
need more help.

HTH

--
Regards,
Zack Barresse, aka firefytr



"CypherSmith" wrote in message
...
I have two worksheets in a workbook that generate pivot tables. I have
found
that if I run the macro associated with one of them, and then run the
macro
for the other, the second one loses data. I think that the problem may
be
from the Advanced Filters that I use in both of these macros. They use
different filters, and are thus using separate procedures. However,
much
of
the rest of the code is the same for each macro, with just an if
statement
in
there to determine some small differences, so in those cases they use
the
same procedures. I do not know why I am losing data here. If I just
run
the
second macro, then it does not lose any data.

Multiple macros in one workbook
 

Ok, the problem is that I have defined a Criteria Range which contains the
criteria that I use in the filter, and it is not being cleared. I set it
like this:

Set CritRange = Cells(1, Nextcol).Resize(2, 2)

Then I clear it like this:

CritRange.Clear

That does not work. I have also tried deleting the columns that the
criteria range is in (as the criteria range is in the last columns and there
is nothing else in those columns). I did that like this:

For i = Nextcol To Nextcol + 1
DeleteRange = Range(Cells(1, Nextcol).Address, Cells(1, Nextcol +
1).Address)
DeleteRange.Columns(i).EntireColumn.Delete
Next i

That doesn't work either. The columns are not deleted. I don't understand
why that is.

"Zack Barresse" wrote:

Try stepping through it as I laid forth in the last post I made. You'll be
able to "see" what's going on.

--
Regards,
Zack Barresse, aka firefytr



"CypherSmith" wrote in message
...
Thanks Zack,

I am sorry if my explanation was confusing. My code is pretty extensive,
and I was trying to simplify.

My macros basically go something like this (generally, not using code):

find out what data is needed
filter data to get only the needed data
copy data to temporary workbook
manipulate the data
copy new data into new sheet in original workbook
close old workbook
use new data to do pivot table and chart

I use the same procedures for everything except the filter and determining
what data is needed.

Let's call them macro A and macro B. Macro A determines what data it
needs
using radio buttons which tell me which site and which organization to
pull
the data for. Macro B determines what data it needs using a text box
wherein
the user inputs the plans he wants to get information on.

If you run macro B on its own, the pivot table and chart will have all of
the information.

However, if you run macro A first, then run macro B, it will only get some
of the information.

Cameron

"Zack Barresse" wrote:

Hello,

Your explanation is all very confusing. To see exactly what your code is
doing, open your VBE and keep it about half-screen with your Excel
workbook
open in the background (you can move around the VBE while the code runs
as
you see fit). Now, in the procedure you want to run, put your cursor
anywhere in that sub routine, then press F8 to begin stepping through
your
code.

The first time you press F8 your code will compile. Everytime thereafter
it
will execute the next (highlighted) line of code. With your windows
positioned just right you should be able to see what your code is doing.
Diagnose your code this way and amend your code as needed. Post back if
you
need more help.

HTH

--
Regards,
Zack Barresse, aka firefytr



"CypherSmith" wrote in message
...
I have two worksheets in a workbook that generate pivot tables. I have
found
that if I run the macro associated with one of them, and then run the
macro
for the other, the second one loses data. I think that the problem may
be
from the Advanced Filters that I use in both of these macros. They use
different filters, and are thus using separate procedures. However,
much
of
the rest of the code is the same for each macro, with just an if
statement
in
there to determine some small differences, so in those cases they use
the
same procedures. I do not know why I am losing data here. If I just
run
the
second macro, then it does not lose any data.

Multiple macros in one workbook
 

Do you have your ScreenUpdating set to False while this code is running? If
so you might not see these steps performed while stepping through your code.
To do any further diagnostics on your code, we'd need to see your entire
code and an explanation of your data structure.

--
Regards,
Zack Barresse, aka firefytr



"CypherSmith" wrote in message
...
Ok, the problem is that I have defined a Criteria Range which contains the
criteria that I use in the filter, and it is not being cleared. I set it
like this:

Set CritRange = Cells(1, Nextcol).Resize(2, 2)

Then I clear it like this:

CritRange.Clear

That does not work. I have also tried deleting the columns that the
criteria range is in (as the criteria range is in the last columns and
there
is nothing else in those columns). I did that like this:

For i = Nextcol To Nextcol + 1
DeleteRange = Range(Cells(1, Nextcol).Address, Cells(1, Nextcol +
1).Address)
DeleteRange.Columns(i).EntireColumn.Delete
Next i

That doesn't work either. The columns are not deleted. I don't
understand
why that is.

"Zack Barresse" wrote:

Try stepping through it as I laid forth in the last post I made. You'll
be
able to "see" what's going on.

--
Regards,
Zack Barresse, aka firefytr



"CypherSmith" wrote in message
...
Thanks Zack,

I am sorry if my explanation was confusing. My code is pretty
extensive,
and I was trying to simplify.

My macros basically go something like this (generally, not using code):

find out what data is needed
filter data to get only the needed data
copy data to temporary workbook
manipulate the data
copy new data into new sheet in original workbook
close old workbook
use new data to do pivot table and chart

I use the same procedures for everything except the filter and
determining
what data is needed.

Let's call them macro A and macro B. Macro A determines what data it
needs
using radio buttons which tell me which site and which organization to
pull
the data for. Macro B determines what data it needs using a text box
wherein
the user inputs the plans he wants to get information on.

If you run macro B on its own, the pivot table and chart will have all
of
the information.

However, if you run macro A first, then run macro B, it will only get
some
of the information.

Cameron

"Zack Barresse" wrote:

Hello,

Your explanation is all very confusing. To see exactly what your code
is
doing, open your VBE and keep it about half-screen with your Excel
workbook
open in the background (you can move around the VBE while the code
runs
as
you see fit). Now, in the procedure you want to run, put your cursor
anywhere in that sub routine, then press F8 to begin stepping through
your
code.

The first time you press F8 your code will compile. Everytime
thereafter
it
will execute the next (highlighted) line of code. With your windows
positioned just right you should be able to see what your code is
doing.
Diagnose your code this way and amend your code as needed. Post back
if
you
need more help.

HTH

--
Regards,
Zack Barresse, aka firefytr



"CypherSmith" wrote in message
...
I have two worksheets in a workbook that generate pivot tables. I
have
found
that if I run the macro associated with one of them, and then run
the
macro
for the other, the second one loses data. I think that the problem
may
be
from the Advanced Filters that I use in both of these macros. They
use
different filters, and are thus using separate procedures. However,
much
of
the rest of the code is the same for each macro, with just an if
statement
in
there to determine some small differences, so in those cases they
use
the
same procedures. I do not know why I am losing data here. If I
just
run
the
second macro, then it does not lose any data.