Making / Defining Workbook As Variable In VBA Macro?

May 7, 2014

In my VBA macro, it copies data from this.workbook, and paste to another "destination" workbook. That destination workbook must be opened first before the macro is run.

Within the Macro code, I had to specify the exact name of the workbook which the data is pasted to.

The problem is that the exact name of that workbook which it paste to changes every week.

Is there any method where my macro can be changed and updated with the name of the destination workbook each week without manually opening and editing the code. The user of this.workbook is not technical enough to edit and maintain the macro each week

View 8 Replies


Defining A Variable As New Collection

Dec 7, 2008

I have a piece of code I am using to important data from a text file. The code works fine but I am having a hard time understanding how defining a Variable "As New Collection" works. I have tried to research on numerous websites dedicated to excel but had no luck finding any further information.

I have executed the code line-by-line over-and-over and believe what the "new collection" does is it creates some type of external database that is hidden within excel (that's just my guess). I have attached a sample file and a text file with dummy data if needed to see how the code is working.

View 4 Replies View Related

File / Workbook Created By Macro Is Making It Heavier?

Jul 4, 2014

I am creating 4 Sheets from a Main Sheet by Filtering Data based on different values in a fixed column, then the Filtered Rows are automatically pasted in the respective sheets.
Then these 4 Sheets are creating another file.

The same thing done manually by Grouping the 4 SHeets and then Move Copy into a New workbook also makes the file heavier.

Everything works fine however though the data is not much the file is very heavy, so what do i do to make it of appropriate size ?

View 1 Replies View Related

Defining Variable Data Row For Chart Series

Oct 5, 2006

I want the below code to run from R1C10 (i.e. cell J1) to the end of the data in the row, which will vary in position, rather than having to define where the end of the row will be beforehand (i.e. R1C21).

Is this possible?

View 9 Replies View Related

Defining Variable Range In Sumif Function With VBA Within Table

Jul 28, 2014

I sum number of articles that are coming in on one sheet and articles going out on another. The total of In - Out is a separate value. All three values are placed on a third sheet.

The following code I use does the job, but I have to use a set range in the SumIf function. I want to be able to use a variable for the lastrow of a table colum.

Private Sub ArttotKnop_Click()

Dim Klanttel As Integer
Dim Rij, vLastRow, iLastRow As Long
Dim varResult, varMin, varTotal As Variant

iLastRow = Sheets("Ingebracht").ListObjects("Ingebracht").ListRows.Count
[Code] ....

Since my table grows larger every day I want to use variables.

Where I wrote A2:A500, B2:B500, B2:B550 and D2:D550 I want to be able to replace A500 and B500 with A and B + the variable iLastRow. The same for B550 and D550 with the vLastRow variable

Tried using:
.Range("A2:A" & iLastRow)
.Range("A2", Cells(iLastRow, 1))
.Range(Cells(2, 1), Cells(iLastRow, 1))

They don't work.

View 2 Replies View Related

Defining Workbook Save Directory

Nov 5, 2008

I have managed to get my workbook to save with a filename based on two textboxes. I am not sure, however, how to define the directory. I have tried adding a ("mydirectory") on the saveas line but it does not work.

This is my
Private Sub CommandButton2_Click()
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim strText As String
strText = txtLMSRef.Text & "," & txtPartial.Text
wb.SaveAs Filename:=strText
Set wb = Nothing

End Sub

View 9 Replies View Related

Making A Constant And A Variable In 2007

Nov 18, 2008

assume we have constant P = 100, K=50 and F=10.

I would like to "lock" these cells, and have a variable which could adjust and this variable would multiplicate each one and put out the number.

For example:

P = 100
K = 50
F = 10

Variable: 10 this would generate a sum of:

P = 1000
K = 50
F = 10

View 3 Replies View Related

Making Variable Selection From Cells

Oct 13, 2011

I have a question about making a variable selection.

In cell X25 is written "B9" and in cell Y25 is written "M39".

Now i want to make a selection by a macro from cell B9 to cell M39.

View 1 Replies View Related

Making Backup Copy Of Active Workbook While Workbook Is Open

Jun 30, 2014

I run a model in Excel that automatically saves my file every xx iterations. After saving the file I want to make a backup of the file. Tried

[Code] .....

but get a permission denied error message.

I don't want to use .SaveAs as it is a huge file that takes a while to save and SaveAs has a tendency to break links that should not be broken..

View 13 Replies View Related

Making A Table Array In A VLOOKUP Variable

Jul 27, 2006

I have a spreadsheet where there are multiple records for each person. Each subsequent record contains information about one year prior to the one above it. So the first records contains info for 2005, the next 2004 and so on. I have the following formula in a cell in sheet 1: VLOOKUP($A2,'Sheet1 (2)'!$A$1:'Sheet1 (2)'!$DD$8796,11,FALSE)

I want to copy the VLOOKUP formula to the cell next to my original cell and have it begin searching on the first row after the first record and continue for each cell to the right. For example, if the lookup in my first cell (say A1) find the result on row 20, I want the lookup formula in B1 to start with row 21. So the change on the formula above would have to change the $A$1 above to $a$row below the first row found. I have a formula which tells me about the first instance of the vlookup finding the first row with the unique identifier.

View 4 Replies View Related

Making Workbook Opened From Other Workbook Active

Nov 20, 2006

I have a workbook that is a formatted report that I need to insert data from a name variable "CSV" file. I have created a macro in this workbook that calls an "Open" dialog for CSV files that I can use to browse to and open the source file. The macro then is supposed to "select all" copy and close the workbook then make the original workbook active and paste the data starting at row 2. The macro "seems" to be working perfectly except when I do the open the macro then makes the original workbook active, selects all copies then wants to close the original workbook. How can I tell the macro that the csv file that I just opened should be the active workbook, baring in mind that the name won't be known before the time it's opened so I can't hardcode the "active. workbook (NAME) "route.

View 9 Replies View Related

Open Workbook Macro With Variable Path

Sep 15, 2006

I am having trouble getting a macro to open a specific file from an open workbook in the same folder based on the current path of the open file. The application is this... I have two files in the same folder. The first is for data entry and it contains a macro button that will open the second which is a template to which the data in the first will be copied. I can easily do this with a fixed path to the second file, however, I would like to be able to move or copy the folder containing these files to other locations or computers without having to change the path each time. I would like to base the path to the second file minus the file name on the first (already open file) path. Assuming that the first file containing the desired macro button is already open, here is one of many of my attempts...

Workbooks.Open " ActiveWorkbook.PathJob Tracker -.xls"

Assuming the second file is the active workbook, I achieved a similar path assignment when saving the file...

ActiveWorkbook.SaveAs "Application.Path/Jobs Active/" & "Job Tracker -" & Range(" '[Job Tracker -.xls]Specifications'!$A$6").Value

View 2 Replies View Related

Defining Specific Stop Point For Macro

Mar 20, 2013

I need my macro to stop running 5 rows before an empty row. I have numerical data in cell BE, and a running summation of the numerical data in cell BI.

The last 4 rows of the group of data are subtotal/total rows, followed by an empty row separating this group of data from the next.

I would like the summation to start at the row of my active cell and continue to populate column BI with the running summation of column BE, but stop 5 rows before an empty row at the bottom of the group of data.

View 3 Replies View Related

Making A Workbook Readonly

Dec 4, 2008

I know that a workbook/spreadsheet can be made a readonly, but i find that this can be altered even if password protected.

I m looking for is a way of making it so when the user has finished entering in data and they save it, ready to send to anther user to view, that the whole sheet is readonly with no way of editing it ( like protecting the whole sheet).

View 5 Replies View Related

Making Sheets In Workbook Correspond Using VBA

Sep 23, 2011

A VBA problem has given me a workbook with two sheets. The first one lists the product names and their unit prices, and there is a third column labeled quantity purchased that is blank. The second sheet is an invoice, which will list the product name, the unit price, the number of units sold and the total price of the purchase. Assignment is to create a sub that will automatically fill out the invoice with any number I type into the blank column. Any good code to get two sheets to correspond in that way?

View 1 Replies View Related

Making A Macro Run From A Button In Different Tab.

Jan 5, 2010

I have an excel spreadsheet with two tabs.One called "menu" and one called "website"
I want to put a button in tab one (menu) and assign a macro. I need the macro to run/output in sheet 2 (website) not in sheet 1 where the button is placed.

No matter what I do I can't figure it out. The macro always runs in the same sheet as the button.

View 5 Replies View Related

Making A Macro Shorter & Dynamic

Sep 1, 2007

I have the following code as part of my macro. What I was wondering is a) Can anyone think of a way to shorten this? and b) I currently have this exact code copied 8 times, I did a find and replace to change 'if year = 2003 then' to the year 2010. What I would like is to have 1 code that will automatically change depending on the year the user inputs in a cell. (I should mention that 'Year' is a cell on my input sheet where the user types in the year they are currently working on and the macro automatically runs)

