Select a Wookbook created using a variable

T

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

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
 
H

Harlan Grove

TeddyBear said:
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
Click to expand...

First terminology: you add a workSHEET to a workBOOK.

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

http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.programming

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.
Click to expand...
....

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?
 
T

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

Harlan Grove

TeddyBear said:
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.
Click to expand...
....

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

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

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