| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy specified columns to another sheet
OK try this: Make the sheet you want to copy from the active sheet before
you run this. Sub Copy() Dim StdWidth As Long, MyWidth As Long, i As Integer Dim sh As String, WrkSht As Worksheet sh = ActiveSheet.Name For Each WrkSht In Worksheets If WrkSht.Name = "Report" Then MsgBox ("A sheet named Report already exists") Exit Sub End If Next Worksheets.Add.Name = "Report" Worksheets(sh).Range("A:F,H:H,U:U,X:Y").Copy Range("A1") Columns("A:J").AutoFit StdWidth = Columns(11).ColumnWidth 'unused column For i = 1 To 10 MyWidth = Columns(i).ColumnWidth Next If MyWidth StdWidth Then Columns("A:J").ColumnWidth = MyWidth Else Columns("A:J").ColumnWidth = StdWidth End If End Sub You wanted to make the pasted columns uniform in width. 10 columns were pasted to the new sheet. I AutoFit these columns to the contents in them. I then check the width of an unused column (11) The For i = 1 to 10 code will find the widest column in the 10 columns that were pasted. If any of those columns are wider than a default column width (column 11) then make all 10 columns that widest width. If all 10 columns are less in width than the default width, then make all 10 columns the default width. This will give some uniformity to those column widths. Mike F "Gemz" wrote in message ... I tried pasting this into a VB module but already it displayed the first line as red..."Columns("A:F, H, U, X, Y").Copy Worksheets("Sheet2").Range("A1")." meaning there is something wrong with this line. i have put 'sub and a name' just before this line but dont know why its not working. also when you make reference to sheet 2, do you mean this info will be copied into sheet 2 only if there is a sheet existing with the name sheet 2? can i not jus say copy into another sheet which is non-existant and for the macro to create it and name it report? finally, what do you mean by "For i = 1 To 10" thanks in advance for all your help. "Mike Fogleman" wrote: Assuming you have already applied the filter: Columns("A:F, H, U, X, Y").Copy Worksheets("Sheet2").Range("A1"). Worksheets("Sheet2").Columns("A:J").AutoFit Dim StdWidth As Long, MyWidth As Long, i As Integer StdWidth = Columns(11).ColumnWidth 'unused column For i = 1 To 10 MyWidth = Columns(i).ColumnWidth Next If MyWidth StdWidth Then Columns("A:J").ColumnWidth = MyWidth Else Columns("A:J").ColumnWidth = StdWidth End If Mike F "Gemz" wrote in message ... I'd like to tell a macro to copy specified columns after it has filtered for a criteria - wich is if column U is blank to hide these rows and then copy columns A,B,C,D,E,F,H,Y,U,X to another sheet within the same workbook. if possible i would also like to tell the macro to format the data in some way because when i manually copied across, the column widths etc were not the same size and it just looked messy, can i also tell the macro to do this? please reply at your earliest possible convinience, many thanks. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy specified columns to another sheet
Hi,
I tried it and it works fine but i may have forgotten to say that i need to apply a filter first... If anything in column B = O or is blank then hide rows. how do i do this? thanks a lot, thanks for making it work. "Mike Fogleman" wrote: OK try this: Make the sheet you want to copy from the active sheet before you run this. Sub Copy() Dim StdWidth As Long, MyWidth As Long, i As Integer Dim sh As String, WrkSht As Worksheet sh = ActiveSheet.Name For Each WrkSht In Worksheets If WrkSht.Name = "Report" Then MsgBox ("A sheet named Report already exists") Exit Sub End If Next Worksheets.Add.Name = "Report" Worksheets(sh).Range("A:F,H:H,U:U,X:Y").Copy Range("A1") Columns("A:J").AutoFit StdWidth = Columns(11).ColumnWidth 'unused column For i = 1 To 10 MyWidth = Columns(i).ColumnWidth Next If MyWidth StdWidth Then Columns("A:J").ColumnWidth = MyWidth Else Columns("A:J").ColumnWidth = StdWidth End If End Sub You wanted to make the pasted columns uniform in width. 10 columns were pasted to the new sheet. I AutoFit these columns to the contents in them. I then check the width of an unused column (11) The For i = 1 to 10 code will find the widest column in the 10 columns that were pasted. If any of those columns are wider than a default column width (column 11) then make all 10 columns that widest width. If all 10 columns are less in width than the default width, then make all 10 columns the default width. This will give some uniformity to those column widths. Mike F "Gemz" wrote in message ... I tried pasting this into a VB module but already it displayed the first line as red..."Columns("A:F, H, U, X, Y").Copy Worksheets("Sheet2").Range("A1")." meaning there is something wrong with this line. i have put 'sub and a name' just before this line but dont know why its not working. also when you make reference to sheet 2, do you mean this info will be copied into sheet 2 only if there is a sheet existing with the name sheet 2? can i not jus say copy into another sheet which is non-existant and for the macro to create it and name it report? finally, what do you mean by "For i = 1 To 10" thanks in advance for all your help. "Mike Fogleman" wrote: Assuming you have already applied the filter: Columns("A:F, H, U, X, Y").Copy Worksheets("Sheet2").Range("A1"). Worksheets("Sheet2").Columns("A:J").AutoFit Dim StdWidth As Long, MyWidth As Long, i As Integer StdWidth = Columns(11).ColumnWidth 'unused column For i = 1 To 10 MyWidth = Columns(i).ColumnWidth Next If MyWidth StdWidth Then Columns("A:J").ColumnWidth = MyWidth Else Columns("A:J").ColumnWidth = StdWidth End If Mike F "Gemz" wrote in message ... I'd like to tell a macro to copy specified columns after it has filtered for a criteria - wich is if column U is blank to hide these rows and then copy columns A,B,C,D,E,F,H,Y,U,X to another sheet within the same workbook. if possible i would also like to tell the macro to format the data in some way because when i manually copied across, the column widths etc were not the same size and it just looked messy, can i also tell the macro to do this? please reply at your earliest possible convinience, many thanks. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy specified columns to another sheet
OK, first it was column U is blank, but now it is column B is blank or = O,
is that the letter "O" as you show, or a zero "0" ?? Are there headers in row 1? If not we will need to insert a row above the data to avoid missing the first row in the filter. Mike F "Gemz" wrote in message ... Hi, I tried it and it works fine but i may have forgotten to say that i need to apply a filter first... If anything in column B = O or is blank then hide rows. how do i do this? thanks a lot, thanks for making it work. "Mike Fogleman" wrote: OK try this: Make the sheet you want to copy from the active sheet before you run this. Sub Copy() Dim StdWidth As Long, MyWidth As Long, i As Integer Dim sh As String, WrkSht As Worksheet sh = ActiveSheet.Name For Each WrkSht In Worksheets If WrkSht.Name = "Report" Then MsgBox ("A sheet named Report already exists") Exit Sub End If Next Worksheets.Add.Name = "Report" Worksheets(sh).Range("A:F,H:H,U:U,X:Y").Copy Range("A1") Columns("A:J").AutoFit StdWidth = Columns(11).ColumnWidth 'unused column For i = 1 To 10 MyWidth = Columns(i).ColumnWidth Next If MyWidth StdWidth Then Columns("A:J").ColumnWidth = MyWidth Else Columns("A:J").ColumnWidth = StdWidth End If End Sub You wanted to make the pasted columns uniform in width. 10 columns were pasted to the new sheet. I AutoFit these columns to the contents in them. I then check the width of an unused column (11) The For i = 1 to 10 code will find the widest column in the 10 columns that were pasted. If any of those columns are wider than a default column width (column 11) then make all 10 columns that widest width. If all 10 columns are less in width than the default width, then make all 10 columns the default width. This will give some uniformity to those column widths. Mike F "Gemz" wrote in message ... I tried pasting this into a VB module but already it displayed the first line as red..."Columns("A:F, H, U, X, Y").Copy Worksheets("Sheet2").Range("A1")." meaning there is something wrong with this line. i have put 'sub and a name' just before this line but dont know why its not working. also when you make reference to sheet 2, do you mean this info will be copied into sheet 2 only if there is a sheet existing with the name sheet 2? can i not jus say copy into another sheet which is non-existant and for the macro to create it and name it report? finally, what do you mean by "For i = 1 To 10" thanks in advance for all your help. "Mike Fogleman" wrote: Assuming you have already applied the filter: Columns("A:F, H, U, X, Y").Copy Worksheets("Sheet2").Range("A1"). Worksheets("Sheet2").Columns("A:J").AutoFit Dim StdWidth As Long, MyWidth As Long, i As Integer StdWidth = Columns(11).ColumnWidth 'unused column For i = 1 To 10 MyWidth = Columns(i).ColumnWidth Next If MyWidth StdWidth Then Columns("A:J").ColumnWidth = MyWidth Else Columns("A:J").ColumnWidth = StdWidth End If Mike F "Gemz" wrote in message ... I'd like to tell a macro to copy specified columns after it has filtered for a criteria - wich is if column U is blank to hide these rows and then copy columns A,B,C,D,E,F,H,Y,U,X to another sheet within the same workbook. if possible i would also like to tell the macro to format the data in some way because when i manually copied across, the column widths etc were not the same size and it just looked messy, can i also tell the macro to do this? please reply at your earliest possible convinience, many thanks. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy specified columns to another sheet
Sorry forgot to say, when i run the macro it copies everything to the next
sheet and although you have put in a code to format the text it still comes up with some columns having to be widened so i can see all the contents - autofit or wrap text would do it right? how can i further modify the code? thanks again for all your help. "Mike Fogleman" wrote: OK try this: Make the sheet you want to copy from the active sheet before you run this. Sub Copy() Dim StdWidth As Long, MyWidth As Long, i As Integer Dim sh As String, WrkSht As Worksheet sh = ActiveSheet.Name For Each WrkSht In Worksheets If WrkSht.Name = "Report" Then MsgBox ("A sheet named Report already exists") Exit Sub End If Next Worksheets.Add.Name = "Report" Worksheets(sh).Range("A:F,H:H,U:U,X:Y").Copy Range("A1") Columns("A:J").AutoFit StdWidth = Columns(11).ColumnWidth 'unused column For i = 1 To 10 MyWidth = Columns(i).ColumnWidth Next If MyWidth StdWidth Then Columns("A:J").ColumnWidth = MyWidth Else Columns("A:J").ColumnWidth = StdWidth End If End Sub You wanted to make the pasted columns uniform in width. 10 columns were pasted to the new sheet. I AutoFit these columns to the contents in them. I then check the width of an unused column (11) The For i = 1 to 10 code will find the widest column in the 10 columns that were pasted. If any of those columns are wider than a default column width (column 11) then make all 10 columns that widest width. If all 10 columns are less in width than the default width, then make all 10 columns the default width. This will give some uniformity to those column widths. Mike F "Gemz" wrote in message ... I tried pasting this into a VB module but already it displayed the first line as red..."Columns("A:F, H, U, X, Y").Copy Worksheets("Sheet2").Range("A1")." meaning there is something wrong with this line. i have put 'sub and a name' just before this line but dont know why its not working. also when you make reference to sheet 2, do you mean this info will be copied into sheet 2 only if there is a sheet existing with the name sheet 2? can i not jus say copy into another sheet which is non-existant and for the macro to create it and name it report? finally, what do you mean by "For i = 1 To 10" thanks in advance for all your help. "Mike Fogleman" wrote: Assuming you have already applied the filter: Columns("A:F, H, U, X, Y").Copy Worksheets("Sheet2").Range("A1"). Worksheets("Sheet2").Columns("A:J").AutoFit Dim StdWidth As Long, MyWidth As Long, i As Integer StdWidth = Columns(11).ColumnWidth 'unused column For i = 1 To 10 MyWidth = Columns(i).ColumnWidth Next If MyWidth StdWidth Then Columns("A:J").ColumnWidth = MyWidth Else Columns("A:J").ColumnWidth = StdWidth End If Mike F "Gemz" wrote in message ... I'd like to tell a macro to copy specified columns after it has filtered for a criteria - wich is if column U is blank to hide these rows and then copy columns A,B,C,D,E,F,H,Y,U,X to another sheet within the same workbook. if possible i would also like to tell the macro to format the data in some way because when i manually copied across, the column widths etc were not the same size and it just looked messy, can i also tell the macro to do this? please reply at your earliest possible convinience, many thanks. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy specified columns to another sheet
OK, here it is:
Sub Filter_Copy() Dim StdWidth As Long, MyWidth As Long, i As Integer Dim sh As Worksheet, RprtSht As Worksheet Dim exists As Boolean Application.ScreenUpdating = False Set sh = Worksheets("GTS_ C_SI") For Each RprtSht In Worksheets If RprtSht.Name = "Report" Then RprtSht.Cells.Clear exists = True Else If exists = True Then Exit For Worksheets.Add.Name = "Report" End If Next Set RprtSht = Worksheets("Report") With sh .Activate .AutoFilterMode = False End With Columns("Y:Y").AutoFilter Field:=1, Field:=1, Criteria1:="<0", _ Operator:=xlAnd, Criteria2:="<" sh.Range("C:D,G:H,S:Y,AJ:AJ").Copy RprtSht.Range("A1") RprtSht.UsedRange.Columns.AutoFit RprtSht.UsedRange.Rows.AutoFit sh.AutoFilterMode = False Application.ScreenUpdating = True End Sub Mike F "Mike Fogleman" wrote in message . .. What do you mean by "Everything"? All the columns are copied, or just everything in the specified columns, because they were not filtered first? the text it still comes up with some columns having to be widened so i can see all the contents - The line that says 'Columns("A:J").AutoFit' should take care of that. I would probably have to see a copy of your workbook to figure that out. If you want to email me a copy send it to: Remove NOSPAM from the address. Mike F "Gemz" wrote in message ... Sorry forgot to say, when i run the macro it copies everything to the next sheet and although you have put in a code to format the text it still comes up with some columns having to be widened so i can see all the contents - autofit or wrap text would do it right? how can i further modify the code? thanks again for all your help. "Mike Fogleman" wrote: OK try this: Make the sheet you want to copy from the active sheet before you run this. Sub Copy() Dim StdWidth As Long, MyWidth As Long, i As Integer Dim sh As String, WrkSht As Worksheet sh = ActiveSheet.Name For Each WrkSht In Worksheets If WrkSht.Name = "Report" Then MsgBox ("A sheet named Report already exists") Exit Sub End If Next Worksheets.Add.Name = "Report" Worksheets(sh).Range("A:F,H:H,U:U,X:Y").Copy Range("A1") Columns("A:J").AutoFit StdWidth = Columns(11).ColumnWidth 'unused column For i = 1 To 10 MyWidth = Columns(i).ColumnWidth Next If MyWidth StdWidth Then Columns("A:J").ColumnWidth = MyWidth Else Columns("A:J").ColumnWidth = StdWidth End If End Sub You wanted to make the pasted columns uniform in width. 10 columns were pasted to the new sheet. I AutoFit these columns to the contents in them. I then check the width of an unused column (11) The For i = 1 to 10 code will find the widest column in the 10 columns that were pasted. If any of those columns are wider than a default column width (column 11) then make all 10 columns that widest width. If all 10 columns are less in width than the default width, then make all 10 columns the default width. This will give some uniformity to those column widths. Mike F "Gemz" wrote in message ... I tried pasting this into a VB module but already it displayed the first line as red..."Columns("A:F, H, U, X, Y").Copy Worksheets("Sheet2").Range("A1")." meaning there is something wrong with this line. i have put 'sub and a name' just before this line but dont know why its not working. also when you make reference to sheet 2, do you mean this info will be copied into sheet 2 only if there is a sheet existing with the name sheet 2? can i not jus say copy into another sheet which is non-existant and for the macro to create it and name it report? finally, what do you mean by "For i = 1 To 10" thanks in advance for all your help. "Mike Fogleman" wrote: Assuming you have already applied the filter: Columns("A:F, H, U, X, Y").Copy Worksheets("Sheet2").Range("A1"). Worksheets("Sheet2").Columns("A:J").AutoFit Dim StdWidth As Long, MyWidth As Long, i As Integer StdWidth = Columns(11).ColumnWidth 'unused column For i = 1 To 10 MyWidth = Columns(i).ColumnWidth Next If MyWidth StdWidth Then Columns("A:J").ColumnWidth = MyWidth Else Columns("A:J").ColumnWidth = StdWidth End If Mike F "Gemz" wrote in message ... I'd like to tell a macro to copy specified columns after it has filtered for a criteria - wich is if column U is blank to hide these rows and then copy columns A,B,C,D,E,F,H,Y,U,X to another sheet within the same workbook. if possible i would also like to tell the macro to format the data in some way because when i manually copied across, the column widths etc were not the same size and it just looked messy, can i also tell the macro to do this? please reply at your earliest possible convinience, many thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy Columns to New Sheet | Excel Programming | |||
| 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 | |||
| macro to copy columns to sheet | Excel Discussion (Misc queries) | |||
Hybrid Mode
