Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assembling Data of 100 Sheets in one Summary Sheet
Hi
I have a Worksheet with 100 Sheets All the Sheet is named as 1,2,3,4,5,6,7,8,9,10......100 All the Sheets is having same format. All the Sheets i have Two Columns B & D Column B Labels Column D Text Box In Each Sheet we have 24 Labels & 24 Text Boxes (Column C = Labels & Column D = Keying area) Now I want is to develop a macro through which i can accumulate data in one sheet named as Summary. Which shows data as Form No| Client Name / Company| Location | Version 1 XYZ ABC India 1.1 2 WXY DEF USA 1.2 3 ZWX GHI Canada 1.3 I want to accumulate all the data of Sheet 1 to Sheet 100 present in the entire worksheet into a new Sheet Summary in the same workbook. How to do that. I am in desperate need of this. awaiting for a solution to this problem. Thanks Akash |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assembling Data of 100 Sheets in one Summary Sheet
OK, I understand this:
I have a Worksheet with 100 Sheets All the Sheet is named as 1,2,3,4,5,6,7,8,9,10......100 All the Sheets is having same format. All the Sheets i have Two Columns ...but B & D Column B Labels Column D Text Box In Each Sheet we have 24 Labels & 24 Text Boxes (Column C = Labels & Column D = Keying area) Controls on a worksheet are not IN cells, but on the layer above. As such column/row has little meaning regarding the control. You can access them through the OLEObjects collections (amongst other collections). OK: Now I want is to develop a macro through which i can accumulate data in one sheet named as Summary. ...but Form No| Client Name / Company| Location | Version 1 XYZ ABC India 1.1 2 WXY DEF USA 1.2 3 ZWX GHI Canada 1.3 How does this relate to the "24 Labels & 24 Text Boxes" on each sheet ? If you are using labels and TextBoxes, it may be easier not to. Just use cells that you can then copy/paste etc. NickHK "Akash" wrote in message ups.com... Hi I have a Worksheet with 100 Sheets All the Sheet is named as 1,2,3,4,5,6,7,8,9,10......100 All the Sheets is having same format. All the Sheets i have Two Columns B & D Column B Labels Column D Text Box In Each Sheet we have 24 Labels & 24 Text Boxes (Column C = Labels & Column D = Keying area) Now I want is to develop a macro through which i can accumulate data in one sheet named as Summary. Which shows data as Form No| Client Name / Company| Location | Version 1 XYZ ABC India 1.1 2 WXY DEF USA 1.2 3 ZWX GHI Canada 1.3 I want to accumulate all the data of Sheet 1 to Sheet 100 present in the entire worksheet into a new Sheet Summary in the same workbook. How to do that. I am in desperate need of this. awaiting for a solution to this problem. Thanks Akash |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assembling Data of 100 Sheets in one Summary Sheet
Hi Akash,
Like Nick I have a problem understanding the significance of your TextBox controls. Presumably, others experienced similar difficulty because your original post from 2 days ago does not appear to have received any response. To create a summary sheet containing the data from the 100 sheets, see Ron de Bruin's sample code at: Merge cells from all or some worksheets into one Master sheet http://www.rondebruin.nl/copy2.htm --- Regards, Norman "Akash" wrote in message ups.com... Hi I have a Worksheet with 100 Sheets All the Sheet is named as 1,2,3,4,5,6,7,8,9,10......100 All the Sheets is having same format. All the Sheets i have Two Columns B & D Column B Labels Column D Text Box In Each Sheet we have 24 Labels & 24 Text Boxes (Column C = Labels & Column D = Keying area) Now I want is to develop a macro through which i can accumulate data in one sheet named as Summary. Which shows data as Form No| Client Name / Company| Location | Version 1 XYZ ABC India 1.1 2 WXY DEF USA 1.2 3 ZWX GHI Canada 1.3 I want to accumulate all the data of Sheet 1 to Sheet 100 present in the entire worksheet into a new Sheet Summary in the same workbook. How to do that. I am in desperate need of this. awaiting for a solution to this problem. Thanks Akash |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assembling Data of 100 Sheets in one Summary Sheet
Hi Norman,
Based on your mail i viewed the site mentioned by you. I tried to copy this code and tried to run it. Sub Test1() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "MergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("MergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "MergeSheet" Set DestSh = ThisWorkbook.Worksheets.Add DestSh.Name = "MergeSheet" 'loop through all worksheets and copy the data to the DestSh For Each sh In ThisWorkbook.Worksheets If sh.Name < DestSh.Name Then Last = LastRow(DestSh) 'This example copies everything, if you only want to copy 'values/formats look at the example below this macro sh.Range("A1:l1").Copy DestSh.Cells(Last + 1, "A") 'This will copy the sheet name in the H column if you want DestSh.Cells(Last + 1, "H").Value = sh.Name End If Next Application.Goto DestSh.Cells(1) With Application .ScreenUpdating = True .EnableEvents = True End With End Sub But it showing me error as Compiler Error: Sub or Function not Defined. Last = LastRow(DestSh) What should i do in this regards. Awaiting for a resolution from your end. Thanks Akash On May 10, 12:58 pm, "Norman Jones" wrote: HiAkash, Like Nick I have a problem understanding the significance of your TextBox controls. Presumably, others experienced similar difficulty because your original post from 2 days ago does not appear to have received any response. To create a summary sheet containing the data from the 100 sheets, see Ron de Bruin's sample code at: Merge cells from all or some worksheets into one Master sheet http://www.rondebruin.nl/copy2.htm --- Regards, Norman "Akash" wrote in message ups.com... Hi I have a Worksheet with 100 Sheets All the Sheet is named as 1,2,3,4,5,6,7,8,9,10......100 All the Sheets is having same format. All the Sheets i have Two Columns B & D Column B Labels Column D Text Box In Each Sheet we have 24 Labels & 24 Text Boxes (Column C = Labels & Column D = Keying area) Now I want is to develop a macro through which i can accumulate data in one sheet named as Summary. Which shows data as Form No| Client Name / Company| Location | Version 1 XYZ ABC India 1.1 2 WXY DEF USA 1.2 3 ZWX GHI Canada 1.3 I want to accumulate all the data of Sheet 1 to Sheet 100 present in the entire worksheet into a new Sheet Summary in the same workbook. How to do that. I am in desperate need of this. awaiting for a solution to this problem. Thanks Akash- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assembling Data of 100 Sheets in one Summary Sheet
Hi Akash,
'---------------- Based on your mail i viewed the site mentioned by you. I tried to copy this code and tried to run it. [...] But it showing me error as Compiler Error: Sub or Function not Defined. Last = LastRow(DestSh) What should i do in this regards. Awaiting for a resolution from your end. [...] '---------------- If you look again at Ron's page, you will see: '----------------- Important: The macro examples use the functions that you can find in the last section of this page. '----------------- Therefore, try copying the function LastRow into your code module and then retry Ron's code. --- Regards, Norman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assembling Data of 100 Sheets in one Summary Sheet
On May 10, 6:57 pm, "Norman Jones"
wrote: HiAkash, '---------------- Based on your mail i viewed the site mentioned by you. I tried to copy this code and tried to run it. [...] But it showing me error as Compiler Error: Sub or Function not Defined. Last = LastRow(DestSh) What should i do in this regards. Awaiting for a resolution from your end. [...] '---------------- If you look again at Ron's page, you will see: '----------------- Important: The macro examples use the functions that you can find in the last section of this page. '----------------- Therefore, try copying the function LastRow into your code module and then retry Ron's code. --- Regards, Norman Hi Norman, Thanks for the Help but i dont want this. I want something else Norman. Let me tell u more precisely... I have data in the below mentioned cells. D10 D54 D12 D70 D14 D87 D20 D102 D22 D118 D24 D137 D30 D141 D32 D145 D48 D162 D50 D164 D52 D166 D168 All the 100 Sheets is having the data in these Cells only. Now what i want is to accumulate the data in one sheet named as Summary. Thanks Akash |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assembling Data of 100 Sheets in one Summary Sheet
Hi Akash,
'------------------ Thanks for the Help but i dont want this. I want something else Norman. Let me tell u more precisely... I have data in the below mentioned cells. D10 D54 D12 D70 D14 D87 D20 D102 D22 D118 D24 D137 D30 D141 D32 D145 D48 D162 D50 D164 D52 D166 D168 All the 100 Sheets is having the data in these Cells only. Now what i want is to accumulate the data in one sheet named as Summary. '------------------ Your use of 'accumulate' is unclear: is the data to be summed or copied? If the data is to bo copied, how do you want the copied data to be arranged? --- Regards, Norman |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assembling Data of 100 Sheets in one Summary Sheet
You will have explain more clearly, as this latest example is completely
different to you initial 24 labels/text and the headings Form No, Client Name / Company, Location, Version. NickHK "Akash" wrote in message ups.com... On May 10, 6:57 pm, "Norman Jones" wrote: HiAkash, '---------------- Based on your mail i viewed the site mentioned by you. I tried to copy this code and tried to run it. [...] But it showing me error as Compiler Error: Sub or Function not Defined. Last = LastRow(DestSh) What should i do in this regards. Awaiting for a resolution from your end. [...] '---------------- If you look again at Ron's page, you will see: '----------------- Important: The macro examples use the functions that you can find in the last section of this page. '----------------- Therefore, try copying the function LastRow into your code module and then retry Ron's code. --- Regards, Norman Hi Norman, Thanks for the Help but i dont want this. I want something else Norman. Let me tell u more precisely... I have data in the below mentioned cells. D10 D54 D12 D70 D14 D87 D20 D102 D22 D118 D24 D137 D30 D141 D32 D145 D48 D162 D50 D164 D52 D166 D168 All the 100 Sheets is having the data in these Cells only. Now what i want is to accumulate the data in one sheet named as Summary. Thanks Akash |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assembling Data of 100 Sheets in one Summary Sheet
Hi Nick/Norman,
all of my sheet have same format. I want to copy all the data present in the respective cell of Column D e.g. D10 D12 D14 D20 D22 D24 D30 D32 D48 D50 D52 D54 D70 D87 D102 D118 D137 D141 D145 D162 D164 D166 D168 I dont want to copy the formats.(ONLY VALUES) I only want all the values of the above mentioned Cells in Vertical position. This is the only requirement which i had asked for. Thanks Akash Maheshwari On May 11, 7:30 am, "NickHK" wrote: You will have explain more clearly, as this latest example is completely different to you initial 24 labels/text and the headings Form No, Client Name / Company, Location, Version. NickHK "Akash" wrote in message ups.com... On May 10, 6:57 pm, "Norman Jones" wrote: HiAkash, '---------------- Based on your mail i viewed the site mentioned by you. I tried to copy this code and tried to run it. [...] But it showing me error as Compiler Error: Sub or Function not Defined. Last = LastRow(DestSh) What should i do in this regards. Awaiting for a resolution from your end. [...] '---------------- If you look again at Ron's page, you will see: '----------------- Important: The macro examples use the functions that you can find in the last section of this page. '----------------- Therefore, try copying the function LastRow into your code module and then retry Ron's code. --- Regards, Norman Hi Norman, Thanks for the Help but i dont want this. I want something else Norman. Let me tell u more precisely... I have data in the below mentioned cells. D10 D54 D12 D70 D14 D87 D20 D102 D22 D118 D24 D137 D30 D141 D32 D145 D48 D162 D50 D164 D52 D166 D168 All the 100 Sheets is having the data in these Cells only. Now what i want is to accumulate the data in one sheet named as Summary. Thanks Akash- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to get sum from one 31 sheets to a summary sheet. | Excel Discussion (Misc queries) | |||
summary sheet across multiple sheets | Excel Discussion (Misc queries) | |||
Accumulating Data From Sheets and preparing one summary sheet. | Excel Programming | |||
Summary Sheet help with multiple sheets | Excel Discussion (Misc queries) | |||
data entered on multiple sheets also added to summary sheet | Excel Programming |