Help with a syntax problem

[Guest]

Hello, all:

I'm using Excel 2003. I'm trying to write a very simple program to copy
cells from one worksheet to another based upon the two worksheets having at
least one common column of data.

The user needs to enter onto a sheet called COPIER the names of the source
workbook and worksheet as well as the recipient workbook and worksheet. I
then want to use the contents of those cells in the routine. Problem is, I
don't know the proper syntax for loading them into a Set statement. Here is
part of the code:

**************************************

Dim COPIER, SOURCE, RECIPIENT As Object

Set COPIER = ActiveSheet

SBOOKNAME = COPIER.Cells(9, 4)'this is the name of the source workbook

SSHEETNAME = COPIER.Cells(11, 4)'this is the name of the source worksheet

RBOOKNAME = COPIER.Cells(9, 4)'name of recipient workbook

RSHEETNAME = COPIER.Cells(11, 4)'name of recipient worksheet

'Here is the problem code (I tried two variations, neither worked):

Set SOURCE = Workbooks(SBOOKNAME).Sheets(SSHEETNAME)'gives a subscript out
of range error

Set RECIPIENT =
Workbooks(COPIER.Range("K9")).Sheets(COPIER.Range("K11"))'gives a type
mismatch error. I also tried this with .Cells(), which didn't work either

**************************************

Anyone know the proper syntax for reading from cells and including the cell
contents in a Set statement?

Thanks in advance,
MARTY

 

[Fredrik Wahlgren]

Hello, all:

I'm using Excel 2003. I'm trying to write a very simple program to copy
cells from one worksheet to another based upon the two worksheets having at
least one common column of data.

The user needs to enter onto a sheet called COPIER the names of the source
workbook and worksheet as well as the recipient workbook and worksheet. I
then want to use the contents of those cells in the routine. Problem is, I
don't know the proper syntax for loading them into a Set statement. Here is
part of the code:

**************************************

Dim COPIER, SOURCE, RECIPIENT As Object

Set COPIER = ActiveSheet

SBOOKNAME = COPIER.Cells(9, 4)'this is the name of the source workbook

SSHEETNAME = COPIER.Cells(11, 4)'this is the name of the source worksheet

RBOOKNAME = COPIER.Cells(9, 4)'name of recipient workbook

RSHEETNAME = COPIER.Cells(11, 4)'name of recipient worksheet

'Here is the problem code (I tried two variations, neither worked):

Set SOURCE = Workbooks(SBOOKNAME).Sheets(SSHEETNAME)'gives a subscript out
of range error

Set RECIPIENT =
Workbooks(COPIER.Range("K9")).Sheets(COPIER.Range("K11"))'gives a type
mismatch error. I also tried this with .Cells(), which didn't work either

**************************************

Anyone know the proper syntax for reading from cells and including the cell
contents in a Set statement?

Thanks in advance,
MARTY

Are you trying to read the contents of an external sheet? In tha case, I
suggest you use the EVALUATE function instead.

/Fredrik

 

[Guest]

Thanks for the reply, Fredrik.

No, I'm not trying to read from an external sheet. I'm trying to read a
couple of cells on the ActiveSheet and incorporate them into a Set statement.

After a while on the message boards, I finally go this to work:

Set SOURCE = Workbooks(CStr(Workbooks("Ex-cell
Copier.xls").Sheets("Copier").Range("D9"))).Sheets(CStr(Workbooks("Ex-cell
Copier.xls").Sheets("Copier").Range("D11")))

but the question I'm wrestling with now is what if I want to change the name
of the active workbook from "Ex-cell Copier.xls" (or the sheet name) to
something else.

Is there a way to write this same statement using ActiveWorkbook.Name and
ActiveSheet?

I tried the following:

SOURCE =
ActiveWorkbook.Sheets(CStr(Workbooks(ActiveWorkbook.Name).Sheets(ActiveSheet).Range("D11")))

but is doesn't work. Other thoughts anyone?

Thanks.