Calling all Macro and VBA experts- the answer is simple but what?

Posted by sdimaggio on December 08, 2001 7:48 PM

I am trying to solve a small problem for all you VBA experts. I have a macro that automatically copies a database in workbook1 to workbook2 automatically and then sorts it while working in workbook1. The problem is I want to paste only selected columns (lets say A, C and D). I think there are two ways to go, I can either paste each column individually so they endup in workbook2 col. A,B and C, or set up a criteria, which I think is better. I know the solution is simple and is in the top half of the script. Ive tried a couple of different options but Im new to the VBA world. The script below works but it copies the whole database.

Sub AutoSort()
Application.ScreenUpdating = False
returncell = ActiveCell.Address

Cells.Select
Selection.Copy

Windows("Book2.xls").Activate
Sheets(1).Select
Range("A1").Select
ActiveSheet.Paste
'
' Application.CutCopyMode = False

Selection.Sort _
Key1:=Range("A2"), _
Order1:=xlDescending, _
Key2:=Range("C2"), _
Order2:=xlDescending, _
Key3:=Range("D2"), _
Order3:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

Range("A1").Select
Sheets(1).Select

Windows("Book1.xls").Activate

Range(returncell).Select
Application.ScreenUpdating = True
End Sub

Posted by Tom Urtis on December 08, 2001 9:01 PM

To copy only columns A, C, and D from workbook 1, replace your block of code
Cells.Select
Selection.Copy

With
Range("B:B,E:IV").EntireColumn.Hidden = True
Cells.SpecialCells(xlCellTypeVisible).Copy

Then, towards the end of your macro, you'll want to see all the columns again in Workbook 1, so unhide them with the line
Cells.EntireColumn.Hidden = False

Hope this is what you are looking for; if not please repost.

Tom Urtis

Cells.Select Selection.Copy Windows("Book2.xls").Activate Sheets(1).Select Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False Key1:=Range("A2"), _ Order1:=xlDescending, _ Key2:=Range("C2"), _ Order2:=xlDescending, _ Key3:=Range("D2"), _ Order3:=xlDescending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom Range("A1").Select Sheets(1).Select Windows("Book1.xls").Activate Range(returncell).Select Application.ScreenUpdating = True

Posted by Bariloche on December 08, 2001 9:51 PM

sd,

Assuming that you have selected your columns or cells that you want to copy, this segment of code will copy it and paste it into "Book2".

Sub CopySelectedColumns()

Selection.Copy
Workbooks("Book2").Sheets("Sheet1").Paste
Application.CutCopyMode = False

End Sub

If you want to hard code the columns into your macro, the syntax would look like this:

Range("C:C,F:F,J:J,M:M").Select

have fun