Passing Worksheetname
May 19, 2007
I have following code which identifies two worksheets, a source (wbSRC) and a destination (wbDEST). In the following code I am trying to get the values for wbsrc and wbdest to be written to the merged spreadhseet. But I am not sure how to go about this in VBA. The commented out approach does not work.
For Each rcell In wbList.Sheets(1). Range("A1"). CurrentRegion.Columns(1).Cells
Set wbDest = Workbooks.Open(rcell.Value)
Set wbSrc = Workbooks.Open(rcell(1, 2).Value)
wbSrc.Sheets(1).Range(copyAddress).Copy _ Destination:=wbDest.Sheets(1).Range(destAddress)
'wbDest.Sheets(1).Range("q3").Formula = "Workbooks.Open(rcell.Value)"
'wbDest.Sheets(1).Range("q4").Formula = "Workbooks.Open(rcell(1, 2).Value)"
View 4 Replies
ADVERTISEMENT
Jul 7, 2006
How do I pass values between procedures? I have two command buttons in the worksheet.
If I have a procedure A that asks user to input a value "x", when a command button is pressed.
Then another command button is pressed that passes the value onto another procedure B, which iterates a msgbox as many times as the number x,
How do I pass that value x from one procedure to another?
View 7 Replies
View Related
Apr 27, 2007
I am building a tool that uses Pivot Tables (so I can't use track changes) and I have found cool code that will insert timestamp and username in the comment when a change occurs. But I need to modify the code to also take the old value and put it into the comment as well. Here is the code to register the change into the comment:
View 11 Replies
View Related
Oct 12, 2007
I'd like to know how to send a different value to a cell based on what is selected in the combobox. Like a vlookup, but from within the box.
Something like :
View 13 Replies
View Related
Jul 19, 2009
Passing Array to VBA. I have the following
View 2 Replies
View Related
Dec 17, 2009
I think this is a relatively easy issue, but I don't know how to do it. I call the Sub SortRange in another Sub SortDeliver. A range (ran5) is defined in Sub SortRange that I also need to use in Sub SortDeliver. How do I pass the range or get the second sub to recognize the range?
View 5 Replies
View Related
Dec 28, 2009
I've a userform (UserForm1) that on closing will open one of 5 other userforms. Which of the userforms that opens will be determined at runtime, depending on an option a user has previously selected. The name of the userform is held in a cell in a workbook and is opened as follows:
VBA.UserForms.Add(sheets("Sheet1").Range("A1")).Show
where the value in Sheets("Sheet1").Range("A1") is "UserForm3"
UserForm1 contains TextBox1. How can I pass the value in TextBox1 to a textbox in UserForm3 when the name of UserForm3 is held as a variable in a cell in a worksheet?
View 9 Replies
View Related
Jun 16, 2006
Option Base 1
Dim Covariance(1 To 5, 1 To 5) As Double
Dim Mean(1 To 5) As Double
Dim Portfolio(1 To 10, 1 To 2, 1 To 5) As Double
Dim PortfolioValues(1 To 10, 1 To 2) As Double
And my main function:
Function Generate_Portfolio_Values()
Dim a As Integer, b As Integer, c As Integer
Dim m As Integer
Fill_Covariance_Array
Fill_Mean_Array
Fill_Portfolio_Array
View 5 Replies
View Related
Jul 25, 2012
I have the following code in 'ThisWorkbook':
VB:
Private Sub Workbook_Open()
Call Meetdata
End Sub
It calls this macro which is in a standard module:
VB:
Public firstvariablename As String
Public secondvariablename As String
Sub Meetdata()
regionname = InputBox("Enter the name of the Region.", "Region Name: North, South, East, West")
meetdate = InputBox("Enter the date of the Meet.", "Date of Meet")
End Sub
This set-up should make the variables available to all the other macros in the workbook. I have two other macros that need to use the values stored in these two variables that are entered into the two 'InputBox' statements. These two macros are in the same module and follow the 'Meetdata' sub. When I run the first macro, it recognizes the variables. When I run the second macro for some reason the variables are not recognized. To test the values returned by the variables, I placed the following code at the end of the first macro and again at the beginning of the second macro .
VB:
MsgBox(firstvariablename & " " & secondvariablename)
At the end of the first macro, the MsgBox returns the correct values stored in the variables which means that when the macro completes its run, the variables still hold their values. When I run the second macro with the MsgBox at the beginning, the Msgbox returns a blank. Somehow, the variables have been re-set to a null value. I can't figure out why the variables have been re-set to null. [URL]
View 2 Replies
View Related
Jan 26, 2009
I have created a few User Defined Types in a Main subroutine, but when I pass them into another sub the intelisense for the elements doesn't come up. I can type in the elements manually - and it all seems to work okay - but with a lot of types and elements I really want it to save me the effort.
View 2 Replies
View Related
Mar 20, 2009
How can I pass a column as a parameter when I execute the sub? ex:
View 2 Replies
View Related
Mar 26, 2009
I want to pass the name of the routine as a parameter.
View 6 Replies
View Related
Nov 3, 2009
I think I am confused as to how to declare a variable in one sub and use it as an input into another sub. I have attached some code below that assigns a value to two variables and then calls a sub that uses those values. I am brand new to programming so I guess I am confused how to implement this.
View 3 Replies
View Related
Nov 3, 2009
I'm having issue with passing along a variable. I learned this morning how to pass these along. But for this instance something isn't working correctly.
The code is below:
I cut out all the subs in between so you can see the problem.I think it has something to do with "File search"
The problem is after sub New_Pro_Test runs then returns back to Sub RunFolder I get subscript out of range error. It is suppose to open the next workbook in that folder.
View 7 Replies
View Related
Dec 21, 2009
I am trying to eliminate a lot of the global variables from my program by passing the variables to my functions and subs as arguments. I am stuck though when it comes to variables created in userforms.
The program starts with a series of userforms that asks the user for information that will be used throughout the rest of the program. Data is assigned to the variables on the click events. Is it possible, without using global variables, to pass those variables to the rest of the program?
Example:
View 3 Replies
View Related
Jan 30, 2010
I am using the following code to open a userform. Once the userform is open, the user has the option to choose a continue button or a cancel button. If the user is choosing continue, the code is not working (it always skips to the "else" portion of my "if" statement. I can't figure out why it is doing this.
View 4 Replies
View Related
Feb 20, 2010
My code passes a string created by a function to procedure. It all works great, but i need to add something so that if the string = false then sub doesn't run. What's the best way to do that. Here is some of the
View 6 Replies
View Related
Mar 11, 2013
Dim lngrow As Long.
i want the above variable to hold the value of endxlup . so for example :
lngrow = Cells(.Rows.Count, "A").End(xlUp)
Why doesnt it hold the # of the last cell with data in column A ?
View 2 Replies
View Related
Oct 30, 2013
I have some code I'm running:
Code:
Sub Main ()
Dim Value1 as integer
Value1=5
Call firstroutine
Call secondroutine
End Sub
[code].....
Somehow I need to pass Value1 & Value2 to secondroutine.
View 5 Replies
View Related
Feb 7, 2014
It seems to work. Are there any problems with it? Is there a better way?
Code:
Function myRangePassExample(RangeA As Range) As Double
Dim ArrayA As Variant, ArrayB As Variant
Dim iLo As Integer, iHi As Integer, i As Integer
Dim jLo As Integer, jHi As Integer, j As Integer
Dim Sum As Single
ArrayA = RangeA.Value
iLo = LBound(ArrayA, 1)
iHi = UBound(ArrayA, 1)
[code].....
View 9 Replies
View Related
May 6, 2014
I want to select a cells value from a Data Validation Box ( Achieved ) then based off of that selection ( value ) pass it into a slicercache, as follows :
Select a value from a drop down list in cell A1 Based on selection update slicercache by passing the value of A1 to it
So far i have : The bold is the part where i need to replace with whatever is selected from cell A1. Must be possible ...
With ActiveWorkbook.SlicerCaches("Slicer_Contract_15")
.SlicerItems("BRUTE").Selected = True
End With
Would be best if this code ran on cell A1 click ...
View 1 Replies
View Related
Dec 6, 2006
I'm trying to figure out how to pass parameters to a date field in a macro to control the display in a pivot table.
The macro currently uses a With statement to set the properties of 177 of the 180 dates to false, leaving the remaining three dates true and visible.
View 9 Replies
View Related
May 10, 2007
I have used my Excel Bible as well as MrExcel.com and tek-tips.com to write the current code I have... but it doesnt work! I think I am just missing a small piece, but I dont see what it is.
I have a macro that calls a userform - from the userform, a Customer is chosen. When the "extract" button is clicked on in the userform, it should pass the customer name to the next macro to do a bunch of stuff - including filtering the data by the customer chosen in the userform.
Here is the code - take a look and see if you can figure out why it doesnt work.
Code for "extract" button (I have tried several differnt variations - this is the current one):
Code: ....
View 9 Replies
View Related
Jul 20, 2007
I am familiar with the normal method of evaluating a variable and passing it to another macro using Call MacroX (var1, var2).
In this instance, I call macro 1 and within macro 1, I call macro 2. Macro 2 establishes some variables, finishes at Exit Sub and hands control back to macro 1.
I want these variable returned to macro 1. How do I do that?
View 9 Replies
View Related
Aug 1, 2007
problems with the syntax involved in calling one subroutine from within another. I want to pass two variables calculated in a 'main' sub into the parentheses of another I am calling (see pseudocode below). When I pass one variable, everything works fine, however, when I try to include another, I get an error message - something about expecting an "=" in the syntax, which is clearly not correct.
Sub main()
row = .....
column = .....
counting(row, column)
End sub()
Sub counting (row, column)
.....
End sub
View 9 Replies
View Related
Dec 23, 2007
My code defines a variable: newrow. Then I call a subroutine using the Call command.
The subroutine does not recognize newrow.
It says it has value zero. How can I pass the value of newrow on to the subroutine.
View 9 Replies
View Related
Jan 28, 2009
I have an excel sheet, in that excel sheet is a command button, once clicked the command button creates a Word document.
Im trying to get my VBA script to bold certain text that will be in the word document. But all my attempts to bold certain cell contents have failed.
Im not getting any errors from the editor, its just not bolding certain text Ive told it too when the word document gets created.
I have included my script below (And Ive tried the following methods already)
1. ActiveCell.Font.Bold & "This Is Function One, This Text Will Appear Bold" & wks.Cells(ActiveCell.Row, 2) & _
2. "This is Function Two, This Text Will Appear Bold" & wks.Cells(ActiveCell.Row, 2).Font.Bold
3. "This is Function Three, This Text Will Appear Bold" & wks.Cells(ActiveCell.Row, 2) & (ActiveCell.Font.Bold) & _
Sub TransferData()
'This macro transfers the data range to a Word Document
'
'Constants:
'docFullName = The full name of an already existing Word document
'
View 9 Replies
View Related
Feb 17, 2009
I'm trying to pass an array of UDT from Excel to a C++ dll but I have no idea how. My UDT looks like this
Private Type Student
Age As Integer
Name As String
StudentID As String
End Type
And my dll function declaration..
Declare Function RegisterStudent Lib "SimpleDLL.dll" _
(arr() as Student) As String
.. which will return a unique Id in string
View 9 Replies
View Related
May 28, 2009
I have an Excel workbook with a command button on it. When this button is clicked I wish to pass the filename of the Excel workbook to my Visual Basic program, I have so far got the following
Public Declare Function ShellExecute Lib "Shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
View 9 Replies
View Related
Sep 23, 2009
I'm trying to set up a journal-type workbook. I have two sheets named Start and End-the first sheet has a cell that contains a formula, =SUM(Start:End!B4) B4 is where you put the number of hours worked in a day. I then run a macro that copies the sheet called Start for each day of the year. What I was expecting was that the cell with the formula would provide a running total of the hours worked through the year on each sheet, but when the sheets are copied the formula changes to =SUM(#REF!B4). My macro to create a copy sheet for the whole year is as follows:
Sub MakeYear()
Dim SH As Worksheet
Dim D As Date, Y As Long
Set SH = ActiveSheet
Y = Val(InputBox("Year:"))
If Y < 2000 Then Exit Sub
If Y > 2100 Then Exit Sub
Application.ScreenUpdating = False
For D = DateSerial(Y, 1, 1) To DateSerial(Y, 12, 31)
Application.StatusBar = D
SH.Copy after:=Sheets("Start")
ActiveSheet.Range("B2").Value = D
ActiveSheet.Name = Format(D, "mmm dd")
Next
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
View 9 Replies
View Related
