Select a Wookbook created using a variable

TeddyBear

I run a macro on some data and place a Vendor number in a variable
vCurrVendNos I then add a workbook into the spreadsheet with the value in the
variable. e.g. Sheets.Add.Name = vCurrVendNos
Later in the copy I wish to select the workbook again to paste some
information into it. I amtrying the following Sheets("101030").Select
(that is the name of the sheet but Sheets(vCurrVendNos).Select
does not work.

How can I achieve what Sheets("101030").Select does except that I wish to
use a variable. Help.

Og

You are more likely to received informed responses to your Excel programming
queries if you post where the Excel programming gurus hang out:
microsoft.public.excel.programming
Steve

"TeddyBear" <> wrote in message
news:...
>I run a macro on some data and place a Vendor number in a variable
> vCurrVendNos I then add a workbook into the spreadsheet with the value in
> the
> variable. e.g. Sheets.Add.Name = vCurrVendNos
> Later in the copy I wish to select the workbook again to paste some
> information into it. I amtrying the following
> Sheets("101030").Select
> (that is the name of the sheet but Sheets(vCurrVendNos).Select
> does not work.
>
> How can I achieve what Sheets("101030").Select does except that I wish to
> use a variable. Help.
>
>
>
>

Og

You are more likely to received informed responses to your Excel programming
queries if you post where the Excel programming gurus hang out:
microsoft.public.excel.programming
Steve

"TeddyBear" <> wrote in message
news:...
>I run a macro on some data and place a Vendor number in a variable
> vCurrVendNos I then add a workbook into the spreadsheet with the value in
> the
> variable. e.g. Sheets.Add.Name = vCurrVendNos
> Later in the copy I wish to select the workbook again to paste some
> information into it. I amtrying the following
> Sheets("101030").Select
> (that is the name of the sheet but Sheets(vCurrVendNos).Select
> does not work.
>
> How can I achieve what Sheets("101030").Select does except that I wish to
> use a variable. Help.
>
>
>
>

Harlan Grove

"TeddyBear" <> wrote...
>I run a macro on some data and place a Vendor number in a variable
>vCurrVendNos I then add a workbook into the spreadsheet with the value
>in the variable. e.g.
>
> Sheets.Add.Name = vCurrVendNos

First terminology: you add a workSHEET to a workBOOK.

Ask application-specific questions in application-specific newsgroups, in
this case



vCurrVendNos is a VBA variable, and as such isn't persistent. When you close
the workbook or reset the VBA project, the values stored global or local
static variables are lost. Values stored in regular local variables are lost
as soon as the procedure in which they're defined ends.

>Later in the copy I wish to select the workbook again to paste some
>information into it. I amtrying the following
>
> Sheets("101030").Select
>
>(that is the name of the sheet but
>
> Sheets(vCurrVendNos).Select
>
>does not work.
....

Are you doing this isn the same macro? If so, has the value of vCurrVendNos
been modified? If not, how did you initialize it before using it?

Harlan Grove

"TeddyBear" <> wrote...
>I run a macro on some data and place a Vendor number in a variable
>vCurrVendNos I then add a workbook into the spreadsheet with the value
>in the variable. e.g.
>
> Sheets.Add.Name = vCurrVendNos

First terminology: you add a workSHEET to a workBOOK.

Ask application-specific questions in application-specific newsgroups, in
this case



vCurrVendNos is a VBA variable, and as such isn't persistent. When you close
the workbook or reset the VBA project, the values stored global or local
static variables are lost. Values stored in regular local variables are lost
as soon as the procedure in which they're defined ends.

>Later in the copy I wish to select the workbook again to paste some
>information into it. I amtrying the following
>
> Sheets("101030").Select
>
>(that is the name of the sheet but
>
> Sheets(vCurrVendNos).Select
>
>does not work.
....

Are you doing this isn the same macro? If so, has the value of vCurrVendNos
been modified? If not, how did you initialize it before using it?

TeddyBear

Hi the variable vCurrVendNos is initialised by selecting a value from a cell
in the workbook. It's value is 101030 when I step thru the macro. This is
also executed in the same subroutine in the same module.

"Harlan Grove" wrote:

> "TeddyBear" <> wrote...
> >I run a macro on some data and place a Vendor number in a variable
> >vCurrVendNos I then add a workbook into the spreadsheet with the value
> >in the variable. e.g.
> >
> > Sheets.Add.Name = vCurrVendNos
>
> First terminology: you add a workSHEET to a workBOOK.
>
> Ask application-specific questions in application-specific newsgroups, in
> this case
>
>
>
> vCurrVendNos is a VBA variable, and as such isn't persistent. When you close
> the workbook or reset the VBA project, the values stored global or local
> static variables are lost. Values stored in regular local variables are lost
> as soon as the procedure in which they're defined ends.
>
> >Later in the copy I wish to select the workbook again to paste some
> >information into it. I amtrying the following
> >
> > Sheets("101030").Select
> >
> >(that is the name of the sheet but
> >
> > Sheets(vCurrVendNos).Select
> >
> >does not work.
> ....
>
> Are you doing this isn the same macro? If so, has the value of vCurrVendNos
> been modified? If not, how did you initialize it before using it?
>
>
>

TeddyBear

Hi the variable vCurrVendNos is initialised by selecting a value from a cell
in the workbook. It's value is 101030 when I step thru the macro. This is
also executed in the same subroutine in the same module.

"Harlan Grove" wrote:

> "TeddyBear" <> wrote...
> >I run a macro on some data and place a Vendor number in a variable
> >vCurrVendNos I then add a workbook into the spreadsheet with the value
> >in the variable. e.g.
> >
> > Sheets.Add.Name = vCurrVendNos
>
> First terminology: you add a workSHEET to a workBOOK.
>
> Ask application-specific questions in application-specific newsgroups, in
> this case
>
>
>
> vCurrVendNos is a VBA variable, and as such isn't persistent. When you close
> the workbook or reset the VBA project, the values stored global or local
> static variables are lost. Values stored in regular local variables are lost
> as soon as the procedure in which they're defined ends.
>
> >Later in the copy I wish to select the workbook again to paste some
> >information into it. I amtrying the following
> >
> > Sheets("101030").Select
> >
> >(that is the name of the sheet but
> >
> > Sheets(vCurrVendNos).Select
> >
> >does not work.
> ....
>
> Are you doing this isn the same macro? If so, has the value of vCurrVendNos
> been modified? If not, how did you initialize it before using it?
>
>
>

Harlan Grove

"TeddyBear" <> wrote...
>Hi the variable vCurrVendNos is initialised by selecting a value from a
>cell in the workbook. It's value is 101030 when I step thru the macro.
>This is also executed in the same subroutine in the same module.
....

Standard response: show the entire macro. The highest likelihood is that
something is modifying the value of the variable, and if it were simple to
spot you would have already. If it's not simple for you to spot, you need to
let others try.

Harlan Grove

"TeddyBear" <> wrote...
>Hi the variable vCurrVendNos is initialised by selecting a value from a
>cell in the workbook. It's value is 101030 when I step thru the macro.
>This is also executed in the same subroutine in the same module.
....

Standard response: show the entire macro. The highest likelihood is that
something is modifying the value of the variable, and if it were simple to
spot you would have already. If it's not simple for you to spot, you need to
let others try.

TeddyBear

Hi Harlan, You asked for it... Here it is

Sub Create_Vendor_Spreadsheet()

' Error Handling Routine
On Error GoTo ErrorHandler

' Set Row and Column to the First Vendor number cell value
vCol = 12
vrow = 12

' Start row of Current Vendor
vStartVendrow = 12
' Last row for Current Vendor
vLastVendrow = 12

' Store in variable vCurrVendNos the first Current Vendor Number
VCurrVendNos = Cells(vrow, vCol)

' Store in verialbe vCurrVendName the Name of the Vendor
vCurrVendName = Cells(vrow, 13)

' Select and Group records for the same Vendor number
Do
While (Cells(vrow, vCol)) <> " "
If VCurrVendNos = Cells(vrow, vCol) Then
vrow = vrow + 1 '
Move to the next Vendor number in the next row
Else
' Prepare Workbook Layout
Call Prepare_New_Workbook
vLastVendrow = vrow - 1 '
Set vLastVendrow to Previous Vendor's last row
Worksheets("Master Warranty Sheet").Activate
' Unable to select Range using variables
Range(vStartVendrow, vLastVendrow).Select '
Select Current Vendor Rows and Columns

vStartVendrow = vrow '
Set vStartVendrow to new Vendor row
Application.CutCopyMode = False
Selection.Copy '
Copy Previous Vendors Information
Sheets("101030").Select '
Select newly create Spreadsheet
Range("A12").Select
ActiveSheet.Paste '
Paste the vendors information into new Sheet
VCurrVendNos = Cells(vrow, vCol) '
Set vCurrVendNos to new current Vendor Number
End If
vrow = vrow + 1
' End DO WHILE If next Row has a Vendor number that is Blank
Wend
Loop Until Cells(vrow, vCol) = " "
Exit Sub

ErrorHandler:
'Pass error to messagebox with error message
Action = MsgBox("Excel encountered a problem. Please contact Information
Services for assistance. Quote Create_Vendor_Spreadsheet Module1 Failed.")
End Sub


Sub Prepare_New_Workbook()

' Create new Workbook with Vendor number and copy Master Headings into new
Workbook
vCurrVendName = Cells(vStartVendrow, 13) '
Set vCurrVendName to Vendors name
Sheets.Add.Name = VCurrVendNos '
Add New Spreqadsheet with a name of Vendor number
Sheets("Master Warranty Sheet").Select '
Select Master Warranty Sheet
Range("A9:N10").Select '
Select Data Heading from Master Warranty Sheet
Selection.Copy '
Copy Headings
' Hardcoded sheet - this doesn't work Sheets(vCurrVendNos).Select
Sheets("101030").Select '
Select newly created Workbook
Range("A9").Select '
Position where paste wilol take place
ActiveSheet.Paste '
Paste the copied data to new Workbook
' Create Workbook Title using Vendor Name
ActiveSheet.Shapes.AddTextEffect(msoTextEffect9, vCurrVendName &
Chr(13) & "" & Chr(10) & "", _
"Arial Black", 36#, msoFalse, msoFalse, 261#, 182.25).Select
Selection.ShapeRange.ScaleHeight 0.73, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.IncrementLeft -211.5
Selection.ShapeRange.IncrementTop -166.5
Range("A11").Select

End Sub

Note When the commands are copied into the main Sub Routine
Create_Vendor_Spreadsheet from the Prepare_New_Workbook sub it works fine.
When I make the call to Prepare_New_Workbook it loses it value in
vCurrVendName variable.



"Harlan Grove" wrote:

> "TeddyBear" <> wrote...
> >Hi the variable vCurrVendNos is initialised by selecting a value from a
> >cell in the workbook. It's value is 101030 when I step thru the macro.
> >This is also executed in the same subroutine in the same module.
> ....
>
> Standard response: show the entire macro. The highest likelihood is that
> something is modifying the value of the variable, and if it were simple to
> spot you would have already. If it's not simple for you to spot, you need to
> let others try.
>
>
>