Cell Ref - Link Question

Good Afternoon,

I have a macro that is writing from one workbook to another.  After it has 
finished opening and writing it's initial data, it calls this macro and 
writes a special cell value to the front page. Though this works fine, I want 
it to be a cell reference (linked value) vs. static.

Ive tried a couple ways unsuccessfully and was wondering if you might have 
some ideas on how to accomplish this.

Thank you - Roger

Sub WritetoMainPage()

Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

irow = ws.Cells(1, 12) _
  .End(xlDown).Offset(1, 0).Row

ws.Cells(irow, 12) = ActiveSheet.Range("h5").Value <<<This works but I want 
a cell ref (link) vs. static value>>>

Worksheets("sheet1").Select

End sub
0
Utf
1/24/2008 8:10:04 PM
access 16762 articles. 3 followers. Follow

3 Replies
569 Views

Similar Articles

[PageSpeed] 44

Inasmuch as this is discussion group is devoted to MS Access, the datbase 
application which is part of MS Office along with MS Excel, you'd probably 
have more success getting a useful response by posting this question in an 
Excel discussion group.

George


"Roger" <Roger@discussions.microsoft.com> wrote in message 
news:0C554CA5-479C-4A83-A34E-C53999474400@microsoft.com...
>
> Good Afternoon,
>
> I have a macro that is writing from one workbook to another.  After it has
> finished opening and writing it's initial data, it calls this macro and
> writes a special cell value to the front page. Though this works fine, I 
> want
> it to be a cell reference (linked value) vs. static.
>
> Ive tried a couple ways unsuccessfully and was wondering if you might have
> some ideas on how to accomplish this.
>
> Thank you - Roger
>
> Sub WritetoMainPage()
>
> Dim irow As Long
> Dim ws As Worksheet
> Set ws = Worksheets("Sheet1")
>
> irow = ws.Cells(1, 12) _
>  .End(xlDown).Offset(1, 0).Row
>
> ws.Cells(irow, 12) = ActiveSheet.Range("h5").Value <<<This works but I 
> want
> a cell ref (link) vs. static value>>>
>
> Worksheets("sheet1").Select
>
> End sub 


0
George
1/24/2008 8:41:21 PM
Sorry, this newsgroup is for questions about Access, the database product 
that's part of Office Professional.

You'd be best off reposting your question to a newsgroup related to Excel.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Roger" <Roger@discussions.microsoft.com> wrote in message 
news:0C554CA5-479C-4A83-A34E-C53999474400@microsoft.com...
>
> Good Afternoon,
>
> I have a macro that is writing from one workbook to another.  After it has
> finished opening and writing it's initial data, it calls this macro and
> writes a special cell value to the front page. Though this works fine, I 
> want
> it to be a cell reference (linked value) vs. static.
>
> Ive tried a couple ways unsuccessfully and was wondering if you might have
> some ideas on how to accomplish this.
>
> Thank you - Roger
>
> Sub WritetoMainPage()
>
> Dim irow As Long
> Dim ws As Worksheet
> Set ws = Worksheets("Sheet1")
>
> irow = ws.Cells(1, 12) _
>  .End(xlDown).Offset(1, 0).Row
>
> ws.Cells(irow, 12) = ActiveSheet.Range("h5").Value <<<This works but I 
> want
> a cell ref (link) vs. static value>>>
>
> Worksheets("sheet1").Select
>
> End sub 


0
Douglas
1/24/2008 8:52:02 PM
I believe that you are in the wrong discussion group. This one is for 
Microsoft Access database problems and not Excel.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Roger" wrote:

> Good Afternoon,
> 
> I have a macro that is writing from one workbook to another.  After it has 
> finished opening and writing it's initial data, it calls this macro and 
> writes a special cell value to the front page. Though this works fine, I want 
> it to be a cell reference (linked value) vs. static.
> 
> Ive tried a couple ways unsuccessfully and was wondering if you might have 
> some ideas on how to accomplish this.
> 
> Thank you - Roger
> 
> Sub WritetoMainPage()
> 
> Dim irow As Long
> Dim ws As Worksheet
> Set ws = Worksheets("Sheet1")
> 
> irow = ws.Cells(1, 12) _
>   .End(xlDown).Offset(1, 0).Row
> 
> ws.Cells(irow, 12) = ActiveSheet.Range("h5").Value <<<This works but I want 
> a cell ref (link) vs. static value>>>
> 
> Worksheets("sheet1").Select
> 
> End sub
0
Utf
1/24/2008 9:03:27 PM
Reply:

