MrExcel Publishing
Your One Stop for Excel Tips & Solutions

if statement in macro


Posted by Steve on October 10, 2001 9:50 AM

I have a macro that opens a file (b) and then copies a sheet to it from the currently open file (a). My problem is that I want it work also if 1) the second file (b) is already open or if 2) the second file (b) already has a sheet by the same name as the one I am trying to copy to it.

Dim vendorbook, loc
loc = "C:\windows\desktop\pps\"
vendorbook = Range("b6").Text & ".xls"

'
Workbooks.Open Filename:=loc & vendorbook
'

Workbooks("PPSRetirement.xls").Activate
Worksheets("Reconcile").Copy After:=Workbooks(vendorbook).Worksheets("Introduction")
Workbooks(vendorbook).Activate
Worksheets("Reconcile").Activate
Dim vendor As String
vendor = Range("e1").Text

'
Sheets("reconcile").Name = vendor
'

Workbooks(vendorbook).Save
Workbooks(vendorbook).Close

thanks for any help with the code.

Steve


Posted by Matt on October 10, 2001 2:02 PM

Hope the following would solve your problem... ;-)

Openning Workbook

On Error Resume Next
Workbooks(vendorbook).Activate
If Err.Number <> 0 Then
Err.Number = 0
Workbooks.Open FileName:=loc & vendorbook
End If

Sheet Copy
Workbooks("PPSRetirement.xls").Activate
Worksheets("Reconcile").Copy After:=Sheets(1)
vendor = Range("E1").Text
ActiveSheet.Name = vendor
Worksheets(vendor).Move After:=Workbooks(vendorbook).Worksheets("Introduction")
Workbooks(vendorbook).Activate
Workbooks(vendorbook).Save
Workbooks(vendorbook).Close

Posted by Steve on October 11, 2001 7:29 AM

Thanks! It worked great!