loop that copy sheets in the same workbook

[eran3185]

hi

i need to copy sheet1 , sheet2 , shhet3 to a new sheet (in the same workbook)
i want to see something like that in the additional sheet : cccbbbaaa

how can i do this macro ?
:dunno

[lucas]

If there is no more to it than that you can usually get good results using the macro recorder...try the below...it was created using the recorder and then I changed a couple of little things. It can be cleaned up much more if it does what you require.
Sub Macro1()
Sheets.Add
ActiveSheet.Name = "Combined"
Sheets("1").Select
Range("A1").Select
Selection.Copy
Sheets("Combined").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C6").Select
Sheets("2").Select
Selection.Copy
Sheets("Combined").Select
Range("B1").Select
ActiveSheet.Paste
Sheets("3").Select
Range("C1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Combined").Select
Range("C1").Select
ActiveSheet.Paste
Range("C5").Select
End Sub

[eran3185]

but i need something more general .
i have some files , every file has 8-10 worksheets
and i need a macro that do this thing using a loop
i dont want to fill in the name of the sheets ...

[lucas]

eran you will have to be more specific. Please detail exactly what you're trying to do. It will save us time trying to help you and it will get you an answer much quicker. I can't read your mind or see over your shoulder so lay it out for us. A sample workbook would help with before sheets and a sheet with what you would like the end result to be.

[eran3185]

ok
i try to be more specific.
i have some files , in every file i have 8-10 , and sometimes 15 worksheets.
i need to do something like in the example (test copy) - to get all the worksheet , and to copy all of them to one sheet.
i need a macro that do this thing , because i have some files.
i thought to build a loop that take all the sheets , and copy them to one sheet in this way :
the first sheet
the second sheed
etc ...

in this way i get all the sheets in one sheet

[lucas]

one column from each sheet?

[mikerickson]

This will add a sheet to a workbook and then copy the other sheets of the workbook onto that new sheet. Each copy will begin on the row below the copy of the previous sheet.
Sub consolidateSheets()
Dim newSheet As Worksheet
Dim xSheet As Worksheet
With ThisWorkbook.Sheets
Set newSheet = .Add(after:=.Parent.Sheets(.Count))
End With
For Each xSheet In ThisWorkbook.Sheets
If Not (xSheet Is newSheet) Then
With newSheet.UsedRange
xSheet.UsedRange.Copy Destination:=newSheet.Cells(.Row + .Rows.Count, 1)
End With
End If
Next xSheet
newSheet.Range("1:1").Delete shift:=xlUp
End Sub

[eran3185]

this macro was very helpful to me

:clap:

[eran3185]

hi
if it possiable , i want to see the name of the sheet , in the first line of the additional sheet.
in my att. file , i want to see something like this :

aa
1
a
bb
2
2
cc
1
2
3

[mikerickson]

Sub consolidateSheets()
Dim newSheet As Worksheet
Dim xSheet As Worksheet
With ThisWorkbook.Sheets
Set newSheet = .Add(after:=.Parent.Sheets(.Count))
End With
For Each xSheet In ThisWorkbook.Sheets
If Not (xSheet Is newSheet) Then
With newSheet.Range("a65536").End(xlUp).Offset(1,0)
.Value = xSheet.Name
.Font.Underline = xlUnderlineStyleDoubleAccounting
End With
With newSheet.UsedRange
xSheet.UsedRange.Copy Destination:=newSheet.Cells(.Row + .Rows.Count, 1)
End With
End If
Next xSheet
newSheet.Range("1:1").Delete shift:=xlUp
End Sub

[eran3185]

thenks
it's good , but it take all the sheets to column a
maybe the sheets could be remain in them position (sheet 2 in b4 - not a4 , sheet 3 in c6 - not a6) ???

[lucas]

So far you haven't contributed any of the learning experience that your gaining here. You have barely answered our questions. It would be good experience for you to try (at least try) to work out some of these issues on your own and come back here with specific questions.

[mikerickson]

xSheet.UsedRange.Copy Destination:=newSheet.Cells(.Row + .Rows.Count, 1)

This is the main line of the code. It copies the .UsedRange of the (varying) xSheet to the bottom of the newSheet in column 1.

I haven't looked at the spread sheet, but your comments indicate that some sheets have empty columns on the left that aren't in the UsedRange.

You could either:
1) add those "leading" columns to the UsedRange by putting something in them.

or

2) use .Offset to offset the destination from the first column.
xSheet.UsedRange.Column is the first column in the UsedRange of xSheet, so xSheet.UsedRange.Column - 1 is the number of columns that the destination needs to be offset.

[eran3185]

thenk's
i try this solutions

have a nice day
eran