Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
"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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
"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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Numbering pages | Excel Worksheet Functions | |||
numbering all pages in worksheet | Excel Discussion (Misc queries) | |||
PRINTING PAGES | Setting up and Configuration of Excel | |||
How do I remove pages in Excel? | Excel Discussion (Misc queries) | |||
How do I delete pages? | Excel Discussion (Misc queries) |