[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

Excel macro to get selected colums to a new sheet

Hi,

I want a macro to ask me which all colums i need to copy to a new sheet.
A,C,F and so on.Ant the macro copies all data in the rows to a new sheet.

Regards
Sharath
0
bsharath
Asked:
bsharath
  • 6
  • 3
  • 2
1 Solution
 
gtglonerCommented:
Try this macro in VBA:

Sub copycols()

    Dim colrange As String
    colrange = InputBox("Select column letter to be copied. If there are 2 or more adjacent, type colon inbetween eg. for columns C to G, type C:G")
   

    Columns(colrange).Select
    Selection.Copy
    Sheets.Add
    Columns("A:A").Select
    ActiveSheet.Paste
   
End Sub
0
 
bsharathAuthor Commented:
Is there a way to give as
A:B:D:G:H
All at the same time.This work make work more easier
0
 
bsharathAuthor Commented:
After 1 colum it creates a new page and for the 2nd time i rin the script it goes again to a new page
0
Technology Partners: 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!

 
gtglonerCommented:
I don't know how this can be done with non-contiguous ranges in a macro. I think you have to take the different sets of columns one at a time when copying them from one sheet to another
0
 
bsharathAuthor Commented:
Ok then how can i do to copy colums A,C and J
0
 
bsharathAuthor Commented:
Any help...
0
 
Wayne Taylor (webtubbs)Commented:
Hello bsharath,

When copying the columns to the new sheet, do you wish the data to go in the same column, or just from left to right?

Regards,

Wayne
0
 
bsharathAuthor Commented:
I want it to copy the full row left and right.
It needs to start from Colum "A"
0
 
Wayne Taylor (webtubbs)Commented:
bsharath,

What I meant was if you copy columns A, B and D, in the new sheet, so you want the data to be copied over to A, B and D, or A, B and C?

Try this....

     Sub CopyColumns()
         Dim ColsToCopy As Range
         Dim newsheet As Worksheet
         Set ColsToCopy = Application.InputBox(Prompt:="Select the columns you wish to copy to a new sheet." & vbCrLf & vbCrLf & "[Press and Hold Ctrl to select non-adjacent columns]", Type:=8)
         If ColsToCopy Is Nothing Then Exit Sub
         Set newsheet = Worksheets.Add
         ColsToCopy.Copy newsheet.Range("A1")
     End Sub

Wayne
0
 
bsharathAuthor Commented:
I want to copy to A,B,C
0
 
Wayne Taylor (webtubbs)Commented:
bsharath,

Try the macro I posted.

Wayne
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now