Solved: Combining 2 macros

[jacksonworld]

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.

[vba]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[/vba]

[mdmackillop]

Change A2:A230 to A1:A230 in the offending row. The ranges need to be the same size.
BTW, it's often better to call other routines as in
Call CopyData(thisWB, newWB, SourceSheet.Name)
It keeps code from getting overcomplicated and so easier to maintain.

[jacksonworld]

Whoops. That was a stupid mistake, however I am still receiving an error at the same point.

[mikerickson]

It looks like you are trying to Evaluate an array function.

[jacksonworld]

I gather that is not possible. Dammit.

[jacksonworld]

Does anyone know if this is possible?

[mdmackillop]

Can you post a workbook with sample data?

[jacksonworld]

Certainly. I have made it as simple as possible.

Btw, I tried calling the sub, instead of pasting it the middle of another one, but to no avail. I thought it might be easier to read for this purpose by leaving it in there.

Thanks for your help.

[mdmackillop]

On your sample, I'm getting a value of 3 for mpDate (Excel 2007; maybe its a version problem?)
Try writing the formula to a cell and reading the result if the Evaluate won't work.
[VBA]
Range("I1").FormulaArray = _
"=MIN(IF((MOD(ROW(RC[-8]:R[229]C[-8]),3)=0)*(RC[-8]:R[229]C[-8]<>""""""""),ROW(RC[-8]:R[229]C[-8])))"
mpDate = Range("I1")
MsgBox mpDate

[/VBA]

[jacksonworld]

Hmm. Ok. I am indeed receiving a 3 there, but what does that mean? I am lost.

[mdmackillop]

You substitute my code for your erroring code.

[Bob Phillips]

I gather that is not possible. Dammit.

Yes it is.

[jacksonworld]

Sorry to be a pain, but I am failing at substituting your code for the old code. Whatever I seem to do, I receive an error somewhere or other.

[mdmackillop]

[VBA]
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
Range("I1").FormulaArray = _
"=MIN(IF((MOD(ROW(RC[-8]:R[229]C[-8]),3)=0)*(RC[-8]:R[229]C[-8]<>""""""""),ROW(RC[-8]:R[229]C[-8])))"
mpDate = Range("I1")
'mpDate = .Evaluate("=MIN(IF((MOD(ROW(A1:A230),3)=0)*(A1: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
[/VBA]

[jacksonworld]

That's exactly what I tried but I receive an error at [VBA]newWB.SaveAs "C:\Working Folder" & Format(.Range("A1:A230").Cells(mpDate, 1), "yyyy-mm-dd") [/VBA]

It's weird.

Thanks for continuing effort, by the way.

[mdmackillop]

Have you checked the string value of that line? It doesn't look like a proper file path to me.

[jacksonworld]

Aagh, I forgot the backslash after the folder.

Thank you so much. It works perfectly.