jacksonworld
02-20-2008, 05:27 PM
Good morning / afternoon / evening,
I am trying to combine a couple of perfectly functioning macros kindly supplied by some VBA Express members.
The problem is that when I attempt to combine the subs, I reach an error. I have tried a few things unsuccessfully, so I will just post my first attempt.
I hope someone can help. I don't think it is too difficult, but what would I know.
Sub CopyWorksheets()
Dim thisWB As Workbook
Dim newWB As Workbook
Dim numSheets As Long
Dim TargetSheet As Worksheet, SourceSheet As Worksheet
Set thisWB = ActiveWorkbook
numSheets = thisWB.Sheets.Count
Application.SheetsInNewWorkbook = numSheets
Set newWB = Workbooks.Add
numSheets = 1
For Each SourceSheet In thisWB.Sheets
Set TargetSheet = newWB.Sheets(numSheets)
Call CopyData(thisWB, newWB, SourceSheet.Name)
numSheets = numSheets + 1
Next SourceSheet
'start of other sub
Dim mpDate As Long
With ActiveSheet
'receiving error here
mpDate = .Evaluate("=MIN(IF((MOD(ROW(A1:A230),3)=0)*(A2:A230<>""""),ROW(A1:A230)))")
If mpDate <> 0 Then
newWB.SaveAs "C:\Working Folder" & Format(.Range("A1:A230").Cells(mpDate, 1), "yyyy-mm-dd")
End If
End With
'end of other sub
Set newWB = Nothing
Set thisWB = Nothing
Set TargetSheet = Nothing
Set SourceSheet = Nothing
End Sub
I am trying to combine a couple of perfectly functioning macros kindly supplied by some VBA Express members.
The problem is that when I attempt to combine the subs, I reach an error. I have tried a few things unsuccessfully, so I will just post my first attempt.
I hope someone can help. I don't think it is too difficult, but what would I know.
Sub CopyWorksheets()
Dim thisWB As Workbook
Dim newWB As Workbook
Dim numSheets As Long
Dim TargetSheet As Worksheet, SourceSheet As Worksheet
Set thisWB = ActiveWorkbook
numSheets = thisWB.Sheets.Count
Application.SheetsInNewWorkbook = numSheets
Set newWB = Workbooks.Add
numSheets = 1
For Each SourceSheet In thisWB.Sheets
Set TargetSheet = newWB.Sheets(numSheets)
Call CopyData(thisWB, newWB, SourceSheet.Name)
numSheets = numSheets + 1
Next SourceSheet
'start of other sub
Dim mpDate As Long
With ActiveSheet
'receiving error here
mpDate = .Evaluate("=MIN(IF((MOD(ROW(A1:A230),3)=0)*(A2:A230<>""""),ROW(A1:A230)))")
If mpDate <> 0 Then
newWB.SaveAs "C:\Working Folder" & Format(.Range("A1:A230").Cells(mpDate, 1), "yyyy-mm-dd")
End If
End With
'end of other sub
Set newWB = Nothing
Set thisWB = Nothing
Set TargetSheet = Nothing
Set SourceSheet = Nothing
End Sub