Code Help - Automation

[Joneye]

I have a work book with many tabs, I wish just for one tab to be exported with the data to a Named folder.

Also this process will take place a few times a week so if the above is possible the exported work book must not conflict with the last.

Is this possible?

 

[VoG]

Try like this

Sub SaveSheet()
Sheets("Sheet1").Copy
With ActiveWorkbook
    .SaveAs Filename:="C:\test\Copy " & Format(Date, "ddmmyyyy") & ".xls"
    .Close
End With
End Sub

 

[Joneye]

VOG, I have little experience in VBA, I asume i alt + F11 then click on the relevant tab and insert my code?

Can I then assign this to a button? - (either command or or button)

Many thanks in advance.

 

[VoG]

Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu and paste in the code. Change the bits in red to suit:

Sub SaveSheet()
Sheets("Sheet1").Copy
With ActiveWorkbook
    .SaveAs Filename:="C:\test\Copy " & Format(Date, "ddmmyyyy") & ".xls"
    .Close
End With
End Sub

Press ALT + Q to close the code window. Add a button from the Forms toolbar and when prompted assign it to SaveSheet.

 

[Joneye]

That is AWESOME VOG - Just made my day

 

[Joneye]

Quick question this part of the code & Format(Date, "ddmmyyyy") & ".xls"

Can i include time also so that if im saving every now and again it does not cause a confliction?

 

[VoG]

Try

Sub SaveSheet()
Sheets("Sheet1").Copy
With ActiveWorkbook
    .SaveAs Filename:="C:\test\Copy " & Format(Now, "ddmmyyyy hhmmss") & ".xls"
    .Close
End With
End Sub

 

[Joneye]

VOG - thanks for the pace and speed of your help... Again BRILLIANT

 

[Joneye]

VOg using the code supplied before, can I export an additional tab?

Plus can I clean the data that im exporting just to copy the raw data not the links its attached to in the sheet?

 

[VoG]

Try like this

Sub SaveSheet()
Dim ws As Worksheet
Sheets(Array("Sheet1", "Sheet2")).Copy
For Each ws In ActiveWorkbook
    With ws.UsedRange
        .Value = .Value
    End With
Next ws
With ActiveWorkbook
    .SaveAs Filename:="C:\test\Copy " & Format(Now, "ddmmyyyy hhmmss") & ".xls"
    .Close
End With
End Sub