Copy Columns to New Sheet
 

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

Copy Columns to New Sheet
 

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

Copy Columns to New Sheet
 

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

Copy Columns to New Sheet
 

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

Copy Columns to New Sheet
 

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