Hi,

I have a worksheet 1 in a workbook A. I have a code to move the worksheet 1
into a new workbook. I want to name the new workbook using the two pieces of
data from cells A1 and C1 from Workbook A. For example, cells A1 and C1 have
ABC and 123 in Workbook A, respective. Then the new workbook name will be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name the new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls" can not
be accessed. It didn't save the file, and the new workbook is left open.

Please help. Thanks.

Which operating system and version of Excel and are you using?

Dan Dungan

On Sep 26, 11:42 am, AccessHelp
wrote:
Hi,

I have a worksheet 1 in a workbook A. I have a code to move the worksheet 1
into a new workbook. I want to name the new workbook using the two pieces of
data from cells A1 and C1 from Workbook A. For example, cells A1 and C1 have
ABC and 123 in Workbook A, respective. Then the new workbook name will be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name the new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls" can not
be accessed. It didn't save the file, and the new workbook is left open.

Please help. Thanks.

Good morning Dan,

I am using Windows XP and Office 2003. Thanks.

"dan dungan" wrote:

Which operating system and version of Excel and are you using?

Dan Dungan

On Sep 26, 11:42 am, AccessHelp
wrote:
Hi,

I have a worksheet 1 in a workbook A. I have a code to move the worksheet 1
into a new workbook. I want to name the new workbook using the two pieces of
data from cells A1 and C1 from Workbook A. For example, cells A1 and C1 have
ABC and 123 in Workbook A, respective. Then the new workbook name will be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name the new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls" can not
be accessed. It didn't save the file, and the new workbook is left open.

Please help. Thanks.

Try this:

With Sheets(1)
FName1 = .Cells(1)
FName2 = .Cells(3)
End With


RBS



"AccessHelp" wrote in message
...
Hi,

I have a worksheet 1 in a workbook A. I have a code to move the worksheet
1
into a new workbook. I want to name the new workbook using the two pieces
of
data from cells A1 and C1 from Workbook A. For example, cells A1 and C1
have
ABC and 123 in Workbook A, respective. Then the new workbook name will be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name the new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls" can not
be accessed. It didn't save the file, and the new workbook is left open.

Please help. Thanks.

Hi RBS,

Thank you for the code. When I try it, I got an error "Run-time Error 6:
overflow".

Thanks.

"RB Smissaert" wrote:

Try this:

With Sheets(1)
FName1 = .Cells(1)
FName2 = .Cells(3)
End With


RBS



"AccessHelp" wrote in message
...
Hi,

I have a worksheet 1 in a workbook A. I have a code to move the worksheet
1
into a new workbook. I want to name the new workbook using the two pieces
of
data from cells A1 and C1 from Workbook A. For example, cells A1 and C1
have
ABC and 123 in Workbook A, respective. Then the new workbook name will be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name the new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls" can not
be accessed. It didn't save the file, and the new workbook is left open.

Please help. Thanks.

Is there a worksheet open when that code runs?

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you for the code. When I try it, I got an error "Run-time Error 6:
overflow".

Thanks.

"RB Smissaert" wrote:

Try this:

With Sheets(1)
FName1 = .Cells(1)
FName2 = .Cells(3)
End With


RBS



"AccessHelp" wrote in message
...
Hi,

I have a worksheet 1 in a workbook A. I have a code to move the
worksheet
1
into a new workbook. I want to name the new workbook using the two
pieces
of
data from cells A1 and C1 from Workbook A. For example, cells A1 and
C1
have
ABC and 123 in Workbook A, respective. Then the new workbook name will
be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name the
new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls" can
not
be accessed. It didn't save the file, and the new workbook is left
open.

Please help. Thanks.

Good morning RBS,

Yes, Workbook A and the new workbook will be opened. Basically, I have a
worksheet with a command button in Workbook A. When the user clicks on the
button, it will bring up a workbook containing the macro/code and will
execute the code. Once the code is executed, the macro workbook will close.
However, Workbook A will be opened at all time. The new workbook will only
open during the execution (during the move) and save it as the name that we
are trying to accomplish.

