| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy different columns from sheet to other sheet | Excel Programming | |||
| Copy certain columns to another sheet using a macro | Excel Programming | |||
| Copy a row from one sheet to another and not all columns copy | Excel Programming | |||
| Create copy of sheet using VB with columns in different order | Excel Programming | |||
| macro to copy columns to sheet | Excel Discussion (Misc queries) | |||
Linear Mode
