Assembling Data of 100 Sheets in one Summary Sheet

[Akash]

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

 

[NickHK]

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

 

[Norman Jones]

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

 

[Akash]

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

 

[Norman Jones]

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.

 

[Akash]

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.

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

 

[Norman Jones]

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?

 

[NickHK]

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

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.

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

 

[Akash]

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

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

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.

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.

Akash- Hide quoted text -

- Show quoted text -