Similar Artilces:

Short Crystal Reports Question
Hi all, How do I display a running total in a subreport in the parent report in Crystal Reports for CRM 1.2? Thanks all Tom You need to do code to do this. You can declare what are in effect global variables in the main report and track the running total in those variables. Becuase they are global the sub report should be able to access them as well. you may also be able to pass the contents of the running total variable directly into the sub report as a parameter as well This book may help you get started. Its the only book I have used for crystal in crm http://www.amazon.com/gp/p...

Getting a filename into VBA and putting it into cells
Basically what I have is a button that creates a new column that should be full of linked values. When the user hits the button, he is asked to select a file from his hard-drive; the code then takes the name of that file and creates a number of links in the new column. So for example: 1) User hits button and selects the closed excel file C:\Tempfiles\Testsheet.xls 2) Excel creates the new column and populates it with formulas that link to the file; in A1 it enters ='C:\Tempfiles\[Testsheet.xls]Overview'!$A$1 in A2 it enters ='C:\Tempfiles\[Testsheet.xls]Budget'...

Popup Form question
How can I set a pop-up form to only take one record? I have a popup form (single form) to take some customer info however when if they tab past the last field all the data disappears. Anyway to prevent this? Thanks in advance D "Dave" <dave@accessdatapros.com> wrote in news:95EBA52D-5DC8-4333-A18F-9A7EA4B65D77@microsoft.com: > How can I set a pop-up form to only take one record? > I have a popup form (single form) to take some customer info > however when if they tab past the last field all the data > disappears. Anyway to prevent this? > > Thanks in ...

Auto moving cells
We use Excel 2002 XP at work, and im trying to find out if its possible to get excel to auto move a cell to the next column. The spread sheet is setout with 5 columns and is about 20 rows down. Each cell has a number in it (which is the number of each till in the store). This gets change quite a few times each week and as they are all in alphabetical order, if a number needs adding or removed, we have to keep doing it buy insert shit cells down etc, then the problem is since its only 20 rows down 1 cell will move on to row 21. Would it be possible to make excel auto maticall move this cell...

cell range not changing when refreshing linked data- sumproduct fo
The cell range is not updating correctly to reflect the last row in the data sheet that is being refreshed. 5878 is the correct number of rows and 5824 is not. I can find and replace in my formulas to correct the problem, but it does it each time the data is refreshed. Any suggestions as to why? =(SUMPRODUCT((Expense!$B$2:$B$5878=$C$2)*(Expense!$C$2:$C$5878=$C$3)*(Expense!$W$2:$W$5824=$A6)*(Expense!$M$2:$M$5878))) I gave you the INDIRECT option y'day, but received no feedback from you in that thread. Looks like you're more interested in knowing why? One simple hunc...

Randbetween question
Is it possible to generate numbers that have decimal numbers? When I use RANDBETWEEN function I only seem to get integers. It sure would be nice to gereate random numbers that are between specific numbers that have decimals. Why not simply use RAND? To get numbers between 50 and 80, for example, you can use =30*RAND()+50 where the 30 is the difference and the 50 is the lowest #. Bob Umlas, Excel MVP "Phil" <anonymous@discussions.microsoft.com> wrote in message news:074f01c424fc$17225120$a301280a@phx.gbl... > Is it possible to generate numbers that have decimal >...

Link Error
Hi! All, I have added the "afxcmn.h" to get the "CImageList" type to do some graphical interface enhancements. The Programs compile fine, but I run itnot Problems during Linking.. I know this is not the Forum, but if something is obvious , please do point it to me. this is how I added it to my 'StdAfx.h' #ifndef _AFX_NO_AFXCMN_SUPPORT #include "afxcmn.h" #include "afxcview.h" #endif #include <windows.h> #include <stdio.h> #include <atlbase.h> #include <commctrl.h> #include <richedit.h> #include <t...

website links in IE8
Links stopped working on websites. They worked yesterday, but not today, Although those links will work on a different computer. Hi, Tools>Internet Options - Security tab, click "Reset all zones to default" PS. Its a good idea to leave your IE Security zone settings at their 'default'/'factory' settings instead of changing them in the hope of getting some web features working on individual sites. Adjust security for web sites by either adding them to the Restricted or the Trusted Sites Zone. Regards. "fjs69xr7" <fjs69xr7@discussio...

