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

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

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
0
fb1990
Asked:
fb1990
  • 5
  • 4
1 Solution
 
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
 
fb1990Author Commented:
I need a macro to automate it
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
Rgonzo1971Commented:
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
 
fb1990Author Commented:
Thank you so much for your help
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now