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
Click to expand...
Click to expand...
 
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
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

PARSING DATA TO SHEETS 3
Help!! 2
Create New sheet and copy appropriate data to new sheet 1
Going To The Last Empty Row and Pasting Special (Values). 5
Macro/VBA to loop through list of values and output row- Help Need 6
Copy columns and paste with blank column between 5
Solver programming in VBA 3
Eliminate blanks while copy row and transpose 11

Top