Need help merging this code together

[zach9208]

I am looking to run the retrieveColumns code first, which copies select columns from source document and moves these select columns to a new workbook.

Then I want to run the saveworksheetscsv code to save the new workbook as a .csv

Sub RetrieveColumns()Dim nmary As Variant, sh1 As Worksheet, sh2 As Worksheet, i As Long, rng As Range
Set sh1 = ThisWorkbook.ActiveSheet
nmary = Array("As of Date", "Portfolio", "Derivative Type", "Identifier", "Trade Date", "Maturity Date", "Counterparty Name", "Pay/Rec", "Currency", "USD Notional Value @ Orig FX", "Rate", "FX @ Inception", "USD Fair Market Value", "Potential Exposure", "Book Value", "Hedge Type", "Hedge Strategy", "Price Source")
Workbooks.Add
Set sh2 = ActiveWorkbook.Sheets(1)
    For i = LBound(nmary) To UBound(nmary)
        Set rng = sh1.Rows(1).Find(nmary(i), , xlValues).EntireColumn
        rng.Copy sh2.Cells(1, i + 1)
    Next

[COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] SaveWorksheetsAsCsv[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]()[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">[COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] WS [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Excel[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Worksheet
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] SaveToDirectory [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] CurrentWorkbook [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] CurrentFormat [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Long[/COLOR][COLOR=#303336]

CurrentWorkbook [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] ThisWorkbook[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]FullName
CurrentFormat [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] ThisWorkbook[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]FileFormat
[/COLOR][COLOR=#858C93]' Store current details for the workbook[/COLOR][COLOR=#303336]
SaveToDirectory [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]"H:\test\"[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]For[/COLOR][COLOR=#101094]Each[/COLOR][COLOR=#303336] WS [/COLOR][COLOR=#101094]In[/COLOR][COLOR=#303336] ThisWorkbook[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Worksheets
    Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]WS[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Name[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Copy
    ActiveWorkbook[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]SaveAs Filename[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]SaveToDirectory [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] ThisWorkbook[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Name [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#7D2727]"-"[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] WS[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Name [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#7D2727]".csv"[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] FileFormat[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]xlCSV
    ActiveWorkbook[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Close savechanges[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336]
    ThisWorkbook[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Activate
[/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336]

Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]DisplayAlerts [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336]
ThisWorkbook[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]SaveAs Filename[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]CurrentWorkbook[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] FileFormat[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]CurrentFormat
Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]DisplayAlerts [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]True[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#858C93]' Temporarily turn alerts off to prevent the user being prompted[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#858C93]'  about overwriting the original file.[/COLOR][COLOR=#303336]
 [/COLOR]</code>[COLOR=#101094][FONT=Consolas]End[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR]

 

[Norie]

It's not clear what to name to save the new file as but perhaps something like this.

Sub RetrieveColumnsAndSaveWorksheetsAsCsv()
Dim wbNew As Workbook
Dim wbSrc As Workbook
Dim SaveToDirectory As String
Dim CurrentWorkbook As String
Dim CurrentFormat As Long
Dim nmary As Variant, sh1 As Worksheet, sh2 As Worksheet, i As Long, rng As Range

    Set wbSrc = ThisWorkbook
    Set sh1 = wbSrc.ActiveSheet

    nmary = Array("As of Date", "Portfolio", "Derivative Type", "Identifier", "Trade Date", "Maturity Date", "Counterparty Name", "Pay/Rec", "Currency", "USD Notional Value @ Orig FX", "Rate", "FX @ Inception", "USD Fair Market Value", "Potential Exposure", "Book Value", "Hedge Type", "Hedge Strategy", "Price Source")

    Set wbNew = Workbooks.Add(xlWBATWorksheet)

    Set sh2 = wbNew.Sheets(1)
    For i = LBound(nmary) To UBound(nmary)
        Set rng = sh1.Rows(1).Find(nmary(i), , xlValues).EntireColumn
        rng.Copy sh2.Cells(1, i + 1)
    Next
    
   ' Store current details for the workbook
    CurrentWorkbook = ThisWorkbook.FullName
    CurrentFormat = ThisWorkbook.FileFormat
 
    SaveToDirectory = "H:\test\"

    wbNew.SaveAs Filename:=SaveToDirectory & ThisWorkbook.Name & "-" & sh2.Name & ".csv", FileFormat:=xlCSV
    
    wbNew.Close savechanges:=False

    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
    Application.DisplayAlerts = True

End Sub

 

[zach9208]

Thanks, Norie. This worked perfectly!