Solved

Copy and Paste Columns

Posted on 2014-09-16
2
209 Views
Last Modified: 2014-09-17
I found this code on the Internet and it works well but I'd like to specify the ranges to copy to a new sheet (which are columns). So, instead of wsSource.Range("E9:Q351").Copy, I'd like to select different columns that start at a specific row. Like, wsSource.Range("column A10:A50, column F10:F50, column S10:S50, etc...").Copy. I hope this makes sense. Thanks!


Sub Foo()
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim wbDest As Workbook
Dim fName As String

'References
Set wbSource = ActiveWorkbook
Set wsSource = ActiveSheet
Set wbDest = Workbooks.Add

'Copy range on original sheet
wsSource.Range("E9:Q351").Copy

'----------------------------
'Save in new workbook
wbDest.Worksheets(1).Cells(1, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

'Get file name and location from user
fName = Application.GetSaveAsFilename(FileFilter:="CSV (*.csv), *.csv", Title:="Save As")
If fName = "" Then Exit Sub '//user cancelled

'Save new csv file
wbDest.SaveAs fName, xlCSV

wbDest.Close SaveChanges:=True
'----------------------------

End Sub

Open in new window

0
Comment
Question by:tracyms
2 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 40327291
You just do it like this:
wsSource.Range("A10:A50,F10:F50,S10:S50").Copy

Open in new window

i.e. just comma-separate the ranges. Make sure that the ranges do not intersect
0
 
LVL 1

Author Comment

by:tracyms
ID: 40328152
Geez! I tried that but had it like this wsSource.Range("A10:A50","F10:F50","S10:S50").Copy - quotes around each range and now I see why it wasn't working! Thank you! :-)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question