VBA worksheet copy

[plost33]

i use the following line of code to copy one sheet from my workbook. how can i change this is include two sheets: "Budget" and "Summary"?

ThisWorkbook.Worksheets("Budget").Copy

 

[JamesW]

Hi,

Where is the code to paste the sheets? I can't see it in your original code (you are copying them, but not putting them anywhere).

Either way, you would put the hide bit after you have pasted the sheets.

 

[plost33]

here is my complete code:

Sub ExportSheet()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
    Dim Folder As String
    Dim locationname As String
    Dim Response, pw
    Dim NewLogRow As Long
    Dim formula As String
    Dim NameValue As String
    Dim FinalName As String
    Dim XXX As String
   
    XXX = Format(Date, "[$-409]yyyymmdd;@")
    locationname = Sheets("Budget").Range("A3").Value
    Folder = "C:\Users\tphythian\Documents\Cost Tracking Exports"
    Worksheets("Budget").Visible = True
   
   Sheets(Array("Summary", "Budget", "Transaction Log")).Copy
   'Sheets("Budget").Copy
    With ActiveSheet.UsedRange
        .Value = .Value
    End With
    With ActiveWorkbook
        .SaveAs Folder & "\" & locationname & " - " & XXX & " - Export" & ".xlsx"
        .Close
    End With
    
                   
    MsgBox "Sheet has been exported and saved in " & "Cost Tracking Exports" & " folder!"
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
[\code]

 

[JamesW]

So what happens to the sheets once copied?

 

[plost33]

they are put into a new workbook by themselves and saved in the location shown.

 

[GTO]

Greetings plost,

Your code seemed to run fine to me, though maybe a bit of qualifying wouldn't hurt. Here's with hiding a sheet in the created wb.

Option Explicit
    
Sub ExportSheet()
Dim Folder          As String
Dim LocationName    As String
Dim XXX             As String
Dim wb              As Workbook
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    With ThisWorkbook
    
        XXX = Format(Date, "[$-409]yyyymmdd;@")
        LocationName = .Worksheets("Budget").Range("A3").Value
        
        Folder = ThisWorkbook.Path  ' "C:\Users\tphythian\Documents\Cost Tracking Exports"
        .Worksheets("Budget").Visible = True
        .Worksheets(Array("Summary", "Budget", "Transaction Log")).Copy
        Set wb = ActiveWorkbook
    End With
    
    With wb
        .ActiveSheet.UsedRange.Value = .ActiveSheet.UsedRange.Value
        'Debug.Print wb.ActiveSheet.Parent.Name
        .Worksheets("Transaction Log").Visible = xlSheetVeryHidden
        .SaveAs Folder & "\" & LocationName & " - " & XXX & " - Export" & ".xls" ' ".xlsx"
        .Close
    End With
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "Sheet has been exported and saved in " & "Cost Tracking Exports" & " folder!"
End Sub

BTW, if you use a forward solidus like [/code] your tags will work.

Hope that helps,

Mark