Exporting Multiple Worksheets into a single Excel File

[suiliclic]

Dear Forum,

I am wondering what's the best way to write Excel VB codes for to accompolish the following tasks?
I have an excel file (book1.xls) which consist of 1 master worksheet. I created a Form to trigger a bunch of macro buttons to do auto filters. Then my codes will copy and paste these filtered records into a new sheets (Sheet1, Sheet2, Sheet3..etc). My problem is that after I created these sheets, I can NOT figure out the codes for button1 to export Sheet1 onto a NEW FILE, and it will allow user SAVE the whatever file name or directory they desire (say file name is book2.xls). Back on the user form of book1.xls, I also would like to click on the button2 to COPY Sheet2 and append it onto any workbook or to the book2.xls as well. I think I have to declare some type of global variable, so the user would be able to map Sheet2 onto any excel work book they desire as appending the worksheet. I am very new to any programing. Please be so kind to provide some insightful advise. Thank you very much. Your advise will make my day! =)

Zabrina from the SF BayARea
Just another humble analyst & VBA newbie~

 

[PHV]

Take a look at the Worksheet.Copy method called without argument.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[suiliclic]

Dear Forum,

Here is my codes to perform above tasks and it stopped at
it wants to debug at "Set sh = wk.Worksheets.Add" Any idea why? Thanks bunch!
------------------------------------------------------------
Public wk As Workbook
Public sh As Worksheet
Public strFileName As String

Private Sub CreateExportData(strSheetName)

' If there isn't a workbook already, then create one
' check variable declare on top of this page
' wk, sh & strFile name are variables

If wk Is Nothing Then
Set wk = Workbooks.Add
strFileName = "test2.xls"

End If

' Create a new worksheet and paste copied contents into the sheet
Set sh = wk.Worksheets.Add
sh.Name = strSheetName
sh.Paste

' Check if file wasn't saved already, if so, then just save, otherwise saveas
Application.DisplayAlerts = False
If strFileName = "" Then
wk.SaveAs strFileName
Else
wk.Save
End If
End Sub
-----------------------------------------------------------

Here are the codes will call above function
Sub D1ExportAllEligDays()
'All D Series subs will export filtered rows into the same file as seperate tabs


Sheets("All Codes_Listing_HH").Select
Selection.AutoFilter Field:=2, Criteria1:="Y"
Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.EntireColumn.Hidden = False
Rows("1:1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Call CreateExportData("DSH Flag Y")

' Focus the application back to the worksheet
Workbooks("All Codes Eligibility_withMacro.xls").Activate

' Unselect cells
Application.CutCopyMode = False


End Sub
---------------------------------------------------------


Zabrina from the SF BayARea
Just another humble analyst & VBA newbie~