Open Specific Sheet in Workbook
 

I was looking to do the following.

I have a workbook. 1 sheet

Column A is for Name

Column B is for Amount

Column C is for Account Type


The following is what I am trying to do.
By running a macro, I would like like excel to see if the specific
Account Type (Column C from Wrk book 1) Workbook is created (The
workbook would be in the same directory if it is created). If the
workbook exists, then go to a specific sheet - all sheets are named
after the Name in column a or workbook 1. Then put the amount in the
exact Name sheet in Cell A4.

Any help would be appreciated.
Thanks in advance

Josh


--
jhahes
------------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=382104

Open Specific Sheet in Workbook
 

Backup your workbook before trying this macro

I am assuming that cell c2 is the workbook name , c2 is the sheet name
, b2 is the amount.

if file exist , It will open workbook, select a sheet name and assign
the amount

else it will add workbook, select a sheet name and assign the amount
and save the workbook as the account name



Sub Macro1()
Dim acount_name, sheet_name, amount As Variant
account_name = Range("c2").Value 'account name
sheet_name = Range("a2").Value 'sheet names
amount = Range("b2").Value 'amount
On Error Resume Next
Workbooks.Open Filename:=Range("c2").Value 'open the file
If Err.Description < "" Then ' if file doesnot exist
Err.Clear
Workbooks.Add 'create new workbook
Sheets("sheet1").Name = sheet_name ' name the sheet1 as name
from column a
Range("a4").Value = amount ' assign the amount
ActiveWorkbook.SaveAs account_name 'save as accountname
Else ' if file exist
Sheets(sheet_name).Select 'select the sheet name
Range("a4").Value = amount 'assign the amount
End If
ActiveWorkbook.Save 'save
ActiveWorkbook.Close 'close
End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=382104