Solved

Excel macro to extract specific column

Posted on 2014-04-08
10
1,102 Views
Last Modified: 2014-04-08
Hello EE,

I have a spreadsheet that have 15 columns.  Is it possible to extract 2 specific columns and place the data into a new tab in the same workbook?  I have attached my sample data.  I have the data in a tab called Data.  I want a new tab created called Expected.

Thanks,
testme.xlsx
0
Comment
Question by:fb1990
  • 5
  • 4
10 Comments
 
LVL 8

Expert Comment

by:itjockey
ID: 39985623
Do u need macro for this or want to do it manually?
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39985650
Hi,

pls try

Sub Macro()
    Set DestSht = Sheets.Add(After:=Sheets(Sheets.Count))
    DestSht.Name = "Expected1"
    Sheets("Data").Columns("O:O").Copy Destination:=DestSht.Range("A1")
    Sheets("Data").Columns("C:C").Copy Destination:=DestSht.Range("B1")
    Application.CutCopyMode = False
    
End Sub

Open in new window

Regards
0
 
LVL 1

Author Comment

by:fb1990
ID: 39985728
I need a macro to automate it
0
 
LVL 1

Author Comment

by:fb1990
ID: 39985737
Hello Rgonzo1971,

Your suggestion worked well.  Is there a way to add filter column J and only pull records where the value is greater than zero?
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39985837
Hi,

pls try

Sub Macro2()
    Range("J1").AutoFilter
    ActiveSheet.UsedRange.AutoFilter Field:=10, Criteria1:=">0"
    Set DestSht = Sheets.Add(After:=Sheets(Sheets.Count))
    DestSht.Name = "Expected1"
    Sheets("Data").Columns("O:O").Copy Destination:=DestSht.Range("A1")
    Sheets("Data").Columns("C:C").Copy Destination:=DestSht.Range("B1")
    Application.CutCopyMode = False
    Sheets("Data").Range("J1").AutoFilter
End Sub

Open in new window

Regards
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:fb1990
ID: 39986211
Hello Rgonzo1971,

I tried the last code and i am getting a runtime error

Thanks
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39986235
Where does the code stop?
0
 
LVL 1

Author Comment

by:fb1990
ID: 39986398
This is the image

Doc1.docx
0
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39986673
Hi,

try this

Sub Macro2()
    Sheets("Data").Range("J1").AutoFilter
    Sheets("Data").UsedRange.AutoFilter Field:=10, Criteria1:=">0"
    Set DestSht = Sheets.Add(After:=Sheets(Sheets.Count))
    DestSht.Name = "Expected1"
    Sheets("Data").Columns("O:O").Copy Destination:=DestSht.Range("A1")
    Sheets("Data").Columns("C:C").Copy Destination:=DestSht.Range("B1")
    Application.CutCopyMode = False
    Sheets("Data").Range("J1").AutoFilter
End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:fb1990
ID: 39987652
Thank you so much for your help
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Search Workbook, then move rows to new sheet 13 31
highlight duplicate entry 16 27
Problem to With line 4 37
Boolean help 6 27
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

932 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now