How to get the columns of an excel sheet that have a specific string, and write them in a new excel file

Question

I have excel file "file1" with a few sheets. I am interested in only one sheet, "sheet1" that has many columns, and I am only interested in few columns with specific name in that sheet (first row of the excel sheet contains the name of the variables.) I'd like to find the columns that match (equal to) a specific set of strings (e.g. 10 variable names), and copy them with all the rows in those column, and paste them into a new excel file, "file2". I'd like to generate the 2nd excel file automatically with the script too (this 2nd excel file will have 1 sheet only.)

I started writing some VBA script for this (I just learned some by a little search, so I am not familiar with VBA), but I was getting some errors. My first question is what is the best tool to do such task, and then how to do it. I appreciate your help.

This is my very simple code that works when I know the column numbers and #rows (but both change and that is why I want to work on strings)

Sub ExtractData()

Filename = "OriginalFile"
Workbooks(Filename).Sheets("sheet2").Range("K1:K206").Copy Range("A1:A206")
Workbooks(Filename).Sheets("sheet2").Range("CF1:CF206").Copy Range("B1:B206")
Workbooks(Filename).Sheets("sheet2").Range("BRG1:BRG206").Copy Range("C1:C206")
Workbooks(Filename).Sheets("sheet2").Range("ESM1:ESN206").Copy Range("D1:E206")
Workbooks(Filename).Sheets("sheet2").Range("EWY1:EWZ206").Copy Range("F1:G206")

End Sub

You'll probably get better answers if you actually post the script you tried and ask for help with it - right now your question is just "do this for me" which doesn't go over well. — Ajean, May 24 '17 at 00:34

score 1 · Accepted Answer · edited May 24 '17 at 02:40

1

Sometimes it is easier to make a copy of everything and remove what you do not want.

Option Explicit

Sub makeCopy()
    Dim c As Long, cols As Variant

    cols = Array("date", "foo", "bar")

    ThisWorkbook.Worksheets("sheet1").Copy

    With ActiveWorkbook.Worksheets(1)
        For c = .Cells(1, .Columns.Count).End(xlToLeft).Column To 1 Step -1
            If IsError(Application.Match(.Cells(1, c).Value2, cols, 0)) Then
                .Cells(1, c).EntireColumn.Delete
            End If
        Next c
        .Parent.SaveAs Filename:="newFoo", FileFormat:=xlOpenXMLWorkbook
    End With
End Sub

edited May 24 '17 at 02:40

YowE3K

23,852
7
26
40

answered May 23 '17 at 23:52

Thank you for writing the code. I haven't test it yet, but I prefer non-delete options. Is it a hard change? – Ari May 24 '17 at 00:35
@Ari Why do you prefer "non-delete" options? What's the difference between (a) taking a copy of the entire sheet and then deleting all the bits except the bits you want and (b) copying only the bits that you want? (Apart from option (b) often being more difficult to implement.) – YowE3K May 24 '17 at 01:40
@YowE3K I misunderstood Jeeped answer. I thought he will delete the original file. – Ari May 24 '17 at 02:07
@Jeeped Thank you. Your code sort of works. It gives an error at the end "Application-defined or object-defined error". It is also very slow, takes 5 minutes on my work laptop (my original file I tested on has 7900 columns and 40 rows.) The output file is generated, but it is not saved though. I am guessing error happened during saving then? – Ari May 24 '17 at 02:15
@ari - (a) change `.Parent.Save` to `.Parent.SaveAs` (b) Do you have formulas in the worksheet being copied, or just data? – YowE3K May 24 '17 at 02:39
Saveas solved the bug and I think it was faster this time too (I am running other codes at the same time too, this might be impacted...) Thank you. No formula. – Ari May 24 '17 at 02:53
@YowE3K - thanks for the tidying up; I just wasn't around to catch that. – May 24 '17 at 14:27
@Jeeped It's always annoying when we have to stop responding to SO questions/comments and attend to lesser matters - such as eating, sleeping, working, etc. – YowE3K May 24 '17 at 19:37

score 0 · Answer 2 · answered May 23 '17 at 23:36

0

I would probably use ODBC to Excel and LINQPad, but adding outside tools you aren't already using seems like overkill. How about using a Query From Workbook?

answered May 23 '17 at 23:36

NetMage

26,163
3
34
55

Could you explain more what you mean by "Query From Workbook"? – Ari May 24 '17 at 00:34
Excel Ribbon, Data Tab, New Query, From File, From Workbook. Now you can use Power Query M language (or the Query UI) to Edit the query and adjust what you want to retrieve. – NetMage May 24 '17 at 16:04

How to get the columns of an excel sheet that have a specific string, and write them in a new excel file

2 Answers2