| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
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  ,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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > 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: > (E-Mail Removed) > Remove NOSPAM from the address. > Mike F > "Gemz" <(E-Mail Removed)> wrote in message > news:828B216A-4A36-49A3-8C5E-(E-Mail Removed)... >> 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" <(E-Mail Removed)> wrote in message >>> news:7725E43E-4615-4CF9-8040-(E-Mail Removed)... >>> >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" <(E-Mail Removed)> wrote in message >>> >> news:F71B339C-7F88-42FB-8F9F-(E-Mail Removed)... >>> >> > 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. >>> >> >>> >> >>> >> >>> >>> >>> > > |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Copy Columns to New Sheet | manfareed | Microsoft Excel Programming | 4 | 21st Dec 2007 10:56 AM |
| copy different columns from sheet to other sheet | p. panter | Microsoft Excel Programming | 1 | 9th Feb 2006 05:23 PM |
| Copy certain columns to another sheet using a macro | Shane Nation | Microsoft Excel Programming | 2 | 24th Sep 2005 05:36 PM |
| Re: Copy a row from one sheet to another and not all columns copy | Peaches | Microsoft Excel Programming | 0 | 7th Sep 2005 12:09 PM |
| macro to copy columns to sheet | Es | Microsoft Excel Misc | 1 | 7th Mar 2005 02:03 PM |