Sheets("Cash Crops").Select
If Year = 2003 Then
' This copies the cash crop BPU's.
Workbooks.Open Filename:="F:xdataCAISCAIS spreadsheetsBPU's.xls"
Application.Goto reference:="Cash03"
ActiveWindow.SmallScroll Down:=21
Application.Goto reference:="CashCrops"
ActiveWindow.SmallScroll Down:=-30
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True..............

View 9 Replies View Related

Making This Macro Non Case-sensitive

Dec 17, 2007

in making this macro non case-sensitive. So when a user searches a company name in the worksheet they do not require to type in the company name exactly as it appears.

For example; when searching Microsoft they can type "microsoft" and the macro would take the user to Microsoft.

I would also like to know if it is possible to add a feature that keeps the search dialogue open so the user can search the next possible match. If the user was to type in "mirco" and the search would show the user any company name with the word micro in it.

Sub Button3_Click()

Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

View 9 Replies View Related

Making Worksheet Active With Macro

May 12, 2006

Basically trying to make change the active worksheet using a macro code. I want the user to be able to click a button and it will automatically switch the worksheet to another within the workbook.

View 5 Replies View Related

2007 Macro: Run-time Error 91:Object Variable Or With Block Variable Not Set

Feb 20, 2009

I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro.
But it ends in a Run-time error 91...

