Hi! Every Body

This is my second post in this site
I am very disappointed with my first post solution because i can't
find any specific solution. But this time i will hope that i will find
the solution.My question is
I have a workbook which have 15 worksheet with same format. I want to
copy all the 15 worksheet into another new worksheet without using
MACRO OR VBA.

Because i am totally stupid about MACRO OR VBA. It took much time to
copy and then paste sepertaly

For example: if sheet 1 has 500 columns and sheet 2 has 250 sheets3
has 100 and so on

I want to copy in new sheet i:e from sheet1 1to 500, then sheet2 501
to750, sheet3 751 to850 and so on.


Thanks in advance

There's no easy way to do this without using a macro.

Here's a quick macro that will do what you want.

Using macros is not a very difficult thing, it just takes a few
minutes of playing around to figure it out. The few minutes you spend
learning this will be much less time than it takes you to copy / paste
all of the data manually.

Follow these 10 simple steps to install the macro below. There's a
million ways to do this,but I find this to be the easiest for
beginners that I've helped.

1) Open Excel
2) Go to Tools-> Macro -> Record
3) In the little drop down box, select Personal Macro Workbook. Click
OK
4) Go to Tools-> Macro-> Stop Recording
5) Now go to Tools-> Macro-> Visual Basic Editor
6a) If there are no windows shown (i.e. the screen is blank except for
the menu's / toolbars on the top) go to View -> Project Explorer
6b) Look for the Personal.xls project. It will say
VBAProject(Personal.xls). Click the + sign to expand
7) Click the + sign before Modules to expand.
8) Double click on Module1. You will notice a code window (a place for
you to type text) appears on the right.
9) Finally paste the code below into this module.
10) To run the macro, go to Tools-> Macro->Macros and select
Combine_Sheets. Hit ok and the macro will run

Be sure to save changes to Personal.xls when you close excel otherwise
you'll have to do the steps above again next time you open excel.

Note: to "install" any other macros on your computer, just start at
step 6a.
Here's the macro...



Sub Combine_Sheets()
Dim wsht As Worksheet
Dim i As Integer

'Add a worksheet for the summary
Sheets(1).Select
Set wsht = ActiveWorkbook.Worksheets.Add



For i = 2 To ActiveWorkbook.Sheets.Count
'select the next sheet
Sheets(i).Select

'copy the data
Range("A1",
ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy ' Copy selection

'go to last used row of the summary sheet
wsht.Select
Range("A" & ActiveCell.SpecialCells(xlLastCell).Row +
1).Select

'paste the data
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False ' paste values
Application.CutCopyMode = False ' exit copy / paste
mode
Range("A1").Select

Next
End Sub


On Feb 22, 9:18 am, renu98 <hardeep.kan...@gmail.com> wrote:
> Hi! Every Body
>
> This is my second post in this site
> I am very disappointed with my first post solution because i can't
> find any specific solution. But this time i will hope that i will find
> the solution.My question is
> I have a workbook which have 15 worksheet with same format. I want to
> copy all the 15 worksheet into another new worksheet without using
> MACRO OR VBA.
>
> Because i am totally stupid about MACRO OR VBA. It took much time to
> copy and then paste sepertaly
>
> For example: if sheet 1 has 500 columns and sheet 2 has 250 sheets3
> has 100 and so on
>
> I want to copy in new sheet i:e from sheet1 1to 500, then sheet2 501
> to750, sheet3 751 to850 and so on.
>
> Thanks in advance

Try this FAST macro. It assumes that each sht has col A as the row with the
last value
Sub newsummarysheet()
Sheets.Add
ActiveSheet.Name = "Summary"
For Each ws In ActiveWorkbook.Sheets
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
slr = ws.Cells(Rows.Count, "a").End(xlUp).Row
If ws.Name <> "Summary" Then
ws.Rows("1:" & slr).Copy Cells(dlr, 1)
End If
Next ws
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"renu98" <(E-Mail Removed)> wrote in message
news:2d71bf70-e518-46c4-b76b-(E-Mail Removed)...
> Hi! Every Body
>
> This is my second post in this site
> I am very disappointed with my first post solution because i can't
> find any specific solution. But this time i will hope that i will find
> the solution.My question is
> I have a workbook which have 15 worksheet with same format. I want to
> copy all the 15 worksheet into another new worksheet without using
> MACRO OR VBA.
>
> Because i am totally stupid about MACRO OR VBA. It took much time to
> copy and then paste sepertaly
>
> For example: if sheet 1 has 500 columns and sheet 2 has 250 sheets3
> has 100 and so on
>
> I want to copy in new sheet i:e from sheet1 1to 500, then sheet2 501
> to750, sheet3 751 to850 and so on.
>
>
> Thanks in advance
>

These posts provide what you need.
You must realize not using a macro is like saying you want to have
excel sheets without learning excel.
We were all there.
Using the macro recorder you will be able to learn that is not as
difficult as it apears.
Don't give up.

Once, no one knew how to use a wheel, or fire.
Push yourself.

Scott

On Fri, 22 Feb 2008 06:18:55 -0800 (PST), renu98
<(E-Mail Removed)> wrote:

>Hi! Every Body
>
>This is my second post in this site
>I am very disappointed with my first post solution because i can't
>find any specific solution. But this time i will hope that i will find
>the solution.My question is
>I have a workbook which have 15 worksheet with same format. I want to
>copy all the 15 worksheet into another new worksheet without using
>MACRO OR VBA.
>
>Because i am totally stupid about MACRO OR VBA. It took much time to
>copy and then paste sepertaly
>
>For example: if sheet 1 has 500 columns and sheet 2 has 250 sheets3
>has 100 and so on
>
>I want to copy in new sheet i:e from sheet1 1to 500, then sheet2 501
>to750, sheet3 751 to850 and so on.
>
>
>Thanks in advance

Or, the old story about the guy that kept getting slapped when he asked
girls to sleep with him. It was worth the wait for the girl who said "YES"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"scott" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> These posts provide what you need.
> You must realize not using a macro is like saying you want to have
> excel sheets without learning excel.
> We were all there.
> Using the macro recorder you will be able to learn that is not as
> difficult as it apears.
> Don't give up.
>
> Once, no one knew how to use a wheel, or fire.
> Push yourself.
>
> Scott
>
> On Fri, 22 Feb 2008 06:18:55 -0800 (PST), renu98
> <(E-Mail Removed)> wrote:
>
>>Hi! Every Body
>>
>>This is my second post in this site
>>I am very disappointed with my first post solution because i can't
>>find any specific solution. But this time i will hope that i will find
>>the solution.My question is
>>I have a workbook which have 15 worksheet with same format. I want to
>>copy all the 15 worksheet into another new worksheet without using
>>MACRO OR VBA.
>>
>>Because i am totally stupid about MACRO OR VBA. It took much time to
>>copy and then paste sepertaly
>>
>>For example: if sheet 1 has 500 columns and sheet 2 has 250 sheets3
>>has 100 and so on
>>
>>I want to copy in new sheet i:e from sheet1 1to 500, then sheet2 501
>>to750, sheet3 751 to850 and so on.
>>
>>
>>Thanks in advance