Thanks.

"RB Smissaert" wrote:

Is there a worksheet open when that code runs?

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you for the code. When I try it, I got an error "Run-time Error 6:
overflow".

Thanks.

"RB Smissaert" wrote:

Try this:

With Sheets(1)
FName1 = .Cells(1)
FName2 = .Cells(3)
End With


RBS



"AccessHelp" wrote in message
...
Hi,

I have a worksheet 1 in a workbook A. I have a code to move the
worksheet
1
into a new workbook. I want to name the new workbook using the two
pieces
of
data from cells A1 and C1 from Workbook A. For example, cells A1 and
C1
have
ABC and 123 in Workbook A, respective. Then the new workbook name will
be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name the
new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls" can
not
be accessed. It didn't save the file, and the new workbook is left
open.

Please help. Thanks.

Yes, there is a workbook, but is there a sheet at the time that code runs?
Maybe you should post the relevant code.

RBS


"AccessHelp" wrote in message
...
Good morning RBS,

Yes, Workbook A and the new workbook will be opened. Basically, I have a
worksheet with a command button in Workbook A. When the user clicks on
the
button, it will bring up a workbook containing the macro/code and will
execute the code. Once the code is executed, the macro workbook will
close.
However, Workbook A will be opened at all time. The new workbook will
only
open during the execution (during the move) and save it as the name that
we
are trying to accomplish.

Thanks.

"RB Smissaert" wrote:

Is there a worksheet open when that code runs?

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you for the code. When I try it, I got an error "Run-time Error
6:
overflow".

Thanks.

"RB Smissaert" wrote:

Try this:

With Sheets(1)
FName1 = .Cells(1)
FName2 = .Cells(3)
End With


RBS



"AccessHelp" wrote in message
...
Hi,

I have a worksheet 1 in a workbook A. I have a code to move the
worksheet
1
into a new workbook. I want to name the new workbook using the two
pieces
of
data from cells A1 and C1 from Workbook A. For example, cells A1
and
C1
have
ABC and 123 in Workbook A, respective. Then the new workbook name
will
be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name
the
new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls"
can
not
be accessed. It didn't save the file, and the new workbook is left
open.

Please help. Thanks.

HI.
Try:

Public Sub prova()
Dim newname As String
With Sheets(1)
newname = .Cells(1) & .Cells(3)
End With
MsgBox newname
End Sub

Regards
Eliano
"AccessHelp" wrote:

Hi,

I have a worksheet 1 in a workbook A. I have a code to move the worksheet 1
into a new workbook. I want to name the new workbook using the two pieces of
data from cells A1 and C1 from Workbook A. For example, cells A1 and C1 have
ABC and 123 in Workbook A, respective. Then the new workbook name will be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name the new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls" can not
be accessed. It didn't save the file, and the new workbook is left open.

Please help. Thanks.

Good morning eliano,

Thanks for the code.

When I tried your code, I got an error:

"'Test.xls' cannot be accessed. The file may be read-only, or you may be
trying to access a read-only location. Or, the server the document is
stored on may not be responding."

If you don't mind, please read my today's response to RB Smissaert above for
the process of my code.

Thanks.

"eliano" wrote:

HI.
Try:

Public Sub prova()
Dim newname As String
With Sheets(1)
newname = .Cells(1) & .Cells(3)
End With
MsgBox newname
End Sub

Regards
Eliano
"AccessHelp" wrote:

Hi,

I have a worksheet 1 in a workbook A. I have a code to move the worksheet 1
into a new workbook. I want to name the new workbook using the two pieces of
data from cells A1 and C1 from Workbook A. For example, cells A1 and C1 have
ABC and 123 in Workbook A, respective. Then the new workbook name will be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name the new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls" can not
be accessed. It didn't save the file, and the new workbook is left open.

Please help. Thanks.