Loading Pages
 

I have 3 sheets in my workbook. the first two sheets will load the data and
adjust the row. Problem that I am having is that the 3rd sheet keeps losing
its values because it gets it values from sheet 1. I am not a VBA
programmer, but I think that there is something that I need to do in VBA in
order to keep my formuals getting the right data. For example, Sheet 3 Row A
will be getting values from Sheet 1 Column B row 3, 5, 7, 9, etc., When the
program loads Sheet 3 Row A changes and gets values, 7, 11, 15, 21, etc. I
need it to always go to 3, 5, 7, 9, etc. Does this make sense?

"Mindie" wrote in message
...
I have 3 sheets in my workbook. the first two sheets will load the data
and
adjust the row. Problem that I am having is that the 3rd sheet keeps
losing
its values because it gets it values from sheet 1. I am not a VBA
programmer, but I think that there is something that I need to do in VBA
in
order to keep my formuals getting the right data. For example, Sheet 3
Row A
will be getting values from Sheet 1 Column B row 3, 5, 7, 9, etc., When
the
program loads Sheet 3 Row A changes and gets values, 7, 11, 15, 21, etc.
I
need it to always go to 3, 5, 7, 9, etc. Does this make sense?

Not quite. DO you use something like =Sheet1!$B$3 in sheet 3? What makes row
A change? The secondsentence mentions some kind of row adjustment. How is
this done?

/Fredrik

I did not develop this program, I am just adding another page to it. Yes, I
have tried using Sheet1!$B$3 and the rows still shift on me. Sheet 1 is being
loaded by external data. Here is the code that they are using

' clear all the sheets
Call ClearAllCartoons
Call ClearAllDataSheets
' place the data on the sheet
Call InsertScheduleData(sText)
' sort
Call SortWorkBook
' set the print areas, this is done since the deleteing
and adding of rows moves the current print row
' that problem may be resolved with absolute addresses
but this is easier.
Call SetPrintAreas
'ThisWorkbook

"Fredrik Wahlgren" wrote:


"Mindie" wrote in message
...
I have 3 sheets in my workbook. the first two sheets will load the data
and
adjust the row. Problem that I am having is that the 3rd sheet keeps
losing
its values because it gets it values from sheet 1. I am not a VBA
programmer, but I think that there is something that I need to do in VBA
in
order to keep my formuals getting the right data. For example, Sheet 3
Row A
will be getting values from Sheet 1 Column B row 3, 5, 7, 9, etc., When
the
program loads Sheet 3 Row A changes and gets values, 7, 11, 15, 21, etc.
I
need it to always go to 3, 5, 7, 9, etc. Does this make sense?

Not quite. DO you use something like =Sheet1!$B$3 in sheet 3? What makes row
A change? The secondsentence mentions some kind of row adjustment. How is
this done?

/Fredrik

"Mindie" wrote in message
...
I did not develop this program, I am just adding another page to it. Yes,
I
have tried using Sheet1!$B$3 and the rows still shift on me. Sheet 1 is
being
loaded by external data. Here is the code that they are using

' clear all the sheets
Call ClearAllCartoons
Call ClearAllDataSheets
' place the data on the sheet
Call InsertScheduleData(sText)
' sort
Call SortWorkBook
' set the print areas, this is done since the
deleteing
and adding of rows moves the current print row
' that problem may be resolved with absolute addresses
but this is easier.
Call SetPrintAreas
'ThisWorkbook

Are the rows inserted from the top? That makes things more complicated. If
you need to keep this logic, you would need a user defined function that
would have to depend on a global variable, an integer set to the number of
rows inserted. You would probably need to save the absolute offset
somewhere. This solution is pretty complex.

/Fredrik

Yes, the pages are loaded from the top. From there on you lost me. I am not
much of a VBA programmer, I am more 400/mainframe. Any ideas?

"Fredrik Wahlgren" wrote:


"Mindie" wrote in message
...
I did not develop this program, I am just adding another page to it. Yes,
I
have tried using Sheet1!$B$3 and the rows still shift on me. Sheet 1 is
being
loaded by external data. Here is the code that they are using

' clear all the sheets
Call ClearAllCartoons
Call ClearAllDataSheets
' place the data on the sheet
Call InsertScheduleData(sText)
' sort
Call SortWorkBook
' set the print areas, this is done since the
deleteing
and adding of rows moves the current print row
' that problem may be resolved with absolute addresses
but this is easier.
Call SetPrintAreas
'ThisWorkbook

Are the rows inserted from the top? That makes things more complicated. If
you need to keep this logic, you would need a user defined function that
would have to depend on a global variable, an integer set to the number of
rows inserted. You would probably need to save the absolute offset
somewhere. This solution is pretty complex.

/Fredrik

"Mindie" wrote in message
...
Yes, the pages are loaded from the top. From there on you lost me. I am
not
much of a VBA programmer, I am more 400/mainframe. Any ideas?


It just struck me that you should be able to use the OFFSET function
http://www.techonthenet.com/excel/formulas/offset.htm

You could do something like this in Sheet3

=OFFSET(Sheet1!$B$3, A1,0,1,1)

The idea is that when new rows are inserted, you enter the number of rows
inserted as the offset in cell A1 in Sheet3. You could also add some code to
tour macro that would insert this number in A1

/Fredrik