PDA

View Full Version : Solved: Combining 2 macros



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

mdmackillop
02-20-2008, 05:46 PM
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
02-20-2008, 06:29 PM
Whoops. That was a stupid mistake, however I am still receiving an error at the same point.

mikerickson
02-20-2008, 07:29 PM
It looks like you are trying to Evaluate an array function.

jacksonworld
02-20-2008, 08:11 PM
I gather that is not possible. Dammit.

jacksonworld
02-21-2008, 03:36 PM
Does anyone know if this is possible?

mdmackillop
02-21-2008, 03:39 PM
Can you post a workbook with sample data?

jacksonworld
02-21-2008, 04:37 PM
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
02-21-2008, 05:10 PM
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.

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

jacksonworld
02-21-2008, 05:40 PM
Hmm. Ok. I am indeed receiving a 3 there, but what does that mean? I am lost.

mdmackillop
02-22-2008, 12:45 AM
You substitute my code for your erroring code.

Bob Phillips
02-22-2008, 02:05 AM
I gather that is not possible. Dammit.

Yes it is.

jacksonworld
02-24-2008, 04:39 PM
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
02-25-2008, 12:25 PM
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

jacksonworld
02-25-2008, 04:04 PM
That's exactly what I tried but I receive an error at newWB.SaveAs "C:\Working Folder" & Format(.Range("A1:A230").Cells(mpDate, 1), "yyyy-mm-dd")

It's weird.

Thanks for continuing effort, by the way.

mdmackillop
02-25-2008, 04:35 PM
Have you checked the string value of that line? It doesn't look like a proper file path to me.

jacksonworld
02-25-2008, 09:25 PM
Aagh, I forgot the backslash after the folder.

Thank you so much. It works perfectly.

:clap: