making a Macro that exports chosen columns to a new file

Question

What kind of Macro can be created that exports chosen columns to another new file?

for example exports columns A, B, C to a new file with a few new extra lines on top?

this would be a Button on page.

Answer 1

You can use ADO. This example writes to a named sheet, but you could also write to a number of different applications, or a text document. It assumes that you have a header row with named columns (ColName1, ColName2) but it is also possible to copy columns that do not have a header row.

Dim cn As Object
Dim rs As Object

strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT ColName1, ColName2 FROM [Sheet4$]"

rs.Open strSQL, cn

For i = 0 To rs.Fields.Count - 1
    Sheets("Sheet2").Cells(1, i + 1) = rs.Fields(i).Name
Next

Sheets("Sheet2").Cells(2, 1).CopyFromRecordset rs

Answer 2

This code will copy the selected range to a new workbook.

Sub Copy_Selection()

    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False

End Sub

Once the macro has been added, it can be linked to a button.

For Excel 2007:

• Click on the Developer tab
• Use the Insert drop down to select the button from the Form controls section
• The cursor will change to a cross hair
• Left click, hold, drag and release on the screen to size the button
• The Assign macro dialog will be displayed, select the macro and click the OK button
• Right click on the button and select Edit text
• Update the button text to 'Copy selection'