Select a Wookbook created using a variable

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.
>
>
>

TeddyBear

Hi Guys,

Thanks to Tom Ogilvy I was able to find a way of doing what I needed to that
will enable me to achieve what I require from public.excel.programming site
Here it is...

Tom said in a previous response to another question

"When you do

worksheets.Add

the sheet just created is the activesheet. If you will be changing another
sheet to the activesheet, just set a reference to this new sheet

Dim shNew as Worksheet
Dim shOld as Worksheet
set shOld = Activesheet
worksheets.Add
set shNew = Activesheet

Now shOld hold a referene to the original activesheet and shNew a reference
to the newly added sheet. (as an example)".

Because I add my sheets using a variable I now store the reference of the
new sheet immediately after my add and then activate the sheet when I need
using this method. I do not need to know the Old sheet as it has a static
name and can be hardcoded. In this way I can switch between the two
WorkSheets.

Thx Tom for your answer to another question which helped me.....


"Og" wrote:

> 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.
> >
> >
> >
> >
>
>
>

TeddyBear

Hi Guys,

Thanks to Tom Ogilvy I was able to find a way of doing what I needed to that
will enable me to achieve what I require from public.excel.programming site
Here it is...

Tom said in a previous response to another question

"When you do

worksheets.Add

the sheet just created is the activesheet. If you will be changing another
sheet to the activesheet, just set a reference to this new sheet

Dim shNew as Worksheet
Dim shOld as Worksheet
set shOld = Activesheet
worksheets.Add
set shNew = Activesheet

Now shOld hold a referene to the original activesheet and shNew a reference
to the newly added sheet. (as an example)".

Because I add my sheets using a variable I now store the reference of the
new sheet immediately after my add and then activate the sheet when I need
using this method. I do not need to know the Old sheet as it has a static
name and can be hardcoded. In this way I can switch between the two
WorkSheets.

Thx Tom for your answer to another question which helped me.....


"Og" wrote:

> 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.
> >
> >
> >
> >
>
>
>