Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto creating an idex sheet when adding formatted sheets
I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets
with similar format. The top row a1 to a10 has cells with heading . I add new this similar sheets continuously with changing data in b1 to b10. Now can I make sheet1 as index of those sheets and how to transfer pasting the contents of b1 to b10 data of every sheet into the row cells of sheet1 automatically as and when the sheets I add? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto creating an idex sheet when adding formatted sheets
Assume your source sheets are simply named as numbers: 1,2,3,... and you want
to retrieve the contents in cells B1, B2, B3, ... from each sheet into a summary sheet In your summary sheet, List the source sheetnames in B1 across, eg: 1,2,3, ... List the cell references in A2 down, eg: B1, B2, ... Then place in B2: =INDIRECT("'"&B$1&"'!"&$A2) Copy B2 across/fill down to populate the contents from all the source sheets If you need it with an error trap to return neat looking blanks ("") for any source sheets not existing as yet, you could use instead in B2: =IF(ISERROR(INDIRECT("'"&B$1&"'!"&$A2)),"",INDIREC T("'"&B$1&"'!"&$A2)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Narnimar" wrote: I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets with similar format. The top row a1 to a10 has cells with heading . I add new this similar sheets continuously with changing data in b1 to b10. Now can I make sheet1 as index of those sheets and how to transfer pasting the contents of b1 to b10 data of every sheet into the row cells of sheet1 automatically as and when the sheets I add? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto creating an idex sheet when adding formatted sheets
I am confused by your statement "The top row a1 to a10 has cells with heading"
A1:A10 is a one column range, not the top row. Where exactly are your headings located? And where exactly is the data located? Gord Dibben MS Excel MVP On Sat, 8 Dec 2007 09:20:01 -0800, Narnimar wrote: I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets with similar format. The top row a1 to a10 has cells with heading . I add new this similar sheets continuously with changing data in b1 to b10. Now can I make sheet1 as index of those sheets and how to transfer pasting the contents of b1 to b10 data of every sheet into the row cells of sheet1 automatically as and when the sheets I add? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto creating an idex sheet when adding formatted sheets
Dear Gord Dibben, Sorry for my wrong statement. I mean the Heading row A1 to J1 and the data will be in row A2 to J2. Thanks for your help. "Gord Dibben" wrote: I am confused by your statement "The top row a1 to a10 has cells with heading" A1:A10 is a one column range, not the top row. Where exactly are your headings located? And where exactly is the data located? Gord Dibben MS Excel MVP On Sat, 8 Dec 2007 09:20:01 -0800, Narnimar wrote: I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets with similar format. The top row a1 to a10 has cells with heading . I add new this similar sheets continuously with changing data in b1 to b10. Now can I make sheet1 as index of those sheets and how to transfer pasting the contents of b1 to b10 data of every sheet into the row cells of sheet1 automatically as and when the sheets I add? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto creating an idex sheet when adding formatted sheets
Sorry Max for my wrong statement. I mean the Heading row A1 to J1 and the data will be in row A2 to J2. Thanks for your further help. "Narnimar" wrote: I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets with similar format. The top row a1 to a10 has cells with heading . I add new this similar sheets continuously with changing data in b1 to b10. Now can I make sheet1 as index of those sheets and how to transfer pasting the contents of b1 to b10 data of every sheet into the row cells of sheet1 automatically as and when the sheets I add? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto creating an idex sheet when adding formatted sheets
Do you want the new sheet's A2:J2 linked to the Index sheet or just copied over?
To link, run this macro after you have made the new sheet and entered data in A2:J2 The macro will add the links to the first blank row below existing data on Index sheet Sub linkit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "='" & ActiveSheet.Name & "'!A2" Sheets("Index").Range(rng.Address & ":J" & rng.Row).FillRight End Sub To copy, run this macro to copy contents to first blank row. Sub copyit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) ActiveSheet.Range("A2:J2").Copy Destination:=rng End Sub Gord On Sun, 9 Dec 2007 08:15:01 -0800, Narnimar wrote: Dear Gord Dibben, Sorry for my wrong statement. I mean the Heading row A1 to J1 and the data will be in row A2 to J2. Thanks for your help. "Gord Dibben" wrote: I am confused by your statement "The top row a1 to a10 has cells with heading" A1:A10 is a one column range, not the top row. Where exactly are your headings located? And where exactly is the data located? Gord Dibben MS Excel MVP On Sat, 8 Dec 2007 09:20:01 -0800, Narnimar wrote: I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets with similar format. The top row a1 to a10 has cells with heading . I add new this similar sheets continuously with changing data in b1 to b10. Now can I make sheet1 as index of those sheets and how to transfer pasting the contents of b1 to b10 data of every sheet into the row cells of sheet1 automatically as and when the sheets I add? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto creating an idex sheet when adding formatted sheets
Thanks for the quick reply. I have checked it, it links the data of A to J
of the same sheet (named Index). But I need this sheet is to be an index sheet. The data in the A2 to J2 of the other sheets (named 1, 2, 3,...n no. of sheets)I add continuously need to be copied or linked. Your further assistance is highly appreciated. Thanks. "Gord Dibben" wrote: Do you want the new sheet's A2:J2 linked to the Index sheet or just copied over? To link, run this macro after you have made the new sheet and entered data in A2:J2 The macro will add the links to the first blank row below existing data on Index sheet Sub linkit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "='" & ActiveSheet.Name & "'!A2" Sheets("Index").Range(rng.Address & ":J" & rng.Row).FillRight End Sub To copy, run this macro to copy contents to first blank row. Sub copyit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) ActiveSheet.Range("A2:J2").Copy Destination:=rng End Sub Gord On Sun, 9 Dec 2007 08:15:01 -0800, Narnimar wrote: Dear Gord Dibben, Sorry for my wrong statement. I mean the Heading row A1 to J1 and the data will be in row A2 to J2. Thanks for your help. "Gord Dibben" wrote: I am confused by your statement "The top row a1 to a10 has cells with heading" A1:A10 is a one column range, not the top row. Where exactly are your headings located? And where exactly is the data located? Gord Dibben MS Excel MVP On Sat, 8 Dec 2007 09:20:01 -0800, Narnimar wrote: I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets with similar format. The top row a1 to a10 has cells with heading . I add new this similar sheets continuously with changing data in b1 to b10. Now can I make sheet1 as index of those sheets and how to transfer pasting the contents of b1 to b10 data of every sheet into the row cells of sheet1 automatically as and when the sheets I add? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto creating an idex sheet when adding formatted sheets
Don't run either macro from Index sheet.
Run while the sheet you just added is the ActiveSheet Gord On Sun, 9 Dec 2007 12:31:00 -0800, Narnimar wrote: Thanks for the quick reply. I have checked it, it links the data of A to J of the same sheet (named Index). But I need this sheet is to be an index sheet. The data in the A2 to J2 of the other sheets (named 1, 2, 3,...n no. of sheets)I add continuously need to be copied or linked. Your further assistance is highly appreciated. Thanks. "Gord Dibben" wrote: Do you want the new sheet's A2:J2 linked to the Index sheet or just copied over? To link, run this macro after you have made the new sheet and entered data in A2:J2 The macro will add the links to the first blank row below existing data on Index sheet Sub linkit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "='" & ActiveSheet.Name & "'!A2" Sheets("Index").Range(rng.Address & ":J" & rng.Row).FillRight End Sub To copy, run this macro to copy contents to first blank row. Sub copyit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) ActiveSheet.Range("A2:J2").Copy Destination:=rng End Sub Gord On Sun, 9 Dec 2007 08:15:01 -0800, Narnimar wrote: Dear Gord Dibben, Sorry for my wrong statement. I mean the Heading row A1 to J1 and the data will be in row A2 to J2. Thanks for your help. "Gord Dibben" wrote: I am confused by your statement "The top row a1 to a10 has cells with heading" A1:A10 is a one column range, not the top row. Where exactly are your headings located? And where exactly is the data located? Gord Dibben MS Excel MVP On Sat, 8 Dec 2007 09:20:01 -0800, Narnimar wrote: I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets with similar format. The top row a1 to a10 has cells with heading . I add new this similar sheets continuously with changing data in b1 to b10. Now can I make sheet1 as index of those sheets and how to transfer pasting the contents of b1 to b10 data of every sheet into the row cells of sheet1 automatically as and when the sheets I add? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto creating an idex sheet when adding formatted sheets
No problem with this revised spec:
.. the data will be in row A2 to J2. as the suggestion to use indirect is easily adaptable Just change the line in my response: List the cell references in A2 down, eg: B1, B2, ... to read as: List the cell references in A2 down, eg: A2, B2, C2, ... J2 Then use the same formula suggested in B2, viz, either: =INDIRECT("'"&B$1&"'!"&$A2) or =IF(ISERROR(INDIRECT("'"&B$1&"'!"&$A2)),"",INDIREC T("'"&B$1&"'!"&$A2)) and copy B2 across/fill down to populate the contents from all the source sheets -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto creating an idex sheet when adding formatted sheets
Gord Dibben,
I got the point. I prefer your cell linking macro. But it adds up same data if I run it repeatedly. Can you do further some modification in the macro so that it does not add up rows again which are already updated once from the sheets? Thanks. "Gord Dibben" wrote: Don't run either macro from Index sheet. Run while the sheet you just added is the ActiveSheet Gord On Sun, 9 Dec 2007 12:31:00 -0800, Narnimar wrote: Thanks for the quick reply. I have checked it, it links the data of A to J of the same sheet (named Index). But I need this sheet is to be an index sheet. The data in the A2 to J2 of the other sheets (named 1, 2, 3,...n no. of sheets)I add continuously need to be copied or linked. Your further assistance is highly appreciated. Thanks. "Gord Dibben" wrote: Do you want the new sheet's A2:J2 linked to the Index sheet or just copied over? To link, run this macro after you have made the new sheet and entered data in A2:J2 The macro will add the links to the first blank row below existing data on Index sheet Sub linkit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "='" & ActiveSheet.Name & "'!A2" Sheets("Index").Range(rng.Address & ":J" & rng.Row).FillRight End Sub To copy, run this macro to copy contents to first blank row. Sub copyit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) ActiveSheet.Range("A2:J2").Copy Destination:=rng End Sub Gord On Sun, 9 Dec 2007 08:15:01 -0800, Narnimar wrote: Dear Gord Dibben, Sorry for my wrong statement. I mean the Heading row A1 to J1 and the data will be in row A2 to J2. Thanks for your help. "Gord Dibben" wrote: I am confused by your statement "The top row a1 to a10 has cells with heading" A1:A10 is a one column range, not the top row. Where exactly are your headings located? And where exactly is the data located? Gord Dibben MS Excel MVP On Sat, 8 Dec 2007 09:20:01 -0800, Narnimar wrote: I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets with similar format. The top row a1 to a10 has cells with heading . I add new this similar sheets continuously with changing data in b1 to b10. Now can I make sheet1 as index of those sheets and how to transfer pasting the contents of b1 to b10 data of every sheet into the row cells of sheet1 automatically as and when the sheets I add? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto creating an idex sheet when adding formatted sheets
The cell linking macro has a hard-coded cell address that you originally asked
for. ActiveSheet.Name & "'!A2" If you run the macro repeatedly from one sheet it will keep repeating the same range A2:J2 each time. What do you want to happen from each sheet that you run the macro on? Loop down to next row A3:J3? Same for all the other sheets? How many possible sheets? How many rows on each sheet are you talking about? What order do you want the sheets' data placed in the Index sheet? Leaving out much detail in original posting always leads to this type of exchange. Gord On Mon, 10 Dec 2007 11:36:02 -0800, Narnimar wrote: Gord Dibben, I got the point. I prefer your cell linking macro. But it adds up same data if I run it repeatedly. Can you do further some modification in the macro so that it does not add up rows again which are already updated once from the sheets? Thanks. "Gord Dibben" wrote: Don't run either macro from Index sheet. Run while the sheet you just added is the ActiveSheet Gord On Sun, 9 Dec 2007 12:31:00 -0800, Narnimar wrote: Thanks for the quick reply. I have checked it, it links the data of A to J of the same sheet (named Index). But I need this sheet is to be an index sheet. The data in the A2 to J2 of the other sheets (named 1, 2, 3,...n no. of sheets)I add continuously need to be copied or linked. Your further assistance is highly appreciated. Thanks. "Gord Dibben" wrote: Do you want the new sheet's A2:J2 linked to the Index sheet or just copied over? To link, run this macro after you have made the new sheet and entered data in A2:J2 The macro will add the links to the first blank row below existing data on Index sheet Sub linkit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "='" & ActiveSheet.Name & "'!A2" Sheets("Index").Range(rng.Address & ":J" & rng.Row).FillRight End Sub To copy, run this macro to copy contents to first blank row. Sub copyit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) ActiveSheet.Range("A2:J2").Copy Destination:=rng End Sub Gord On Sun, 9 Dec 2007 08:15:01 -0800, Narnimar wrote: Dear Gord Dibben, Sorry for my wrong statement. I mean the Heading row A1 to J1 and the data will be in row A2 to J2. Thanks for your help. "Gord Dibben" wrote: I am confused by your statement "The top row a1 to a10 has cells with heading" A1:A10 is a one column range, not the top row. Where exactly are your headings located? And where exactly is the data located? Gord Dibben MS Excel MVP On Sat, 8 Dec 2007 09:20:01 -0800, Narnimar wrote: I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets with similar format. The top row a1 to a10 has cells with heading . I add new this similar sheets continuously with changing data in b1 to b10. Now can I make sheet1 as index of those sheets and how to transfer pasting the contents of b1 to b10 data of every sheet into the row cells of sheet1 automatically as and when the sheets I add? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto creating an idex sheet when adding formatted sheets
Gord Dibben In the sheets from A1 to J2 have the main data like S. NO ORDER NO DATE APRX. ETD PAYMENT VALUE PARTY SECTION these datas are getting summarized in the index sheet when macro runs which is perfect only as per I wanted. Only my worry is that to prevent the chances of duplicate data which may sit in the index sheet if I run the macro again by mistake while updating in any of the sheets. If no repetition allowed my purpose is served. Thanks. "Gord Dibben" wrote: The cell linking macro has a hard-coded cell address that you originally asked for. ActiveSheet.Name & "'!A2" If you run the macro repeatedly from one sheet it will keep repeating the same range A2:J2 each time. What do you want to happen from each sheet that you run the macro on? Loop down to next row A3:J3? Same for all the other sheets? How many possible sheets? How many rows on each sheet are you talking about? What order do you want the sheets' data placed in the Index sheet? Leaving out much detail in original posting always leads to this type of exchange. Gord On Mon, 10 Dec 2007 11:36:02 -0800, Narnimar wrote: Gord Dibben, I got the point. I prefer your cell linking macro. But it adds up same data if I run it repeatedly. Can you do further some modification in the macro so that it does not add up rows again which are already updated once from the sheets? Thanks. "Gord Dibben" wrote: Don't run either macro from Index sheet. Run while the sheet you just added is the ActiveSheet Gord On Sun, 9 Dec 2007 12:31:00 -0800, Narnimar wrote: Thanks for the quick reply. I have checked it, it links the data of A to J of the same sheet (named Index). But I need this sheet is to be an index sheet. The data in the A2 to J2 of the other sheets (named 1, 2, 3,...n no. of sheets)I add continuously need to be copied or linked. Your further assistance is highly appreciated. Thanks. "Gord Dibben" wrote: Do you want the new sheet's A2:J2 linked to the Index sheet or just copied over? To link, run this macro after you have made the new sheet and entered data in A2:J2 The macro will add the links to the first blank row below existing data on Index sheet Sub linkit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "='" & ActiveSheet.Name & "'!A2" Sheets("Index").Range(rng.Address & ":J" & rng.Row).FillRight End Sub To copy, run this macro to copy contents to first blank row. Sub copyit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) ActiveSheet.Range("A2:J2").Copy Destination:=rng End Sub Gord On Sun, 9 Dec 2007 08:15:01 -0800, Narnimar wrote: Dear Gord Dibben, Sorry for my wrong statement. I mean the Heading row A1 to J1 and the data will be in row A2 to J2. Thanks for your help. "Gord Dibben" wrote: I am confused by your statement "The top row a1 to a10 has cells with heading" A1:A10 is a one column range, not the top row. Where exactly are your headings located? And where exactly is the data located? Gord Dibben MS Excel MVP On Sat, 8 Dec 2007 09:20:01 -0800, Narnimar wrote: I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets with similar format. The top row a1 to a10 has cells with heading . I add new this similar sheets continuously with changing data in b1 to b10. Now can I make sheet1 as index of those sheets and how to transfer pasting the contents of b1 to b10 data of every sheet into the row cells of sheet1 automatically as and when the sheets I add? |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto creating an idex sheet when adding formatted sheets
What you would like is to make sure the macro cannot get run from the same sheet
twice. I will have to think on that for a while. Gord On Mon, 10 Dec 2007 13:31:00 -0800, Narnimar wrote: Gord Dibben In the sheets from A1 to J2 have the main data like S. NO ORDER NO DATE APRX. ETD PAYMENT VALUE PARTY SECTION these datas are getting summarized in the index sheet when macro runs which is perfect only as per I wanted. Only my worry is that to prevent the chances of duplicate data which may sit in the index sheet if I run the macro again by mistake while updating in any of the sheets. If no repetition allowed my purpose is served. Thanks. "Gord Dibben" wrote: The cell linking macro has a hard-coded cell address that you originally asked for. ActiveSheet.Name & "'!A2" If you run the macro repeatedly from one sheet it will keep repeating the same range A2:J2 each time. What do you want to happen from each sheet that you run the macro on? Loop down to next row A3:J3? Same for all the other sheets? How many possible sheets? How many rows on each sheet are you talking about? What order do you want the sheets' data placed in the Index sheet? Leaving out much detail in original posting always leads to this type of exchange. Gord On Mon, 10 Dec 2007 11:36:02 -0800, Narnimar wrote: Gord Dibben, I got the point. I prefer your cell linking macro. But it adds up same data if I run it repeatedly. Can you do further some modification in the macro so that it does not add up rows again which are already updated once from the sheets? Thanks. "Gord Dibben" wrote: Don't run either macro from Index sheet. Run while the sheet you just added is the ActiveSheet Gord On Sun, 9 Dec 2007 12:31:00 -0800, Narnimar wrote: Thanks for the quick reply. I have checked it, it links the data of A to J of the same sheet (named Index). But I need this sheet is to be an index sheet. The data in the A2 to J2 of the other sheets (named 1, 2, 3,...n no. of sheets)I add continuously need to be copied or linked. Your further assistance is highly appreciated. Thanks. "Gord Dibben" wrote: Do you want the new sheet's A2:J2 linked to the Index sheet or just copied over? To link, run this macro after you have made the new sheet and entered data in A2:J2 The macro will add the links to the first blank row below existing data on Index sheet Sub linkit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "='" & ActiveSheet.Name & "'!A2" Sheets("Index").Range(rng.Address & ":J" & rng.Row).FillRight End Sub To copy, run this macro to copy contents to first blank row. Sub copyit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) ActiveSheet.Range("A2:J2").Copy Destination:=rng End Sub Gord On Sun, 9 Dec 2007 08:15:01 -0800, Narnimar wrote: Dear Gord Dibben, Sorry for my wrong statement. I mean the Heading row A1 to J1 and the data will be in row A2 to J2. Thanks for your help. "Gord Dibben" wrote: I am confused by your statement "The top row a1 to a10 has cells with heading" A1:A10 is a one column range, not the top row. Where exactly are your headings located? And where exactly is the data located? Gord Dibben MS Excel MVP On Sat, 8 Dec 2007 09:20:01 -0800, Narnimar wrote: I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets with similar format. The top row a1 to a10 has cells with heading . I add new this similar sheets continuously with changing data in b1 to b10. Now can I make sheet1 as index of those sheets and how to transfer pasting the contents of b1 to b10 data of every sheet into the row cells of sheet1 automatically as and when the sheets I add? |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto creating an idex sheet when adding formatted sheets
Try this revision.
If the activesheet is already linked on Index sheet, the macro will quit. Sub Linkit() Dim whatname As String Dim rng As Range whatname = ActiveSheet.Name If ActiveSheet.Name = "Index" Then MsgBox "Do not run from Index sheet. Try again" Exit Sub End If With Worksheets("Index").Cells Set c = .Find(whatname, LookIn:=xlFormulas, LookAt:=xlPart) If Not c Is Nothing Then Exit Sub End With Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "='" & ActiveSheet.Name & "'!A2" Sheets("Index").Range(rng.Address & ":J" & rng.Row).FillRight End Sub Gord On Mon, 10 Dec 2007 15:22:53 -0800, Gord Dibben <gorddibbATshawDOTca wrote: What you would like is to make sure the macro cannot get run from the same sheet twice. I will have to think on that for a while. Gord On Mon, 10 Dec 2007 13:31:00 -0800, Narnimar wrote: Gord Dibben In the sheets from A1 to J2 have the main data like S. NO ORDER NO DATE APRX. ETD PAYMENT VALUE PARTY SECTION these datas are getting summarized in the index sheet when macro runs which is perfect only as per I wanted. Only my worry is that to prevent the chances of duplicate data which may sit in the index sheet if I run the macro again by mistake while updating in any of the sheets. If no repetition allowed my purpose is served. Thanks. "Gord Dibben" wrote: The cell linking macro has a hard-coded cell address that you originally asked for. ActiveSheet.Name & "'!A2" If you run the macro repeatedly from one sheet it will keep repeating the same range A2:J2 each time. What do you want to happen from each sheet that you run the macro on? Loop down to next row A3:J3? Same for all the other sheets? How many possible sheets? How many rows on each sheet are you talking about? What order do you want the sheets' data placed in the Index sheet? Leaving out much detail in original posting always leads to this type of exchange. Gord On Mon, 10 Dec 2007 11:36:02 -0800, Narnimar wrote: Gord Dibben, I got the point. I prefer your cell linking macro. But it adds up same data if I run it repeatedly. Can you do further some modification in the macro so that it does not add up rows again which are already updated once from the sheets? Thanks. "Gord Dibben" wrote: Don't run either macro from Index sheet. Run while the sheet you just added is the ActiveSheet Gord On Sun, 9 Dec 2007 12:31:00 -0800, Narnimar wrote: Thanks for the quick reply. I have checked it, it links the data of A to J of the same sheet (named Index). But I need this sheet is to be an index sheet. The data in the A2 to J2 of the other sheets (named 1, 2, 3,...n no. of sheets)I add continuously need to be copied or linked. Your further assistance is highly appreciated. Thanks. "Gord Dibben" wrote: Do you want the new sheet's A2:J2 linked to the Index sheet or just copied over? To link, run this macro after you have made the new sheet and entered data in A2:J2 The macro will add the links to the first blank row below existing data on Index sheet Sub linkit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "='" & ActiveSheet.Name & "'!A2" Sheets("Index").Range(rng.Address & ":J" & rng.Row).FillRight End Sub To copy, run this macro to copy contents to first blank row. Sub copyit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) ActiveSheet.Range("A2:J2").Copy Destination:=rng End Sub Gord On Sun, 9 Dec 2007 08:15:01 -0800, Narnimar wrote: Dear Gord Dibben, Sorry for my wrong statement. I mean the Heading row A1 to J1 and the data will be in row A2 to J2. Thanks for your help. "Gord Dibben" wrote: I am confused by your statement "The top row a1 to a10 has cells with heading" A1:A10 is a one column range, not the top row. Where exactly are your headings located? And where exactly is the data located? Gord Dibben MS Excel MVP On Sat, 8 Dec 2007 09:20:01 -0800, Narnimar wrote: I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets with similar format. The top row a1 to a10 has cells with heading . I add new this similar sheets continuously with changing data in b1 to b10. Now can I make sheet1 as index of those sheets and how to transfer pasting the contents of b1 to b10 data of every sheet into the row cells of sheet1 automatically as and when the sheets I add? |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto creating an idex sheet when adding formatted sheets
Dear Gord Dibben,
It was working and all cells were filling when run the macro. But unexpectedly it fills only leftmost cell. I re pasted the macro but no luck. What would be the reason? Thanks. "Gord Dibben" wrote: Try this revision. If the activesheet is already linked on Index sheet, the macro will quit. Sub Linkit() Dim whatname As String Dim rng As Range whatname = ActiveSheet.Name If ActiveSheet.Name = "Index" Then MsgBox "Do not run from Index sheet. Try again" Exit Sub End If With Worksheets("Index").Cells Set c = .Find(whatname, LookIn:=xlFormulas, LookAt:=xlPart) If Not c Is Nothing Then Exit Sub End With Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "='" & ActiveSheet.Name & "'!A2" Sheets("Index").Range(rng.Address & ":J" & rng.Row).FillRight End Sub Gord On Mon, 10 Dec 2007 15:22:53 -0800, Gord Dibben <gorddibbATshawDOTca wrote: What you would like is to make sure the macro cannot get run from the same sheet twice. I will have to think on that for a while. Gord On Mon, 10 Dec 2007 13:31:00 -0800, Narnimar wrote: Gord Dibben In the sheets from A1 to J2 have the main data like S. NO ORDER NO DATE APRX. ETD PAYMENT VALUE PARTY SECTION these datas are getting summarized in the index sheet when macro runs which is perfect only as per I wanted. Only my worry is that to prevent the chances of duplicate data which may sit in the index sheet if I run the macro again by mistake while updating in any of the sheets. If no repetition allowed my purpose is served. Thanks. "Gord Dibben" wrote: The cell linking macro has a hard-coded cell address that you originally asked for. ActiveSheet.Name & "'!A2" If you run the macro repeatedly from one sheet it will keep repeating the same range A2:J2 each time. What do you want to happen from each sheet that you run the macro on? Loop down to next row A3:J3? Same for all the other sheets? How many possible sheets? How many rows on each sheet are you talking about? What order do you want the sheets' data placed in the Index sheet? Leaving out much detail in original posting always leads to this type of exchange. Gord On Mon, 10 Dec 2007 11:36:02 -0800, Narnimar wrote: Gord Dibben, I got the point. I prefer your cell linking macro. But it adds up same data if I run it repeatedly. Can you do further some modification in the macro so that it does not add up rows again which are already updated once from the sheets? Thanks. "Gord Dibben" wrote: Don't run either macro from Index sheet. Run while the sheet you just added is the ActiveSheet Gord On Sun, 9 Dec 2007 12:31:00 -0800, Narnimar wrote: Thanks for the quick reply. I have checked it, it links the data of A to J of the same sheet (named Index). But I need this sheet is to be an index sheet. The data in the A2 to J2 of the other sheets (named 1, 2, 3,...n no. of sheets)I add continuously need to be copied or linked. Your further assistance is highly appreciated. Thanks. "Gord Dibben" wrote: Do you want the new sheet's A2:J2 linked to the Index sheet or just copied over? To link, run this macro after you have made the new sheet and entered data in A2:J2 The macro will add the links to the first blank row below existing data on Index sheet Sub linkit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "='" & ActiveSheet.Name & "'!A2" Sheets("Index").Range(rng.Address & ":J" & rng.Row).FillRight End Sub To copy, run this macro to copy contents to first blank row. Sub copyit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) ActiveSheet.Range("A2:J2").Copy Destination:=rng End Sub Gord On Sun, 9 Dec 2007 08:15:01 -0800, Narnimar wrote: Dear Gord Dibben, Sorry for my wrong statement. I mean the Heading row A1 to J1 and the data will be in row A2 to J2. Thanks for your help. "Gord Dibben" wrote: I am confused by your statement "The top row a1 to a10 has cells with heading" A1:A10 is a one column range, not the top row. Where exactly are your headings located? And where exactly is the data located? Gord Dibben MS Excel MVP On Sat, 8 Dec 2007 09:20:01 -0800, Narnimar wrote: I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets with similar format. The top row a1 to a10 has cells with heading . I add new this similar sheets continuously with changing data in b1 to b10. Now can I make sheet1 as index of those sheets and how to transfer pasting the contents of b1 to b10 data of every sheet into the row cells of sheet1 automatically as and when the sheets I add? |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto creating an idex sheet when adding formatted sheets
Works for me as written A2:J2 of each sheet is linked by formula to "Index"
sheet in next available row. If it was working for you but now has stopped, it is not the code. A change in your layout would be my guess. Gord On Tue, 8 Jan 2008 07:26:06 -0800, Narnimar wrote: Dear Gord Dibben, It was working and all cells were filling when run the macro. But unexpectedly it fills only leftmost cell. I re pasted the macro but no luck. What would be the reason? Thanks. "Gord Dibben" wrote: Try this revision. If the activesheet is already linked on Index sheet, the macro will quit. Sub Linkit() Dim whatname As String Dim rng As Range whatname = ActiveSheet.Name If ActiveSheet.Name = "Index" Then MsgBox "Do not run from Index sheet. Try again" Exit Sub End If With Worksheets("Index").Cells Set c = .Find(whatname, LookIn:=xlFormulas, LookAt:=xlPart) If Not c Is Nothing Then Exit Sub End With Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "='" & ActiveSheet.Name & "'!A2" Sheets("Index").Range(rng.Address & ":J" & rng.Row).FillRight End Sub Gord On Mon, 10 Dec 2007 15:22:53 -0800, Gord Dibben <gorddibbATshawDOTca wrote: What you would like is to make sure the macro cannot get run from the same sheet twice. I will have to think on that for a while. Gord On Mon, 10 Dec 2007 13:31:00 -0800, Narnimar wrote: Gord Dibben In the sheets from A1 to J2 have the main data like S. NO ORDER NO DATE APRX. ETD PAYMENT VALUE PARTY SECTION these datas are getting summarized in the index sheet when macro runs which is perfect only as per I wanted. Only my worry is that to prevent the chances of duplicate data which may sit in the index sheet if I run the macro again by mistake while updating in any of the sheets. If no repetition allowed my purpose is served. Thanks. "Gord Dibben" wrote: The cell linking macro has a hard-coded cell address that you originally asked for. ActiveSheet.Name & "'!A2" If you run the macro repeatedly from one sheet it will keep repeating the same range A2:J2 each time. What do you want to happen from each sheet that you run the macro on? Loop down to next row A3:J3? Same for all the other sheets? How many possible sheets? How many rows on each sheet are you talking about? What order do you want the sheets' data placed in the Index sheet? Leaving out much detail in original posting always leads to this type of exchange. Gord On Mon, 10 Dec 2007 11:36:02 -0800, Narnimar wrote: Gord Dibben, I got the point. I prefer your cell linking macro. But it adds up same data if I run it repeatedly. Can you do further some modification in the macro so that it does not add up rows again which are already updated once from the sheets? Thanks. "Gord Dibben" wrote: Don't run either macro from Index sheet. Run while the sheet you just added is the ActiveSheet Gord On Sun, 9 Dec 2007 12:31:00 -0800, Narnimar wrote: Thanks for the quick reply. I have checked it, it links the data of A to J of the same sheet (named Index). But I need this sheet is to be an index sheet. The data in the A2 to J2 of the other sheets (named 1, 2, 3,...n no. of sheets)I add continuously need to be copied or linked. Your further assistance is highly appreciated. Thanks. "Gord Dibben" wrote: Do you want the new sheet's A2:J2 linked to the Index sheet or just copied over? To link, run this macro after you have made the new sheet and entered data in A2:J2 The macro will add the links to the first blank row below existing data on Index sheet Sub linkit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) rng.Formula = "='" & ActiveSheet.Name & "'!A2" Sheets("Index").Range(rng.Address & ":J" & rng.Row).FillRight End Sub To copy, run this macro to copy contents to first blank row. Sub copyit() Dim rng As Range Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) ActiveSheet.Range("A2:J2").Copy Destination:=rng End Sub Gord On Sun, 9 Dec 2007 08:15:01 -0800, Narnimar wrote: Dear Gord Dibben, Sorry for my wrong statement. I mean the Heading row A1 to J1 and the data will be in row A2 to J2. Thanks for your help. "Gord Dibben" wrote: I am confused by your statement "The top row a1 to a10 has cells with heading" A1:A10 is a one column range, not the top row. Where exactly are your headings located? And where exactly is the data located? Gord Dibben MS Excel MVP On Sat, 8 Dec 2007 09:20:01 -0800, Narnimar wrote: I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets with similar format. The top row a1 to a10 has cells with heading . I add new this similar sheets continuously with changing data in b1 to b10. Now can I make sheet1 as index of those sheets and how to transfer pasting the contents of b1 to b10 data of every sheet into the row cells of sheet1 automatically as and when the sheets I add? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding sheets based upon a template sheet | Excel Worksheet Functions | |||
creating multiple sheets, then individualized workbooks for each sheet | Excel Discussion (Misc queries) | |||
Auto Fill Different Sheets same cell on each sheet? | Excel Discussion (Misc queries) | |||
Pivot Chart/Sheet comparisons -- adding or subtracting sheets | Excel Discussion (Misc queries) | |||
Auto link rows of information from multiple sheets to single sheet | Excel Discussion (Misc queries) |