Excel macro to extract specific column

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
LVL 1
fb1990Asked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
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
 
Naresh PatelTraderCommented:
Do u need macro for this or want to do it manually?
0
 
Rgonzo1971Commented:
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
fb1990Author Commented:
I need a macro to automate it
0
 
fb1990Author Commented:
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
 
Rgonzo1971Commented:
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
 
fb1990Author Commented:
Hello Rgonzo1971,

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

Thanks
0
 
Rgonzo1971Commented:
Where does the code stop?
0
 
fb1990Author Commented:
This is the image

Doc1.docx
0
 
fb1990Author Commented:
Thank you so much for your help
0
All Courses

From novice to tech pro — start learning today.