Hi, I can't work out what to put after (strSourceSheetName) to get it to
work, the worksheet it needs yto show is (Sheet1) - i.e. the standard
worksheet names.

'set object refs to both source and target
Set ShtSource = CurrentWrkBook.Worksheets(strSourceSheetName)
Set ShtTarget = NewWrkBook.Worksheets(strTargetSheetName)

Any help would be much appreciated.

Thanks

Windows XP Professional
Office 2003

As long as the variables strSourceSheetName and strTargetSheetName
contains the names of existing worksheets, and you have properly
declared your variables with the correct data types, your code should
work. I suspect the problem is in code that you did not elect to
include in your message. Something like the following should work:


Dim CurrentWrkBook As Excel.Workbook
Dim NewWrkBook As Excel.Workbook
Dim ShtSource As Excel.Worksheet
Dim ShtTarget As Excel.Worksheet
Dim strSourceSheetName As String
Dim strTargetSheetName As String

Set CurrentWrkBook = Workbooks("Book2")
Set NewWrkBook = Workbooks("Book3")
strSourceSheetName = "Sheet1"
strTargetSheetName = "Sheet2"

Set ShtSource = CurrentWrkBook.Worksheets(strSourceSheetName)
Set ShtTarget = NewWrkBook.Worksheets(strTargetSheetName)

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Sun, 30 Aug 2009 13:40:01 -0700, Bob1866
wrote:

Hi, I can't work out what to put after (strSourceSheetName) to get it to
work, the worksheet it needs yto show is (Sheet1) - i.e. the standard
worksheet names.

'set object refs to both source and target
Set ShtSource = CurrentWrkBook.Worksheets(strSourceSheetName)
Set ShtTarget = NewWrkBook.Worksheets(strTargetSheetName)

Any help would be much appreciated.

Thanks

Windows XP Professional
Office 2003