The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile.
Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile.
The next target file in the folder is opened and the actions are repeated in this second target file.
For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook.
The error message i get is: "Run-time error 91:Object variable or with block variable not set."
When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.

View 9 Replies View Related

Macro Error Message (object Variable Or With Block Variable Not Set)

Jul 7, 2009

When i try to run the code below i get the error message - object variable or with block variable not set-


Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select
Selection.AutoFilter Field:=1, Criteria1:="1"
Dim sFormula1 As String
Dim sFormula2 As String
Dim sCell1 As String
Dim sCell2 As String
Dim sSheet1 As String
Dim sSheet2 As String
Dim r As Range
Dim MyRange As Range 'for testing

With Sheets("Points")
sFormula1 = .Range("CY1").Formula
sFormula2 = .Range("CY2").Formula
End With

strFormula = "=IF(ISNA(VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE)),0,VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE))"
r.FormulaR1C1 = strFormula
Dim ar As Range 'an area is a range
For Each ar In r.Areas 'areas are discrete, contiguous ranges of cells
ar.Value = ar.Value
Next ar

Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select
Selection.AutoFilter Field:=1

End Sub

View 9 Replies View Related

Find Dates Macro: Object Variable With Block Variable Not Set

Nov 21, 2006

I found this nice little bit of code for a date range search in column A but it will not work. Apparently i have not set a variable or something.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer
startDate = InputBox("Enter the Start Date: (dd/mm/yyyy)")
If startDate = "" Then End
stopDate = InputBox("Enter the Stop Date: (dd/mm/yyyy)")
If stopDate = "" Then End
startDate = Format(startDate, "dd/mm/yyyy")
stopDate = Format(stopDate, "dd/mm/yyyy")
startRow = Worksheets("sheet1").Columns("A").Find(startDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Worksheets("sheet1").Columns("A").Find(stopDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
Worksheets("Sheet1").Range("A" & startRow & ":A" & stopRow).Select

End Sub

View 9 Replies View Related

Object Variable Or With Block Variable Not Set Error With Macro

Mar 12, 2009

I attempted to modify "macro_1a1ay" to look into the "comments" sheet (column a) for a specific text string. If that text string is found, I have it delete the entire row, then re-sort the page and return to the calling page. It works well as long as it finds something. When it does not find the string (i.e. like now when the page is blank) it gives me the run-time error mentioned above.

View 7 Replies View Related

Macro - Making Extensive Action Items Log For Work

Apr 11, 2012

I am trying to make an extensive action items log for work.

On Sheet 1, rows 1-3 are blank. Row 4 contains column headers.

Column A: Item #
Column B: Description
Column C: Drawing #
Column D: Conforming
Column E: Nonconforming
Columns F, G, H don't really matter

What I am wanting is to be able to put an "x" or check mark in either column D or E (conforming or nonconforming). Then I want to run a macro with a push button (I know how to make these) that when pushed scans the spreadsheet row by row until it finds the "x" or check in Column E. I want it to copy every instance of this and send it to an Action Item Page that I have temporarily called Sheet 2. (This way I can go here to see all nonconforming issues). I do not want it to remove the information from Sheet 1.

Then, I want to be able to change the "x" or check on the Action Item Page to Column D (back to conforming) and the macro (push button preferably) copy and send that information back to the original spreadsheet in the SAME row it was.

So, in short I want this to be a fluid way of checking off a punchlist of items.

To complicate things even further, if possible, I would like to have several worksheets send information to the one Action Item List rather than just the one.

View 2 Replies View Related

How To Specify Workbook With Variable

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

Set Workbook Variable And Use It Within Another Sub?

May 19, 2014

I have this code which opens another workbook and defines the two open workbooks as MainWkbk and NextWkbk:

Private Sub wbo()
'open file selected from dropdown on Macros tab and define the two open workbooks
Dim singlepath As String


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


within the next sub but this is not right either.

View 1 Replies View Related

How To Get Workbook Name From Variable Sheet Name

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

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


The last two lines are the errors. I want to type in:


But the error occurs as it gets to the last two lines. By the way, all these two documents are open.

View 4 Replies View Related

Dim Current Workbook As Variable

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

Set A Workbook Variable As A String

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!

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

Copyrights 2005-15, All rights reserved