ExcelBanter - View Single Post

manfareed

Thank You

"Joel" wrote:

from
Worksheets.Add after:=Sheets(Sheets.Count)
to
Worksheets.Add befo=Sheets(1)

"manfareed" wrote:

Hi Joel,

Thanks for the code ... perfect !!!

One question ...

How do I make the "sheet" "All Branches" the first tab ? It should be before
the summary sheet.

Thanks,

Manir

"Joel" wrote:

Sub create_summary()

Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "All Branches"

ColumnCount = 3 'Column C
For Each sht In ThisWorkbook.Sheets
If sht.Name < "All Branches" Then
sht.Columns("C:I").Copy _
Destination:=Sheets("All Branches"). _
Columns(ColumnCount)
With Sheets("All Branches").Cells(8, ColumnCount)
.Value = sht.Name
.Font.Bold = True
.Font.Underline = xlUnderlineStyleSingle
End With
ColumnCount = ColumnCount + 8
End If

Next sht
"manfareed" wrote:

Hi ,

I have "current month" and "Year to Date" data in columns "C" to "I"... for
each of the branches for one of our companies. I need to add a new sheet [
"All Branches"]and copy " columns C to I from each of the branch "tabs" to
the new sheet. Therefore the data from the first tab will be in Column C to I
in the new sheet and data from the 2nd tab will be copied to the next
available column after COL I [I would prefer 1 column space between 2
different branches i.e. col "K" ]in the new sheet and so on ...

One final thing ... when data is copied to the new sheet - "All Branches"
tab ... the tab name [branch name] should also be copied. For Example for
the first branch it should be copied to Range "C8" [In bold and underlined].

I cannot record the macro because the names and the number of branches
varies for each of the companies.

Hope the above makes sense.

Thanks