Solved

Copy and Paste Columns

Posted on 2014-09-16
2
211 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 25

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

762 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