2 questions from a beginner
Hi i have 2 questions on how to do things if anyone could help it woul be great. I need to create a text box and a button where when you ente a number in the text and click the button it mutiplies the number, th question actually looks like this... a) Define a variable Mult in you code b) When the user clicks the button if the textbox has an empty string set the variable Mult to 10. If it is not empty, set the variable Mul to the number they typed into the textbox. c) Use a for..next loop to fill in the values in column A and B a shown in the sample. Hint use the cell property. and the othe...

Data vs. forms & Unload/reload questions
A .mdb file seems to encompass both the database and all the 'code' (forms, modules, and logic) used to CRUD the data in the tables. 1) Is there any way to separate the database from the code into separate files, eg. the db is mydata.mdb and the 'code is in a separate file, eg. code.mdb? 2) Say you have v1.0 of an app and the tables and attributes are populated with data. You then go and build v2.0 of the app, adding tables and columns to what already existed in v1.0, and perhaps moving some of the v1.0 attributes into different tables. What is the recommended way to unload/reloa...

Link Errors __endthreadex and __beginthreadex
I've started to teach myself Windows Programing with Visual C++ 6.0 with SP6. I'm using "MFC Programing from the GROUND UP" second ed. Copy his examples into a new proj. with new workspace set for an empty win32 application. They compile with no errors but I have Link Errors on "unresolved external symbol __endthreadex" and unresolved external symbol __endthreadex" and __beginthreadex. What am I missing? Thanks for any help forthcoming. you forget to enable support of MFC dll in your project, Enable it from following location Project|setting or pres...

Linking text to style for auto update
I want to auto update figure/table number in text to that in Figure/Table style numbers Use Insert > Cross Reference to put the numbers into the text; they don't update every time the numbering changes, but only when you tell them to (Ctrl-A, F9) or when you print or switch to Print Preview. On Dec 18, 4:05=A0pm, Taurus <Tau...@discussions.microsoft.com> wrote: > I want to auto update figure/table number in text to that in Figure/Table > style numbers ...

Cell Reference to Page Header
Is is possible to reference a cell in the page header? I am hoping to have a sheet where demographic information can be inputted. Name, Address, etc. Then have that demographic information show in the header of every sheet printed. Hi only possible with VBA, not with a cell reference. -- Regards Frank Kabel Frankfurt, Germany Allen wrote: > Is is possible to reference a cell in the page header? I > am hoping to have a sheet where demographic information > can be inputted. Name, Address, etc. Then have that > demographic information show in the header of every sheet &...

Manage online services question
Hi Q1. How do I remove an account from under " Financial institutions not setup for background online services" heading? It is an old account which I do not use anymore. Q2. Under "Financial institutions that have no background services", there are a list of Financial Institutions. How do I amend the Financial Institutions in this list? The reason I ask is that one Institution has 'File Import' marked as a Service. But I don't understand why I have another Institution NOT marked as 'File Import', even though I do import a file into Money, ...

Can't link a graphic?
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Email Client: pop I've been told on this forums several times that one cannot have a graphic with live link in your email signature. <br> Still, I've recently received a few email with small buttons that link to facebook and myspace in Entourage. How are these folks achieving this? thanks. kevs12@officeformac.com wrote: > Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power > PC Email Client: pop I've been told on this forums several times that > one cannot ha...

Another question, regard enum { IDD = DIALOGID };
Hope it's okay if I ask another question, I noticed that my dialog lacked an IDD-enum. When you create a new dialog project, say, you get something like the following in the public section of the dialog class header: enum { IDD = IDD_ASIMPLETEST_DIALOG }; I noticed I didn't have one in this old project so I added one but it won't compile because the compiler doesn't see the identifier I'm using, which is due to the fact that Resource.h isn't included in the dialog class header file. But the thing is, it isn't included in my test project either but it compiles...

Outlook 2003 EAS server-based Archive solution Questions
At work they installed EAS on selected PCs including, I believe, my PC. All of my older email messages in the Inbox have a symbol that looks likke an external drive, CD disk and an envelope. When I open these messages the original attachment isn't there just the attachment titles. What do I need to do to get to the attachments? On my PC I don't see the EAS Icon in the System Tray. Does EAS need to be loaded on my PC before I can open EAS archived attachments? Thanks, Tom What is EAS???? --=20 Milly Staples [MVP - Outlook] Post all replies to the group to keep the di...

