Assigning Variable As Workbook
May 15, 2014
So I have a macro that is saving new excel files based on month and year. I want to assign a variable but I keep getting an error.
Here's what I have:
***********
Sub AssignVariables()
Dim Rebates as Workbook
Dim Master as Workbook
Dim month as string
[Code]....
The last two lines are the errors. I want to type in:
Rebates.Activate
Master.Activate
But the error occurs as it gets to the last two lines. By the way, all these two documents are open.
View 4 Replies
ADVERTISEMENT
Jul 14, 2014
I am trying to open a workbook, assign it a variable and use that variable later. here is what i have
[Code] ......
When I try to activate it later on in another procedure using:
[Code] copy to clipboard
I receive an "Automation Error"
I have tried declaring it as a public variable, even a global variable and cannot get this to work.
All I'm really wanting to do is open a workbook (where the name will change depending on the book being opened), assign that workbook a variable name, and use it later in the code.
View 7 Replies
View Related
Dec 22, 2011
Ive wrote some code that goes into a workbook. This code then opens up a "master" workbook which has 18 blank tabs in it and then proceeds to open up 18 other named workbooks one at a time and copy some date from these workbooks to the master (i.e workbook 1's data goes to the master workbook on tab "1".)
I have this working no problem but here is my snag.
The workbook name changes every week to correspond with the date (i.e 1_14DEC2011.xls then 1_21DEC2011.xls).
Ideally id like to be able to create a variable for the latter half of the filename so that i can apply this variable to each filepath but i cant quite get it to work.
A small sample of my code is below (and yes i know it is very blunt but so is my knowledge at this stage!)
Code:
Sub collate()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
[Code]....
View 2 Replies
View Related
Nov 19, 2008
to assign a variable to equal a Constant variable, then I need to find the last unused row on the worksheet, then paste that variable down the column (1-12200 or so rows). I also need to assign Strings for the first two Rows in the target column.
View 14 Replies
View Related
Oct 31, 2006
The relevant code is below. I can post it all if necessary -- it's about 30 lines though. Can anyone recognize what the problem might be?
MsgBox "vSh " & vSh & " vOp " & vOp & "vCash " & vCash
vCash = vSh * vOp
MsgBox "vCash " & vCash
The MsgBox's are merely for debugging. When the code above runs, the first MsgBox I receive states similar to:
"vSh 14.238964 vOp 45.23 vCash 1000"
The second MsgBox, from after the multiplication, states:
"vCash 1000"
For some reason the vCash variable just will not set. At the beginning of the code, I declared each variable as Dim var, meaning as a variant, and whenever I debug by halting during a MsgBox display, each of the variables appear to be the same types: Variant/Double.
View 6 Replies
View Related
Feb 13, 2007
G:G contains a list of integars, though some cells are blank; lets say 75 of the 100 cells in data range are < 0. I want a macro which copies a range where the number of rows = the number of values in G:G. my Macro:
Sub myMacro()
Dim rowRange As Integer
rowRange = Count("G:G")
End Sub
This, as you guessed, comes back with an error. Sub or Function not defined. Anyone the proper syntax for assigning a formula to a variable?
View 5 Replies
View Related
Feb 22, 2013
I tried to count rows from one sheet and to put this value as counter in VBA For-Next loop.
VB:
Sub Copy_ID()
'
' Copy_ID Macro
[Code]...
It ends with "Invalid qualifier" error on the counter.Formula line. I attached sample excel with macro that I listed here.
View 5 Replies
View Related
Jun 2, 2009
I am assigning the result of a vlookup to a variable that is defined as Variant. I then need to use that variable in a calculation.
View 5 Replies
View Related
Dec 7, 2009
I’m attempting designate the cell which will then determine the start of the current region, to be copied and pasted to another sheet. I’m receiving an “ERROR 1004” , Method Range of object_ Worksheet Failed.
View 4 Replies
View Related
Dec 27, 2006
I'm attempting to create a macro that will look at the total in column (K) and send an e-mail to two different addresses, depending on the amount. If the amount is over $10,000 then one address (over@macro.com) if under, then the other (under@macro.com)
My main problem is that I never know what cell the total is going to be in.
I currently have all quotes going back to the person that sent in the request, no matter what the amount. Their e-mail is in the sheet.
So what I think I need to do, is find the last cell in column K with data and assign it a variable. If that variable is over $10,000 then I can send the e-mail to a hard coded address. If it's over, I just use my original code.
View 9 Replies
View Related
Jul 3, 2009
I would like to assign listbox members to a public variable (quite the opposite of what you normally do). I receive the error message "type mismatch".
Public MemberLB as Variant
Public Sub ListBoxTest()
n = ListBox_target.ListCount
For iCnt = 1 To n
MemberLB(iCnt) = ListBox_target.List(iCnt - 1)
Next iCnt
End Sub
View 9 Replies
View Related
Jul 20, 2007
I have a formula which assigns a points score to letters in a range and adds them up. In the example below F=0, P=6, M=12 & D=18. =IF( COUNTIF(AT5:BE5,"="""),"",SUM(COUNTIF(AT5:BE5,"=F")*0,COUNTIF(AT5:BE5,"=P")*6,COUNTIF(AT5:BE5,"=M")*12,COUNTIF(AT5:BE5,"=D")*18)). Thus if F, P, M & D were in cells A1:D1 the result would be 0+6+12+18=36.
My aim is to be able to customise the values of F, P, M & D, using a table and a cell value. See the table below, where the first number in each row represents a cell value which the user enters into BH1, the second, third, fourth and fifth numbers represents the values assigned to the letters F, P, M & D.
10 0 1 2 3
30 0 3 6 9
60 0 6 12 18
90 0 9 18 27
120 0 12 24 36
Some examples of expected output: user enters 10 into BH1 and then enters F, P, M & D in cells A1:D1 the result would be 0+1+2+3=6. user enters 90 into BH1 and then enters F, P, M & D in cells A1:D1 the result would be 0+9+18+27=54.
View 4 Replies
View Related
Sep 17, 2013
I am working on a customer report template that generates our customers reports and will send them out automatically.
This issue I have now is that the system that generates the raw data for these reports only lists the Customers name in a column with an entry for each line of data, the thing is though that the system has lots of variations of the customers name, even more so if that customers has different departments.
What I need to do is from this list of customer names, I need to automatically figure out what the "common" name is or main name so to speak, and then make a variable using the correct full name, which will be used later on in the code to import correct logos, and direct the reports to the correct people.
Here is a quick example of what data we get raw from the system:
Customer Name:
John Build
Johns Buildings
Johns Ltd Building
Johns Plumbing Department
Glass Doors Ltd A Department of Johns Buildings
Johns Building Corporation
Hole In One Golf Range
This is just an example, we have thousands of clients, so the length, number of words etc can change alot. Ideally I from a list similar to that I would get a full proper result of "Johns Buildings Ltd" for example, this would then be in a variable to be used in code from then on to reference doing certain things with the reports of Johns Buildings Ltd.
You'll notice there is one name "Hole In One Golf Range" that seems to have no relation at all, this is correct, ideally I would also like to build in some error checking into the code, so that rows like that that have nothing to do with the others would get deleted.
So how would you amazing VBA gurus go about working with data like this? I'd prefer a more general answer with explainations that just straight code, as I'm sure I will have to adapt the hell out of it for it to be useful in context.
View 9 Replies
View Related
Jul 19, 2012
I am writing a code wherein I want to assign specific excel range to a declared variable and then paste this excel in outlook message ody but I am finding difficulty in assigning that range to variable " brng"
I think code is right and issue is there in excel setting.
Code:
Sub mailer()
'
'
'
Dim Ash As Worksheet
Dim brng As Range
Dim OutApp As Object
Dim OutMail As Object
'Windows("Copy of FF RPL REPORT_JULY").Activate
[Code] .......
View 1 Replies
View Related
Mar 3, 2014
I have a spreadsheet where a user inputs a client name (ClientName). I will create a new Workbook with the ClientName along with the current date. 2014-03-03John Doe.xls
I am trying to now input data into 2014-03-03John Doe.xls by specifying it in VBA but I can't seem to reference it properly.
DateStr = Format(Date, "yyyy-mm-d")
Comment = Application.InputBox("Type in Client Name ", "File Name")
WBName = DateStr & Comment
Application.Workbooks(WBName).Sheets(1).Range(A2).Value = "QTY"
But it gives me an error.
View 4 Replies
View Related
May 19, 2014
I have this code which opens another workbook and defines the two open workbooks as MainWkbk and NextWkbk:
Code:
Private Sub wbo()
'open file selected from dropdown on Macros tab and define the two open workbooks
Dim singlepath As String
[Code]....
This works great for going between the open workbooks within the same sub but I also need to be able to use MainWkbk/NextWkbk in another sub within the same module and cannot seem to get it to work!
I tried adding this to the top of the module;
Private MainWkbk As Workbook
Private NextWkbk As Workbook
but this doesn't work, it always says the variable is not set when in the next sub.
I also tried
wbo(Mainwkbk).Activate
within the next sub but this is not right either.
View 1 Replies
View Related
Nov 11, 2011
I have an Active sheet which is
MySName = ActiveSheet.Name
Here MySName gives the active sheet name.
I require 1 more variable to take this MySName and assign it to a workbook name and make it is as .xls as i required this workbook name to be used later to close the workbook
For ex:
MySName = ActiveSheet.Name
this gives MySName =aaaa
Now i want like MyWBook = aaaa.xls
But aaaa i want it from variable as we cannot hardcode it.
View 1 Replies
View Related
Jul 2, 2014
I have a Macro that opens a closed file to VLookup some info and pull it to my spreadsheet then close that workbook. the problem I have run into is writing the Macro correctly so if I were to change the file name of the workbook I am running the macro with that it will still use the range declared for the workbook. I have colored the file path that I want to dim in Blue so that it will be easier to see what im trying to do. here is my current macro that works perfectly unless I save my current workbook under a different file name...
Sub Button7_Click()
Sheets("Sheet1").Unprotect "Password"
Workbooks.Open Filename:="\NDGI-SPARE-1UsersownerDocuments2014Vom ResultsVomResults.csv"
Dim c As Range
[Code] .....
View 8 Replies
View Related
Oct 20, 2009
I have a string variable contacting a file.
I want to set a workbook variable to this file, WITHOUT actually opening it!
ie:
DIM wb as Workbook
DIM filenm as String
filenm = "C: empdata.xls"
set workbook = filenm
============================================
So I know this won't work, but is there anyway around it? (bearing in mind I don't want to open the file!
View 9 Replies
View Related
Jul 27, 2006
I am opening a workbook using a macro. I need to run a macro in the workbook I am opening when it opens. I have created a variable containing the workbook name but don't know how to use it to run the macro.
Dim WBName
WBName = ActiveWorkbook.Name
Application.Run _
"'DIST_91094_EDTABS_DIABETES CARE_07072006.xls'!aStartProcess"
'DIST_91094_EDTABS_DIABETES CARE_07072006.xls'! needs to be replaced by the name stored in WBName
aStartProcess is the name of the macro in that workbook I want to run.
View 9 Replies
View Related
Dec 3, 2007
how to set up a global varable over a workbook? Incognito439
View 5 Replies
View Related
Jan 4, 2008
In conjuction with a previous request I've tried to create my own vba code (actually amend existing code i have) but its failing........ whats wrong??
Sub Retail_1_Xfer()
Dim NextRow As Long, Isht As Worksheet, Lsht As Worksheet
Application. ScreenUpdating = False
Set Isht = Worksheets("Retail Team 1")
Set Lsht = Worksheets("Raw Data")
NextRow = Lsht. Cells(Rows.Count, 2).End(xlUp).Row + 1 'next empty row in col A
Lsht.Range("A" & NextRow).Value = Isht.Range("A9").Value
Lsht.Range("B" & NextRow).Value = Isht.Range("A4").Value
Lsht.Range("C" & NextRow).Value = Isht.Range("B9").Value
Lsht.Range("D" & NextRow).Value = Isht.Range("N3").Value
Lsht.Range("E" & NextRow).Value = Isht.Range("N2").Value
Lsht.Range("F" & NextRow).Value = Isht.Range("BJ9").Value
End Sub
I think its got something to do with the fact that the worksheet " Retail Team 1 " is in another workbook
View 2 Replies
View Related
Feb 18, 2008
Ive just finished writing a macro that copies data from different parts of one workbook into a new workbook. However after doing all that my boss now informs me that the first workbook will be renamed regularly.
Is there a way adding a reference to a workbook that is not dependant on the name? Possibly declare it as a variable at the beginning or something? The indexing is not possible either as they might have multiple Spreadsheets open at any one time.
View 4 Replies
View Related
May 28, 2014
I just started venturing into VBA. I'm trying to pull the data from a different workbook that will have the previous month as the sheet's name. I can't get the dynamic part of the code.
[Code] .....
View 5 Replies
View Related
Jan 28, 2014
I need to reference a different workbook in a while loop. Each iteration, PopFive increases by 3, as I want to pull the numbers of every third row. How does the syntax of the line go after the " ! "?
[Code] ......
View 1 Replies
View Related
Nov 29, 2012
I have a challenge with Vlookups!
I have one workbook called Ratecards, which contains various tabs which are named based on a client code. e.g. ABC02
I then have another workbook which has employees in column B and the client code in C3.
The current Vlookup is =VLOOKUP(B8,'[ratecards.xlsx]ABC02'!$A$5:$N$168,4,false) which works fine for one client.
I want to substitute the ABC02 in the vlookup for cell ref C3, as the contents of C3 is variable and I want it to tell the sheet which ratecard to refer to.
View 3 Replies
View Related
Jan 27, 2008
about variable lifetime/scope in a workbook. Basically I have a Class Module that I set up in the Workbook_Open() event as follows:
Private Sub Workbook_Open()
Set CurrentClient = New CClient
End Sub
Once the workbook is open the user can select their "client" and all properties/methods for that "client" are available throughout the workbook until they close it, and it all works fine. However, whilst developing I'm obviously making lots of changes to the code, and every time I do the object is no longer available in memory.
I've tried manually running the Workbook_Open event after I make a code change, and even put in a button with the same code, but whatever I do the object won't remain in memory until I close & reopen the workbook. It's so frustrating having to close & reopen the whole workbook every time I want to test a code change I've made!
View 9 Replies
View Related
Mar 16, 2008
I need to create a formula or code which will return the value of cell d16 from an external and unopened workbook, where that external workbook's file name, directory and sheet names are provided within cells (able to be updated) of the current workbook. I have downloaded the add-in for INDIRECT.EXT but either being unfamiliar with this function or that I'm barking up the wrong tree all I'm getting is #REF!
And to add to my problem, I was hoping to use this formula / code within an excel table embedded in a word document.
View 9 Replies
View Related
Apr 11, 2008
I have a workbook with a field that contains the users name in cell B1. The first time the user opens the workbook, they put their name in this cell. Is there a way that that name can be saved to disk outside of the workbook in the same directory as the workbook is saved and a formula be put in workbook.open so that it will go get this name variable and insert it in B1 so the user doesn't have to enter their name each time.
I now have them putting their name in and saving the workbook which works until I send out an upgrade. Then they have to type in their name again.
View 9 Replies
View Related
Jul 11, 2007
This is the code after editing to make it more clear
Public Sub 1()
BookA= activeworkbook. name
BookB=Application.Workbooks.Add
Workbook(BookB).activate
End Sub
Public Sub 2()
BookB=Activeworkbook.name
With BookB. sheets(1)
.range("A1")=BookA.sheets(1).range("B1")
End Sub()
At the end of public sub 1, BookB is the active workbook. What I want to do in public sub 2 is to copy some data from BookA to BookB. Unfortunately, when moving from public sub 1 to public sub 2, BookA needs to be defined again. The code above is the code that I use in my add-ins. I figured out for non add-ins code I can define BookA with thisworkbook.name when BookB is active as I before work with BookA. This does not apply for add-ins as thisworkbook will refer to my add-ins code. Is there anyway of keeping definition of BookA is constant from one public sub to another public sub? This is simplified code. In fact, I can't merge public sub 2 with public sub 1 due to some reason which I don't say it here.
View 6 Replies
View Related
