Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to link worksheets
I'm creating a spreadsheet that will have several worksheets added over time.
i.e. a new worksheet for each new customer. All of the individual worksheets will have the customers information added to it when it is received. There will be a summary sheet added to create a master list of all customers and their basic info...Name, Location, Contact Name....etc. How would I write a simple macro that will take the customer basic info and add it to the summary sheet each time a new worksheet is added? Basically just a macro that will enter the formula ='Customer (1)'!A1 into the summary sheet from the customer sheet. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to link worksheets
Couple of questions Todd.
When you add a new sheet there will be no customer information in it so what is there to add to the summary sheet? Is the added sheet a copied one or a new one? This macro will add a sheet and enter the formula into the last cell in Summary Sheet column A and copies across to column G Sub add_copy() Dim newsheet As Worksheet Set newsheet = Sheets.Add Set rng = Sheets("Summary").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "=" & newsheet.Name & "!A1" Sheets("Summary").Range(rng.Address & ":G" & rng.Row).FillRight End Sub Gord Dibben MS Excel MVP On Thu, 6 Dec 2007 13:18:03 -0800, Todd wrote: I'm creating a spreadsheet that will have several worksheets added over time. i.e. a new worksheet for each new customer. All of the individual worksheets will have the customers information added to it when it is received. There will be a summary sheet added to create a master list of all customers and their basic info...Name, Location, Contact Name....etc. How would I write a simple macro that will take the customer basic info and add it to the summary sheet each time a new worksheet is added? Basically just a macro that will enter the formula ='Customer (1)'!A1 into the summary sheet from the customer sheet. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to link worksheets
Hi Gord,
1) You are correct. When I add a new customer sheet, initially there will be no information, it will have to be added manually. After I have entered all the relevant info, then I would run this macro to update the summary sheet. 2) The new sheet added will be a copied sheet, and just require the new customer info to replace the existing info from the copied sheet. Each new sheet, will have exactly the same format. I will try the macro you provided and let you know. Thank you! "Gord Dibben" wrote: Couple of questions Todd. When you add a new sheet there will be no customer information in it so what is there to add to the summary sheet? Is the added sheet a copied one or a new one? This macro will add a sheet and enter the formula into the last cell in Summary Sheet column A and copies across to column G Sub add_copy() Dim newsheet As Worksheet Set newsheet = Sheets.Add Set rng = Sheets("Summary").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "=" & newsheet.Name & "!A1" Sheets("Summary").Range(rng.Address & ":G" & rng.Row).FillRight End Sub Gord Dibben MS Excel MVP On Thu, 6 Dec 2007 13:18:03 -0800, Todd wrote: I'm creating a spreadsheet that will have several worksheets added over time. i.e. a new worksheet for each new customer. All of the individual worksheets will have the customers information added to it when it is received. There will be a summary sheet added to create a master list of all customers and their basic info...Name, Location, Contact Name....etc. How would I write a simple macro that will take the customer basic info and add it to the summary sheet each time a new worksheet is added? Basically just a macro that will enter the formula ='Customer (1)'!A1 into the summary sheet from the customer sheet. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to link worksheets
If all you're doing is copying an existing sheet and changing the data you don't
need my macro which Inserts a new blank sheet. You could use part of the macro after you have copied the sheet and entered the new data. Sub add_copy() Dim rng As Range Set rng = Sheets("Summary").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "='" & ActiveSheet.Name & "'!A1" Sheets("Summary").Range(rng.Address & ":G" & rng.Row).FillRight End Sub Run the macro with the copied sheet as the active sheet. Gord On Fri, 7 Dec 2007 05:51:01 -0800, Todd wrote: Hi Gord, 1) You are correct. When I add a new customer sheet, initially there will be no information, it will have to be added manually. After I have entered all the relevant info, then I would run this macro to update the summary sheet. 2) The new sheet added will be a copied sheet, and just require the new customer info to replace the existing info from the copied sheet. Each new sheet, will have exactly the same format. I will try the macro you provided and let you know. Thank you! "Gord Dibben" wrote: Couple of questions Todd. When you add a new sheet there will be no customer information in it so what is there to add to the summary sheet? Is the added sheet a copied one or a new one? This macro will add a sheet and enter the formula into the last cell in Summary Sheet column A and copies across to column G Sub add_copy() Dim newsheet As Worksheet Set newsheet = Sheets.Add Set rng = Sheets("Summary").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "=" & newsheet.Name & "!A1" Sheets("Summary").Range(rng.Address & ":G" & rng.Row).FillRight End Sub Gord Dibben MS Excel MVP On Thu, 6 Dec 2007 13:18:03 -0800, Todd wrote: I'm creating a spreadsheet that will have several worksheets added over time. i.e. a new worksheet for each new customer. All of the individual worksheets will have the customers information added to it when it is received. There will be a summary sheet added to create a master list of all customers and their basic info...Name, Location, Contact Name....etc. How would I write a simple macro that will take the customer basic info and add it to the summary sheet each time a new worksheet is added? Basically just a macro that will enter the formula ='Customer (1)'!A1 into the summary sheet from the customer sheet. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to link worksheets
Thanks again, Gord. I'm really close to getting this to work.
However, the macro is not copying the information from the customer sheet into the summary sheet. it merely duplicates the headings of the summary sheet to the line below. how can i modify this to grab the newly created customer information? "Gord Dibben" wrote: If all you're doing is copying an existing sheet and changing the data you don't need my macro which Inserts a new blank sheet. You could use part of the macro after you have copied the sheet and entered the new data. Sub add_copy() Dim rng As Range Set rng = Sheets("Summary").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "='" & ActiveSheet.Name & "'!A1" Sheets("Summary").Range(rng.Address & ":G" & rng.Row).FillRight End Sub Run the macro with the copied sheet as the active sheet. Gord On Fri, 7 Dec 2007 05:51:01 -0800, Todd wrote: Hi Gord, 1) You are correct. When I add a new customer sheet, initially there will be no information, it will have to be added manually. After I have entered all the relevant info, then I would run this macro to update the summary sheet. 2) The new sheet added will be a copied sheet, and just require the new customer info to replace the existing info from the copied sheet. Each new sheet, will have exactly the same format. I will try the macro you provided and let you know. Thank you! "Gord Dibben" wrote: Couple of questions Todd. When you add a new sheet there will be no customer information in it so what is there to add to the summary sheet? Is the added sheet a copied one or a new one? This macro will add a sheet and enter the formula into the last cell in Summary Sheet column A and copies across to column G Sub add_copy() Dim newsheet As Worksheet Set newsheet = Sheets.Add Set rng = Sheets("Summary").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "=" & newsheet.Name & "!A1" Sheets("Summary").Range(rng.Address & ":G" & rng.Row).FillRight End Sub Gord Dibben MS Excel MVP On Thu, 6 Dec 2007 13:18:03 -0800, Todd wrote: I'm creating a spreadsheet that will have several worksheets added over time. i.e. a new worksheet for each new customer. All of the individual worksheets will have the customers information added to it when it is received. There will be a summary sheet added to create a master list of all customers and their basic info...Name, Location, Contact Name....etc. How would I write a simple macro that will take the customer basic info and add it to the summary sheet each time a new worksheet is added? Basically just a macro that will enter the formula ='Customer (1)'!A1 into the summary sheet from the customer sheet. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to link worksheets
What range on the copied sheet contains the customer info?
You probably have titles in Row1 of new sheet which you are seeing. You originally asked for a formula of =Customer(2)!A1 to be copied to the Summary sheet. I extended it to A1:G1 Change the A1 in the macro to some other cell address like A2 Gord On Fri, 7 Dec 2007 09:10:03 -0800, Todd wrote: Thanks again, Gord. I'm really close to getting this to work. However, the macro is not copying the information from the customer sheet into the summary sheet. it merely duplicates the headings of the summary sheet to the line below. how can i modify this to grab the newly created customer information? "Gord Dibben" wrote: If all you're doing is copying an existing sheet and changing the data you don't need my macro which Inserts a new blank sheet. You could use part of the macro after you have copied the sheet and entered the new data. Sub add_copy() Dim rng As Range Set rng = Sheets("Summary").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "='" & ActiveSheet.Name & "'!A1" Sheets("Summary").Range(rng.Address & ":G" & rng.Row).FillRight End Sub Run the macro with the copied sheet as the active sheet. Gord On Fri, 7 Dec 2007 05:51:01 -0800, Todd wrote: Hi Gord, 1) You are correct. When I add a new customer sheet, initially there will be no information, it will have to be added manually. After I have entered all the relevant info, then I would run this macro to update the summary sheet. 2) The new sheet added will be a copied sheet, and just require the new customer info to replace the existing info from the copied sheet. Each new sheet, will have exactly the same format. I will try the macro you provided and let you know. Thank you! "Gord Dibben" wrote: Couple of questions Todd. When you add a new sheet there will be no customer information in it so what is there to add to the summary sheet? Is the added sheet a copied one or a new one? This macro will add a sheet and enter the formula into the last cell in Summary Sheet column A and copies across to column G Sub add_copy() Dim newsheet As Worksheet Set newsheet = Sheets.Add Set rng = Sheets("Summary").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "=" & newsheet.Name & "!A1" Sheets("Summary").Range(rng.Address & ":G" & rng.Row).FillRight End Sub Gord Dibben MS Excel MVP On Thu, 6 Dec 2007 13:18:03 -0800, Todd wrote: I'm creating a spreadsheet that will have several worksheets added over time. i.e. a new worksheet for each new customer. All of the individual worksheets will have the customers information added to it when it is received. There will be a summary sheet added to create a master list of all customers and their basic info...Name, Location, Contact Name....etc. How would I write a simple macro that will take the customer basic info and add it to the summary sheet each time a new worksheet is added? Basically just a macro that will enter the formula ='Customer (1)'!A1 into the summary sheet from the customer sheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
link to other worksheets | Excel Discussion (Misc queries) | |||
Link worksheets | Excel Worksheet Functions | |||
How do I Link two worksheets? | Excel Worksheet Functions | |||
Link Worksheets | Excel Worksheet Functions | |||
Can you link 2 worksheets together? | Excel Discussion (Misc queries) |