Lost my links to Visio diagrams in Word, How to restore them?
I had lots of links in my Word file to diagrams in a Visio document in the same directory. They have all disappeared. Word now says the file may not be there or is corrupted. It may have been that the directory was renamed. Is there a way to recover the links or do I have to delete all the current diagrams in Word and repaste the links using "Paste Special" again. Thanks in advance, -- Marco UCO Lick Observatory Laboratory for Adaptive Optics ...

DCDiag test questions
I'm planning to migrate my 2003R2 Forest to 2008R2 soon and based on advice posted here I'm starting that process. I'm documenting what I need to do for reference and future. 1. Step 1 is to test the forest and domains so I ran dcdiag using dcdiag /v /c /e /s:ForestRootDC > outputfile.txt A. Everything passed except ForestRootDC shows systemlog errors. From what I can find that seems to be normal? A Technet article on dcdiag shows these errors on a healthy DC. Can I ignore the syslog errors or the type "Event String could not be retrieved"? B....

can IF leave a cell EMPTY ??
I'm attempting some simple (to me at least) data analysis and am running into trouble with zero values in the data array (region). These zero values (null string: "", actually) represent non-existant data that I'd like the chart to ignore, but as the array actually contains formulas the chart engine does not see an empty cell. Is it possible to use IF to cause a cell to appear Empty (ie, IsBlank(cell) == True) ? Something like: " = if (condition, reference, Empty) " My raw data consists of date shipped and date recieved; I have created an array (Mon, Tue...

copying non adjancent cells
sorry Ms. debra dalagliesh I missed your reply to my query. I got it later through google groups search. thank you very much for clarifying my doubts. I am repeating your message for the sake of readers seeing only this messagae copy of Ms. Dalgleish's message from: Debra Dalgleish (dsd@contexturesXSPAM.com) Subject: Re: copying non adjacent cells If you want to copy multiple ranges, the selections must match in rows and columns In each column, the same rows must be selected, or nothing is selected In each row, the same columns must be selected, or nothing is selected For example, you...

Next automation question: keeping my app active
When I execute a go-to-slide operation via PowerPoint automation, the PowerPoint presenation pops to the top (desirable) and becomes the active app (undesirable). I've tried all of the tricks I know of to try to get focus back to my app and keep it active, but none of them seem to work because PowerPoint is off *becoming* active; when it finally gets around to activating, it steals focus from me. Short of something grotesque, like a timer polling for deactivation and forcing reactivation, I'm stuck. Any automation gurus have an idea of a better way to handle this? thanks joe...

BillPayment Schedule Question?
I am looking at switching from the Q to ms money, but I would like to know how Money handles Billpay payments. Does it take into account the back processing time? For example, if a bill is scheduled to be do on 8/31 and the bank need 4 days to process the payment, when does money say the bill is due? on the 8/31 or 8/27? Thanks TW In Bills & payment, you can set it to record as many days before payment as you wish. For instant if the bill is due on the 6th of the month, you can set it to show payment on the 1st of the month. (Reminder to self). Dan "wired" <wired...

Stop selecting blocks of cells
I am happily going along selecting certain cells with my mouse when, all of a sudden, a click of the mouse starts selecting five cells at a time. I don't have extend selection in use, that's not the problem. Any ideas? You might see what happens when you touch the f8 key or beat up your mouse or your keyboard shift key -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Legallisa - switching from WordPerfect" <LegallisaswitchingfromWordPerfect@discussions.microsoft.com> wrote in message news:255A9B14-BA65-4F56-8BDC-0D2335F59D9C@micr...

cell formating #2
I want to have 2 columns one headed start and the other finish. In the start column I will be inputing dd/mm and the time in 24hour format i.e 05:00 In the finish column it will be the same apart from the time that will be later and could possibly be the next day say 01:00 (20 hours). I will have a 3rd column that will show the total hours from start to finish. At the bottom of the 3rd column will be a weekly total of all the hours. So far I have formatted the cells in the 3rd column and it is showing me the hours worked, but I cannot seem to get the weekly total cell to format,if I try the no...