Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Macros within Excel
Is it possible to do a macro to update information between the following
situation. 1 - Journal Spreadsheet - same every week - needs to be updated in cells J2, J5, J8, etc. - every 3rd cell with information from spreadsheet 2. 2 - Payroll Spreadsheet - different every week - information in column H - cells H13, H14, H15 update cells in spreadsheet 1 - i.e. H13 to J2, H14 to J5, H15 to J8, etc. When the new payroll spreadsheet (spreadsheet 3) comes into play, it has a different name because of the date i.e. Payroll 2-15-08 - my macro only works from Spreadsheet 1 to Spreadsheet 2 - when Spreadsheet 3 comes into play - is there a way to do a macro to use this new information to go into Spreadsheet 1 - the Journal? Can this be done when it has a new name (different date)? I know about the + and = signs to update/change information from one spreadsheet to another - that is how I wrote my first macro. The cells we need to use for the upated information are in the same location every week. Spreadsheet 3 creates the problem.... Or is there a better way? Thanks for any input! -- Coni |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Macros within Excel
If you post your macro code we will be in a better position to assist
you. Generally speaking, you will have lines in your code containing phrases like: Sheets("Sheet3") etc and you will need to change these to: Sheets(my_sheet) where my_sheet is a text variable that is built up into the sheet name that you want to reference. Hope this helps. Pete On Mar 17, 3:05*pm, Coni wrote: Is it possible to do a macro to update information between the following situation. 1 - Journal Spreadsheet - same every week - needs to be updated in cells J2, J5, J8, etc. - every 3rd cell with information from spreadsheet 2. 2 - Payroll Spreadsheet - different every week - information in column H - cells H13, H14, H15 update cells in spreadsheet 1 - i.e. H13 to J2, H14 to J5, H15 to J8, etc. When the new payroll spreadsheet (spreadsheet 3) comes into play, it has a different name because of the date i.e. Payroll 2-15-08 - my macro only works from Spreadsheet 1 to Spreadsheet 2 - when Spreadsheet 3 comes into play - is there a way to do a macro to use this new information to go into Spreadsheet 1 - the Journal? *Can this be done when it has a new name (different date)? I know about the + and = signs to update/change information from one spreadsheet to another - that is how I wrote my first macro. *The cells we need to use for the upated information are in the same location every week.. * Spreadsheet 3 creates the problem.... Or is there a better way? *Thanks for any input! -- Coni |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Macros within Excel
Hi Pete,
Thank you for your reply. My macro looks like this...and this is just how it recorded when I did this... ='[TEST 1 - Payroll - blank.xlsx] Sheet 1'!$H$13 then, ...$H$14 - $H$15, etc. I do not know what I am doing! But have been requested to research this and nobody in my office is familiar either! With your response, it seems I need to change Sheet 1 to my_sheet? Then, when you say this is a test variable that is built up into the sheet name that you want to reference...such as? Can you give me an example? I really appreciate your time. Thank you for dealing with Macro 101! -- Coni "Pete_UK" wrote: If you post your macro code we will be in a better position to assist you. Generally speaking, you will have lines in your code containing phrases like: Sheets("Sheet3") etc and you will need to change these to: Sheets(my_sheet) where my_sheet is a text variable that is built up into the sheet name that you want to reference. Hope this helps. Pete On Mar 17, 3:05 pm, Coni wrote: Is it possible to do a macro to update information between the following situation. 1 - Journal Spreadsheet - same every week - needs to be updated in cells J2, J5, J8, etc. - every 3rd cell with information from spreadsheet 2. 2 - Payroll Spreadsheet - different every week - information in column H - cells H13, H14, H15 update cells in spreadsheet 1 - i.e. H13 to J2, H14 to J5, H15 to J8, etc. When the new payroll spreadsheet (spreadsheet 3) comes into play, it has a different name because of the date i.e. Payroll 2-15-08 - my macro only works from Spreadsheet 1 to Spreadsheet 2 - when Spreadsheet 3 comes into play - is there a way to do a macro to use this new information to go into Spreadsheet 1 - the Journal? Can this be done when it has a new name (different date)? I know about the + and = signs to update/change information from one spreadsheet to another - that is how I wrote my first macro. The cells we need to use for the upated information are in the same location every week.. Spreadsheet 3 creates the problem.... Or is there a better way? Thanks for any input! -- Coni |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Macros within Excel
I think you are confusing the terminology. What you have is a formula,
not a macro, and the formula will bring data from cell H13 in Sheet1 of the workbook (file) called TEST 1 - Payroll - blank.xlsx. It is also clear from this that you are using Excel 2007. What you need to do is to describe the layout of your data in the three sheets (are these all in different files?) and then describe what it is you want to do - let the respondents here work out how to do it. Hope this helps. Pete On Mar 18, 1:24*pm, Coni wrote: Hi Pete, Thank you for your reply. *My macro looks like this...and this is just how it recorded when I did this... ='[TEST 1 - Payroll - blank.xlsx] Sheet 1'!$H$13 then, ...$H$14 - $H$15, etc. I do not know what I am doing! *But have been requested to research this and nobody in my office is familiar either! *With your response, it seems I need to change Sheet 1 to my_sheet? *Then, when you say this is a test variable that is built up into the sheet name that you want to reference...such as? * Can you give me an example? I really appreciate your time. *Thank you for dealing with Macro 101! -- Coni "Pete_UK" wrote: If you post your macro code we will be in a better position to assist you. Generally speaking, you will have lines in your code containing phrases like: * * * Sheets("Sheet3") etc and you will need to change these to: * * * Sheets(my_sheet) where my_sheet is a text variable that is built up into the sheet name that you want to reference. Hope this helps. Pete On Mar 17, 3:05 pm, Coni wrote: Is it possible to do a macro to update information between the following situation. 1 - Journal Spreadsheet - same every week - needs to be updated in cells J2, J5, J8, etc. - every 3rd cell with information from spreadsheet 2. 2 - Payroll Spreadsheet - different every week - information in column H - cells H13, H14, H15 update cells in spreadsheet 1 - i.e. H13 to J2, H14 to J5, H15 to J8, etc. When the new payroll spreadsheet (spreadsheet 3) comes into play, it has a different name because of the date i.e. Payroll 2-15-08 - my macro only works from Spreadsheet 1 to Spreadsheet 2 - when Spreadsheet 3 comes into play - is there a way to do a macro to use this new information to go into Spreadsheet 1 - the Journal? *Can this be done when it has a new name (different date)? I know about the + and = signs to update/change information from one spreadsheet to another - that is how I wrote my first macro. *The cells we need to use for the upated information are in the same location every week.. * Spreadsheet 3 creates the problem.... Or is there a better way? *Thanks for any input! -- Coni- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Macros within Excel
Hi Pete!
Thank you for your reply. I thought I described the layout of my data in my first request, but here it is. There are 3 different spreadsheets/files - not "sheets" within one spreadsheet. The 1st spreadsheet is the only one that needs updated - from the weekly payroll spreadsheets... 1. "Journal Spreadsheet" - same every week - needs to be updated in cells J2, J4, J8, etc. - every 3rd cell with information from spreadsheet 2, then the next payroll week - spreadsheet 3, etc. 2. "Payroll Spreadsheet" - different payroll total every week - information in column H - cells H13, H14, H14 update cells in the "Journal Spreadsheet" - i.e. H13 to J2, H14 to J5, H15 to J8. This update continues through H20. 3. "Payroll Spreadsheet" - New payroll - new spreadsheet - it has a different name then #2 because of the date (i.e. Payroll 2-15-08, next week Payroll 2-22-08, etc.) I only know how to write "formulas" according to your description (and I appreciate the correct terminology!), the + and = signs, to exchange information from 2 spreadsheets. Is there a way to do a macro to use this new information (always the same cells - H13 to J2, H14 to J5, etc) to go into Spreadsheet 1 - "Journal Spreadsheet" - at the end of each weekly payroll? I don't know how to describe it any other way? Or maybe there is a better way to accomplish this project? Do you have any more questions? -- Coni "Pete_UK" wrote: I think you are confusing the terminology. What you have is a formula, not a macro, and the formula will bring data from cell H13 in Sheet1 of the workbook (file) called TEST 1 - Payroll - blank.xlsx. It is also clear from this that you are using Excel 2007. What you need to do is to describe the layout of your data in the three sheets (are these all in different files?) and then describe what it is you want to do - let the respondents here work out how to do it. Hope this helps. Pete On Mar 18, 1:24 pm, Coni wrote: Hi Pete, Thank you for your reply. My macro looks like this...and this is just how it recorded when I did this... ='[TEST 1 - Payroll - blank.xlsx] Sheet 1'!$H$13 then, ...$H$14 - $H$15, etc. I do not know what I am doing! But have been requested to research this and nobody in my office is familiar either! With your response, it seems I need to change Sheet 1 to my_sheet? Then, when you say this is a test variable that is built up into the sheet name that you want to reference...such as? Can you give me an example? I really appreciate your time. Thank you for dealing with Macro 101! -- Coni "Pete_UK" wrote: If you post your macro code we will be in a better position to assist you. Generally speaking, you will have lines in your code containing phrases like: Sheets("Sheet3") etc and you will need to change these to: Sheets(my_sheet) where my_sheet is a text variable that is built up into the sheet name that you want to reference. Hope this helps. Pete On Mar 17, 3:05 pm, Coni wrote: Is it possible to do a macro to update information between the following situation. 1 - Journal Spreadsheet - same every week - needs to be updated in cells J2, J5, J8, etc. - every 3rd cell with information from spreadsheet 2. 2 - Payroll Spreadsheet - different every week - information in column H - cells H13, H14, H15 update cells in spreadsheet 1 - i.e. H13 to J2, H14 to J5, H15 to J8, etc. When the new payroll spreadsheet (spreadsheet 3) comes into play, it has a different name because of the date i.e. Payroll 2-15-08 - my macro only works from Spreadsheet 1 to Spreadsheet 2 - when Spreadsheet 3 comes into play - is there a way to do a macro to use this new information to go into Spreadsheet 1 - the Journal? Can this be done when it has a new name (different date)? I know about the + and = signs to update/change information from one spreadsheet to another - that is how I wrote my first macro. The cells we need to use for the upated information are in the same location every week.. Spreadsheet 3 creates the problem.... Or is there a better way? Thanks for any input! -- Coni- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros warning always shows up, even if all macros removed | Excel Discussion (Misc queries) | |||
Macros - copying macros from one computer to another | Excel Discussion (Misc queries) | |||
Training: More on how to use macros in Excel: Recording Macros | Excel Worksheet Functions | |||
Excel Macros | Excel Discussion (Misc queries) | |||
help with excel (maybe macros) | Excel Worksheet Functions |