CODEWITHSUNDEEP
excelexcel-automationvba
Ari
Ari

Reputation: 23

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

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

Upvotes: 1

Views: 207

Answers (2)

user4039065
user4039065

Reputation:

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

Upvotes: 1

NetMage
NetMage

Reputation: 26907

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?

Upvotes: 0

Related Questions