Beamish
06-21-2010, 07:24 AM
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
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
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
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