Linking VB To Another Notebook
Jun 27, 2007I am working on a project and if i hit button one in my VB program i need it to pull up a program under c:programfiles/user/name.xls
ADVERTISEMENT
Macro Works On Notebook Laptop But Not On Desk Top PC
Jun 6, 2006I have this code working on my notebook:
Dim lastrow As Integer
Dim irow As Integer
lastrow = Workbooks("tmp").Worksheets("vRptMOMarkToHedgeFacilities"). Range("C65356").End(xlUp).Row
For irow = 2 To lastrow
Workbooks("tmp"). Sheets("vRptMOMarkToHedgeFacilities").Range("C" & irow).Copy
Windows("vlookupsheet.05.01.06.xls").Activate
Sheets("new stuff").Activate
Range("D" & irow + 1).Select
ActiveSheet.Paste
Next irow
Range("D1").Select
but it does not work on my desktop. For some reason when I go and execute the macro it gives me a "Run-time error '9' - Subscript out of range"
Linking To Other Workbooks
Jun 20, 2014It is quite standard to create links between workbooks, and generally I do this by inserting an "=" sign in the cell I want data to appear in for Workbook 1, I then open workbook 2 where the desired data is and click on the cell housing the data I want.
I just went to do this, and upon putting the = sign in the cell I want in workbook 1 and clicking in workbook 2 nothing happens except my cursor is now in workbook 2 and the = sign just remains alone in the workbook 1 cell.
If, however, I put an = sign in a cell in workbook on and then click another cell in that sheet or a cell in another worksheet but in the same workbook a proper link is created, so the problem seems to be isolated to links to other workbooks.
Validation And Linking
May 27, 2008I've attached a basic excel file with an example of the problem I need to solve. As you can see, if you select "No" to "Do they have a pet?" then conditional formatting blocks out the next field. The problem with that is, it still allows you to enter a value into the "Type" field which is skewing my results. Is there a way to allow a value in "Type" ONLY if there is "Yes" in "Do they have a pet?". I would also like it to delete the value in "Type" if I later select "No" instead of just covering it up. Is this possible? I'd like to avoid the scripting route if at all possible but I'm not sure it is...
Also, I have two linked spreadsheets, one pulls the totals off the other. However, If I add rows into the original it does not update the total formulas in the second sheet. Is there a way to make it do this automatically?
Linking Two Spreadsheets
Aug 5, 2008I am working with excel2003. I have to different workbooks in which on is a Bill of lading that is used to show the items being shipped. The other workbook has a list of the items that we ship.
On the BOL I want to be able to type in the "L" number (or I.D. number) in D14, D19, D24, D29. By typing in the L-number I want it to automatically bring up the product name into cells E14, E19, E24 and E29. With the exact name of the item it will help prevent employee error. I want it to also bring over the exact weight over from the flavor list in cell range C5:C28.
I have the FlavorCodes range with all the L-numbers on the BOL in cell range U9:U48. I thought I had it set up correctly but when I select the L-number in the drop down list in cells E14, E19, E24 and E29 I get error code #NA. I thought I defined the cell range but it is still not working.
The workbooks are attached below.
Linking Worksheets ...
Dec 1, 2008I have a problem and wanted to know if there was an easier way to create links to various worksheets. I have a folder with 59 worksheets. The names of the Worksheets are the weeks of the year(example LA-01-01-09) I have another workbook with 13 worksheets one for every month and a sheet that totals the whole year up.
What I want to know is how can I create a link to everyone of those weekly sheets to go to my monthly sheets,so when data is entered into the weekly sheet the monthly sheet will update automatically? The weekly sheets are setup exactly the same. I'm looking only to pull row 3 columns c:d:f:g:h:i over to my monthly sheet.
I have an example of my weekly sheet and monthly sheet.
Linking A SUB To Another Module
Jun 9, 2009I have a Form that when the 'SUBMIT' button is clicked, I would like to validate the information and then write it to a 'MASTER SHEET'. However, the validation has gotten lengthy and I have opted to put the validation code in a separate Module to keep my general code a little cleaner.
PROBLEM:
The original code would kick you back to the form if something was wrong via a 'END SUB', but as now the SUB is on a different Module, it isn't working.
Here is the pertinent code on the general code sheet: ....
Linking Worksheets
Oct 13, 2005I want to pull data from about 100 different Excel files. They are located in
the same folder and drive and the data I want is in consistent cell
locations. I would to find a fast way to reference the files. something like
S:FolderJanuaryCaseNumber1.xls that will allow me to pull-down the
folumula and copy the month and case number from two separate fields on my
spreadsheet. So I would specify the month and case numbers and have them
automatically populate my link formula.
Linking Two Formulas
Mar 20, 2009in each cell I have formatted a Workday formula which is for example:
=WORKDAY(D16,3)
Now I want to add to that function
"If D16 is blank return blank"
What I am getting at the moment is that the if D16 is blank I am getting #VALUE!
THis makes the sheet very hard to read.
Linking Buttons With A Row?
Nov 23, 2011If possible I would like to create using vba a button at the end of my rows of data allowing a user to copy and insert the row next to the button, i.e. duplicate.
I am using the following code to create the button but am not clear how to link a button to a row or cell. The button would be within a cell in a row at the end of the data.
Range("a2").Offset(rowoffset, 10).Select
ActiveSheet.Buttons.Add ActiveCell.Left, ActiveCell.Top, ActiveCell.Width, ActiveCell.Height
rowoffset increments within a loop and a button is placed at the end of each row of data.
I would also like to know how to name the button.
Linking Workbooks ...
Apr 4, 2007I run a number of spreadsheets each 4 week period and these are stored in a folders named after the period. ie Period 10 will contain workbookA10wk1, workbookA10wk2, workbookA10wk3 and workbookA10wk4,
Period 11 will contain the same workbooks but named workbookA11wk1 etc.
The data from the workbooks is then used in graphs / tables for each year.
I always pick up the same cell ref in each workbook to compare each week and my table is set out with heading of period and wk.
Is it possible to use the table headings to produce the names of the workbooks that you wish to reference, ie: ='Period 13[workbookA13wk1.xls]Sheet1!$D$17 to appear under Period 13, wk1
About Linking Cells?
Sep 9, 2007how do I go about linking cells?
Example:
Lets say I have a workbook with 6 sheets on it and the sheets names are:
Sheet1
Sheet2
Sheet3
Sheet4
Sheet5
Status
And we will just use cell A1 through sheet1 to sheet5 and the status sheet to grab the numbers from sheet 1 through sheet5
Sheet1 through sheet5 will have numbers in cells A1
Sheet1 : 586
Sheet2 : 436
Sheet3 : 621
Sheet4 : 610
Sheet5: 561
And in the status sheet I would like to find the highest number from these 5 sheets and put it in cell A1 status sheet and have it linkable like when it finds the highest number it will put it in cell A1 on the status sheet and when I click on the A1 cell in the status sheet I would like it to goto that sheet that has the highest number it found and maybe highlight it or something.
Linking To Another Tab Causes To Crash
Nov 16, 2009I have a multi-tabbed spreadsheet that keeps crashing when I try to set up a reference from one tab to another tab in the same spreadsheet (e.g., =Sheet2!B1) . The spreadsheet does NOT have any macros in it although my Personal.xls DOES have numerous macros that I have been using for over 6 years with no problems. In any ase, when I start Excel without loading personal.xls, so that NO macros are loaded, the spreadsheet still causes Excel to crash when I try to set up the reference to another tab.
View 9 Replies View RelatedLinking To Other Spreadsheets
Jan 4, 2010Is it possible for me to link to other spreasheet data, and have the links update (retrieve the data) without those files being opened?
View 9 Replies View RelatedLinking Another Workbook
Sep 18, 2006I have a workbook Book1 that has 4 sheets, one of those sheets needs to be an exact replica of a sheet from another workbook Book2. I tried to directly link the data and use Vlookup's but the problem is that when someone opens Book2 and inserts more data (Inserting rows) it does not show up on Book1.
View 3 Replies View RelatedLinking Spreadsheets
Jan 14, 2007I have sucessfully created a spreadsheet which links to another using a vlookup and an array. I now need to reverse the process. My requirements are this:
I have individual spreadsheets which represent individual projects/quotes for our customers. I'd like each of our sales staff to have a summary spreadsheet which summarises all their ongoing projects in one spreadsheet, dynamically.
when a new project is created on a new (projects/quote) spreadsheet, it looks to the summary spreadsheet and looks to see if that project exists in the summary, if not, it creates a new line and writes the summary details to it. then as the project spreadsheet evolves, it updates its details on the summary sheet dynamically.
Linking The Worksheets
Mar 1, 2007I compile a report each month. I have a program/macro built to do this so based on the month I select it runs the code below. The code below would run if i selected January. I run this report on a spreadhseet and its for a ton of people, and my code is really long. I know there is someway to shorten it, but i cant figure out how. Below is an example of one persons, and the next persons would begin on the row below the example.
Sub January_NB()
'Week1
'Christie
Sheets("New Business").Activate
With Sheets("New Business")
Range("B8").Select
ActiveCell.FormulaR1C1 = _
"='\lm-file-dfs-01centralPermktUnd-Prod-CtrsProperty Count SheetsNew Work ReportNB Team Count sheetsChristie[Count Sheet January Christie.xls]Tally'!R73C4"
Range("D8").Select
ActiveCell.FormulaR1C1 = _
"='\lm-file-dfs-01centralPermktUnd-Prod-CtrsProperty Count SheetsNew Work ReportNB Team Count sheetsChristie[Count Sheet January Christie.xls]Tally'!R6C4"
Range("E8").Select...........................
Linking A Table
Apr 15, 2007i have created a worksheet in excel that is used to moniter employees. i want to export the data in to an access database when a button is clicked so that i am able to track the employees over a period of time. how can i do this using vba?
View 5 Replies View RelatedLinking Dropdown Box Within Formula
Dec 5, 2013I'm trying to work out how to allow a formula to be changed by pointing part of the code at a drop down list containing worksheet tab names from another workbook.
View 2 Replies View RelatedLinking Grid To Combobox?
Mar 6, 2014I'm trying to link objects and pricing to groups via a combo box. If the user selects Group A from the combo box and enters Object A on the line item, I want the price associated with Object A and Group A to populate.
Group A
Group B
Group C
Group D
Object A
$1
$3
$5
$7
Object B
$2
$4
$6
$8
Object C
$3
$5
$7
$9
Object D
$4
$6
$8
$10
Linking To Worksheets According To Date
Mar 10, 2014I create the billing data for some key accounts and I was asked to build a summary sheet that brought all of them into one place so that the billing department didn't have to open each individual spreadsheet each month to verify their info to mine. What I would like to accomplish is to stay current according to today's date.
So if it's March, 10, 2014, I need to update the spreadsheet data according to the February 2014 spreadsheet a Key Account #1. Then in April, I need it to look for the new March 2014 spreadsheet. I'm assuming I can use an =IF(OR(... formula for this, but I'm just not sure how to get the date thing sorted out.
As far as naming of the the Key Accounts billing spreadsheets, they go something like 0214 "KeyAcct #1".xlsx. Then the new one will be 0314 "KeyAcct #1".xlsx. Even tho the next months billing file isn't created, the formula should be able to check against the date and know when to look for the new one.
Linking Data Between 2 Workbooks
Jul 16, 2014You have two Workbooks open. Workbook 1 and Workbook 2.
You COPY all the data from Workbook 1 and PASTE the data as a 'Link' into Workbook 2.
You then save and close both Workbooks.
You then open Workbook 1 and edit the data. You save and close Workbook 1.
QUESTION
Will the linked data in Workbook 2 update automatically (i.e., without opening the file)?
Linking Columns In Excel Together
Jul 18, 2014I have a list of countries in excel and a list of partner countries, the countries all have individual country codes.
Example:
country partner country exporter country code importer country code year value
Australia..........Belgium........................AUS.........................BEL..1999..1000
Canada............Belgium........................CAN.........................BEL..1999..2000
This for a long list of countries, I have filled in the country codes for the first year manually but the list goes on to 2008 and is a lot of work to do manually, (thousands of cells). is there a way to complete the list of exporter and importer codes linked to the country and partner country column and then complete the whole list?
Linking Data From One Worksheet To The Other?
Jul 28, 2014I have a database sheet with a ticket list which has various columns like business area, ticket number, location, status etc. I am creating a new sheet where i want to highlight only the tickets that are in pending status and I dont want all the columns from the main sheet but just a few. How can I link it so its automatic.
View 4 Replies View RelatedLinking A Number Code To A Name
Apr 18, 2007For example, Column D, Rows 2 - 500 contain different number codes:451
461
593
675
I want to match up the number code and replace it with the Course Name.451 = Course A
461 = Course B
593=Course C
675=Course D
I am looking to have the number code, i.e., 451 replaced in Column D with the Course Name, Course A or the Course Name appear in Column E pulling from the number code.
In the end I will probably have 100 unique number codes to match up with course names.
Linking Adjacent Cells To Each Other
Jun 26, 2008I need to have three rows link to each other. The first column cell A is a paste link and is always changing. The second B is a vlookup referenceing A and finding it in a changing table...the source for the paste link. C needs to follow the other two cells and remain "attached" to it. However, C has to be a cell that is hardcoded somewhere, meaning that C is not found in the source data and must be determined by a person. I have tried to link the sheets through vlookups and other formulas, however, this only works when the data is sorted and nothing new is added. I need a static sheet that can take in new values. I need to create some sort of relationship between the cells
View 14 Replies View RelatedLinking Buttons And Datasheets
Dec 30, 2008I'm interested if it is at all possible to configure say, one sheet that is a rough visual layout of an inventory shelf, and if an object on said shelf was clicked, then relevant text would be displayed in the respective fields.
So if I had a picture of a shelf full of groceries on sheet1, and say clicked on a can of beans, then beside my picture of the shelf full of groceries, it will copy over the information I keep relevant to my beans.
Linking To Last Record In A Spreadsheet
Dec 31, 2008I am formatting a workbook so that the first table is a flat database of information used in other tables in the workbook. Each row in the tables would correspond to the values for a specific year thus,
year|weight|length
1999|24|123
2000|27|134
2001|21|121
2002|25|132
2003|19|112
2004|31|135
2005|22|126
2006|27|145
2007|28|147
I would like to have other tables reference this table and automatically update for the last record in the table so that the second table in the workbook would show,
year|weight|length
2007|28|147
My goal is to be able to go into the first table flat database tomorrow (Jan 1, 2009) and insert,
2008|29|110
in row below the 2007 data and then have the other tables in the workbook automatically update to reflect this new data and show
year|weight|length
2008|29|110
Linking Lists Across Workbooks
Jan 20, 2009I am trying to create a master price list, where the 1st file called MasterPriceList will list all of our ingredients and their prices.
It would look like this:
A_________B
ING______Price
1 Ing 1___$1.00
2 Ing 2___$1.50
3 Ing 3___$2.00
4 Ing 4___$3.00
My second workbook is is a template for when we need to formulate blends.
It looks something like this:
A______B______C_______D__
1 Ing 1__50%__$1.00__$0.50
2 Ing 3__50%__$2.00__$1.00
We want to pull the cost of the ingredient from the MasterPriceList and populate column C with that value.
I have tried copying and pasting a link. This works fine until I insert a row. Even If I make the link relative (='[MasterPriceList.xls]Sheet1'!$C1), it will only adjust if both worksheets are open at the same time. Since we will have over 200 pricing sheets, it would be impractical to have them all open every time we have to insert a new ingredient into the MasterPriceList workbook.
I have tried to use VLOOKUP but it will not work across workbooks and the same is true with Drop Down lists. I read a tutorial, http://office.microsoft.com/en-us/ex...995141033.aspx , which details how to create a drop down list across workbooks but when I come to the step of defining the validation list, I get a message saying that it can't be done across workbooks.
Linking And Finding Information
Apr 2, 2009I have a spreadsheet with roughly 20 or so tabs. Each tab contains about 20-50 components which together form a product, so each tab is really a detailed list of what the specific product contains.
One component occupies one line, just a few cells, where you can find basic information like "name" and "price" and other things, but name and price are what's interesting in my case.
What I have done is taken each unique component and placed it on a first tab, creating a list of every unique component with full information on it. I then wish to link that information to the correct component in each tab, so that when you change somethin, for instance purchase price, on the first tab it changes for all the components of that type through all 20 product tabs. Now I know how to do this manually, but it's a lot of work to do that with every component in the document.
so my question is, is there a formula or way for me to make it easier? For instance, perhaps let excel search for the same component name in the database sheet and link the price automatically to that component in the product tabs? I don't know, I just figured there must be some easier way to link the information then me hopping back and forth between tabs 'till my hair turns grey.