I am trying to create a simple macro that will run every time I open a
specific workbook in Excel 2002. When I look at the Excel helpfile on this
it tells me to start the Visual Basic Editor and search for help there. I do
that but find no help on this subject at all.

All I want to do is copy a long list of names from one workbook
("NameMaster" to another ("_PayrollSheet") for a validation list. I will
then copy the NameList to an area just below my data entry block to take
advantage of autofill.

I want to use the macro because the "NameMaster" workbook will change
frequently and I need the current list in the _PayrollSheet "NameList" every
time that workbook is opened.

Thanks for the help,
Carl

After opening the VB Editor double click on the item called "thisWorkbook".
in the window that opens place the following:

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Workbooks.Open FileName:="NameMaster" 'Make sure to include the full path
Sheets("mySheet").Range("A1:A100").Copy
Workbooks("_PayrollSheet").Sheets("thisSheet").Ran ge("A1").PasteSpecial _
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.cutcopymode = false
Workbooks("NameMaster").Activate
ActiveWorkbook.Close False 'closes "NameMaster" without saving

Msgbox "The file has been updated", vbInformation
End Sub

Make sure you change the ranges and sheet names to fit your needs.

"Carl" wrote:

I am trying to create a simple macro that will run every time I open a
specific workbook in Excel 2002. When I look at the Excel helpfile on this
it tells me to start the Visual Basic Editor and search for help there. I do
that but find no help on this subject at all.

All I want to do is copy a long list of names from one workbook
("NameMaster" to another ("_PayrollSheet") for a validation list. I will
then copy the NameList to an area just below my data entry block to take
advantage of autofill.

I want to use the macro because the "NameMaster" workbook will change
frequently and I need the current list in the _PayrollSheet "NameList" every
time that workbook is opened.

Thanks for the help,
Carl

"JNW" wrote:

After opening the VB Editor double click on the item called "thisWorkbook".
in the window that opens place the following:

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Workbooks.Open FileName:="NameMaster" 'Make sure to include the full path
Sheets("mySheet").Range("A1:A100").Copy
Workbooks("_PayrollSheet").Sheets("thisSheet").Ran ge("A1").PasteSpecial _
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.cutcopymode = false
Workbooks("NameMaster").Activate
ActiveWorkbook.Close False 'closes "NameMaster" without saving

Msgbox "The file has been updated", vbInformation
End Sub

Make sure you change the ranges and sheet names to fit your needs.

"Carl" wrote:

I am trying to create a simple macro that will run every time I open a
specific workbook in Excel 2002. When I look at the Excel helpfile on this
it tells me to start the Visual Basic Editor and search for help there. I do
that but find no help on this subject at all.

All I want to do is copy a long list of names from one workbook
("NameMaster" to another ("_PayrollSheet") for a validation list. I will
then copy the NameList to an area just below my data entry block to take
advantage of autofill.

I want to use the macro because the "NameMaster" workbook will change
frequently and I need the current list in the _PayrollSheet "NameList" every
time that workbook is opened.

Thanks for the help,
Carl

Private Sub Workbook_Open()
' INSERT_DESCRIPTION_HERE Macro
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Workbooks.Open FileName:="\YOUR_MASTER" ' Include full path
Range("A1:A100").Select
Selection.Copy
ActiveWindow.ActivateNext
Range("A1").Select
ActiveSheet.Paste
ActiveWindow.ActivatePrevious
Application.CutCopyMode = False
ActiveWindow.Close
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

You may need to play with this slightly but I think you'll find it easier to
use. If your sheets aren't protected delete the protect and unprotect lines.
If you prefer include the message box from JNW
Lou

"JNW" wrote:

After opening the VB Editor double click on the item called "thisWorkbook".
in the window that opens place the following:

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Workbooks.Open FileName:="NameMaster" 'Make sure to include the full path
Sheets("mySheet").Range("A1:A100").Copy
Workbooks("_PayrollSheet").Sheets("thisSheet").Ran ge("A1").PasteSpecial _
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.cutcopymode = false
Workbooks("NameMaster").Activate
ActiveWorkbook.Close False 'closes "NameMaster" without saving

Msgbox "The file has been updated", vbInformation
End Sub

Make sure you change the ranges and sheet names to fit your needs.

"Carl" wrote:

I am trying to create a simple macro that will run every time I open a
specific workbook in Excel 2002. When I look at the Excel helpfile on this
it tells me to start the Visual Basic Editor and search for help there. I do
that but find no help on this subject at all.

All I want to do is copy a long list of names from one workbook
("NameMaster" to another ("_PayrollSheet") for a validation list. I will
then copy the NameList to an area just below my data entry block to take
advantage of autofill.

I want to use the macro because the "NameMaster" workbook will change
frequently and I need the current list in the _PayrollSheet "NameList" every
time that workbook is opened.

Thanks for the help,
Carl

Thank you both for the help and tips...I'll try this tomorrow but it sure
looks good to me.

Carl

"Rookie 1st class" wrote:

Private Sub Workbook_Open()
' INSERT_DESCRIPTION_HERE Macro
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Workbooks.Open FileName:="\YOUR_MASTER" ' Include full path
Range("A1:A100").Select
Selection.Copy
ActiveWindow.ActivateNext
Range("A1").Select
ActiveSheet.Paste
ActiveWindow.ActivatePrevious
Application.CutCopyMode = False
ActiveWindow.Close
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

You may need to play with this slightly but I think you'll find it easier to
use. If your sheets aren't protected delete the protect and unprotect lines.
If you prefer include the message box from JNW
Lou

"JNW" wrote:

After opening the VB Editor double click on the item called "thisWorkbook".
in the window that opens place the following:

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Workbooks.Open FileName:="NameMaster" 'Make sure to include the full path
Sheets("mySheet").Range("A1:A100").Copy
Workbooks("_PayrollSheet").Sheets("thisSheet").Ran ge("A1").PasteSpecial _
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.cutcopymode = false
Workbooks("NameMaster").Activate
ActiveWorkbook.Close False 'closes "NameMaster" without saving

Msgbox "The file has been updated", vbInformation
End Sub

Make sure you change the ranges and sheet names to fit your needs.

"Carl" wrote:

I am trying to create a simple macro that will run every time I open a
specific workbook in Excel 2002. When I look at the Excel helpfile on this
it tells me to start the Visual Basic Editor and search for help there. I do
that but find no help on this subject at all.

All I want to do is copy a long list of names from one workbook
("NameMaster" to another ("_PayrollSheet") for a validation list. I will
then copy the NameList to an area just below my data entry block to take
advantage of autofill.

I want to use the macro because the "NameMaster" workbook will change
frequently and I need the current list in the _PayrollSheet "NameList" every
time that workbook is opened.

Thanks for the help,
Carl