Copy Columns to New Sheet

M

manfareed

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
 
J

Joel

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
 
M

manfareed

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 said:
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 said:
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
 
J

Joel

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

manfareed said:
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 said:
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 said:
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
 
M

manfareed

Thank You

Joel said:
from
Worksheets.Add after:=Sheets(Sheets.Count)
to
Worksheets.Add before:=Sheets(1)

manfareed said:
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 said:
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
:

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top