Macro Help

[Beamish]

Hi,

I need to amend this code so that:

a) the new workbooks automatically save to the folder /Reports which will sit in the same folder as this master file sits in. So that wherever the master file is saved, as long as there is a folder called Reports next to it, then the new workbooks will be saved there.

b) The macro performs copy values only and not any of the formulas.

c) If possible, add an additional tab into the newly created worksheet, blank but with the name “Transactions”.


Any help with this would be great. Also if anyone can point me in the best direction for self teaching for excel macros that would be great.

TIA

[VBA]Sub CopySheets()
Dim i As Long, s As String
s = ThisWorkbook.Name
For i = 1 To Sheets.Count
Sheets(i).Select
If Cells(4, 2).Text = "." Then
Sheets(i).Copy
Workbooks(Workbooks.Count).SaveAs _
Filename:="O:\Reports\" + Cells(1, 2).Text + ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Workbooks(Workbooks.Count).Close
End If
Windows(s).Activate
Next
End Sub
[/VBA]

[p45cal]

try:[vba]Sub blah()
Dim i As Long, s As String, MyPath As String, NewWb As Workbook, sht As Worksheet, NewWs As Worksheet
Application.ScreenUpdating = False
MyPath = ThisWorkbook.Path & "\Reports\"
For Each sht In ThisWorkbook.Sheets
With sht
If .Cells(4, 2).Text = "." Then
sht.Copy
Set NewWb = ActiveWorkbook
NewWb.Sheets(1).UsedRange.Value = sht.UsedRange.Value
Set NewWs = NewWb.Sheets.Add
NewWs.Name = "Transactions"
NewWb.SaveAs _
Filename:=MyPath + sht.Cells(1, 2).Text + ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
NewWb.Close
End If
End With
Next sht
Application.ScreenUpdating = True
End Sub
[/vba]