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,

Try:

Sheets(Array("Summary", "Budget")).Copy

 

[plost33]

thanks James, worked great!

 

[plost33]

what would i need to change this to to copy every sheet int he workbook?

Sheets(Array("Summary", "Budget")).Copy

 

[JamesW]

Hi,

Try:

    For Each sht In ActiveWorkbook.Sheets
        sht.Copy After:=Workbooks("Book2").Sheets(Sheets.Count)
    Next

 

[GTO]

Another way:
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> exa()<br>    ThisWorkbook.Worksheets.Copy _<br>        After:=Workbooks("Book2.xls").Worksheets(Workbooks("Book2.xls").Worksheets.Count)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[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
   
  [COLOR=red] Sheets(Array("Summary", "Budget")).Copy
[/COLOR]    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]
 
 
Neither of the codes provided works.  I am wanting to change the line in red to also include a sheet named "Transaction Log".  when this code adds all three sheets to the new workbook i want to also have the sheet "transaction log" hidden. hope someone can help.

 

[plost33]

no one has any solutions to this?

 

[JamesW]

Hi,

Did you try this:

Sheets(Array("Summary", "Budget", "Transaction Log")).Copy

 

[plost33]

well i had tried that yesterday and it was not working. i juswt tried it nowa nd it worked.

yesterday when i tried i was also trying to hid the sheet "Transaction log" once it is pasted into the new workbook. i guess i had that line creating soem issues.

how might i add hiding the sheet "transaction log" after it is pasted into the new workbook to my current code?