Copy specified columns to another sheet

[Gemz]

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.

 

[Mike Fogleman]

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]

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]

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]

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.

 

[Gemz]

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]

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

 

[Mike Fogleman]

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 address removed)
Remove NOSPAM from the address.
Mike F

 

[Mike Fogleman]

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