Recording macros with relative row and column relationships

I am trying to copy information in Excel 2003 from one row to a new row on 
separate worksheets, using a macro.  However, the macro records actual rows 
and columns (even though the "$" is not present), which I have verified by 
running the macro with a newly created row.  (It still copies the previous 
row to the other worksheets and not the new row.)  How do I record a macro 
with relative rows/columns, so that I can copy the data from each newly 
created row on the first worksheet to the remaining 14 worksheets in the 
workbook?  (Please note that I am not copying all the data from a row to 
other worksheet rows, but rather just come of the columns from that original 
row.)  Thanks.
0
drwpk (1)
10/12/2005 5:50:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
458 Views

Similar Articles

[PageSpeed] 27

When you start recording a macro, a toolbar will pop up consisting of 2 
controls. One is (obviously) the "Stop Recording" button.  The second is a 
not-so-obvious "Relative Reference" toggle. When RelativeReference is "on", 
recorded code will contain Offset references rather than absolute addresses.

HTH,
-- 
George Nicholson

Remove 'Junk' from return address.


"drwpk" <drwpk@discussions.microsoft.com> wrote in message 
news:A0C5E0D3-037D-4083-B564-79562E603FA9@microsoft.com...
>I am trying to copy information in Excel 2003 from one row to a new row on
> separate worksheets, using a macro.  However, the macro records actual 
> rows
> and columns (even though the "$" is not present), which I have verified by
> running the macro with a newly created row.  (It still copies the previous
> row to the other worksheets and not the new row.)  How do I record a macro
> with relative rows/columns, so that I can copy the data from each newly
> created row on the first worksheet to the remaining 14 worksheets in the
> workbook?  (Please note that I am not copying all the data from a row to
> other worksheet rows, but rather just come of the columns from that 
> original
> row.)  Thanks. 


0
JunkGeorgeN (154)
10/12/2005 8:06:29 PM
Reply:

Similar Artilces:

How do I import sequential data into columns in Excel?
I have a raw text file containing a single list of data (tab separated) which I want to import into columns. The data in question is temperature data taken every hour over a full year to give 8760 values. I want to group this in Excel by days, so I have 24 columns by 365 rows, with the first 24 values of the data forming the first row, the next 24 the second and so on. Can anyone suggest a way of doing this? I'm using Excel 2003. Hi if your data is on sheet1 in column A, enter the following formula in cell A1 on a second sheet =OFFSET('sheet1'!$A$1,COLUMN()-1+24*(ROW()-1),0) ...

Average Row Formula
Is there a way to calculate the average of a row if some cells are empty but when calculating the average they should have the value of the cell to the left? If the following data is enterered A B C D E F G 1 3 5 6 For the calculation the cells should use the data A B C D E F G 1 3 3 3 5 5 6 Also what would the average formula be if you wanted to skip one of the cells from the average calculation? Thanks Tom pls do NOT multipost. It wastes resources -- Don Guillett SalesAid Software donaldb@281.com "Tom" <tsanders123@hotmail.com> wrote in message news:111546...

How do I add a record counter to my form
I would like to add my own record counter, like "Record # of #", to my form. How can I do this? Private Sub Form_Load() DoCmd.GoToRecord , , acNext DoCmd.GoToRecord , , acFirst End Sub Private Sub Form_Current() Me.Caption = "Record " & CurrentRecord & " Of " & RecordsetClone.RecordCount & " Records" End Sub This code places the info in the form's title area. To place it in a Label, substitute Me.LableName.Caption for Me.Caption. To place it in a TextBox, substitute Me.TextBoxName.Va...

Need Unprotect/Protect for Excel Macro
Hello there, I need the code to put into a macro to go in and unprotect a sheet (just long enough for me to insert some data)...then re-protect the sheet before saving. This way....the user cannot destroy the data or formula in teh protected sheet. How do I do this? Is there a global command or do I have to imput my password when I ask it to follow me recording the macro. Please help! Anyone!! Thanks!! Wade Previous post of J E McGimpseys will give you the syntax Sub Toggleprotect2() Const PWORD As String = "ken" Dim wkSht As Worksheet For Each sh ...

Alternate row color for combo box
Please advice how to make the alternate row color of a combo box. I mean one row white the next yellow, next row white, next row yellow color and so on. I am using Access 2000 Regards Irshad On Fri, 14 May 2010 22:44:01 -0700, Irshad Alam <IrshadAlam@discussions.microsoft.com> wrote: There is no built-in way to do this. If you feel strongly about this, let Microsoft know and submit a feature request. -Tom. Microsoft Access MVP >Please advice how to make the alternate row color of a combo box. I mean one >row white the next yellow, next row white, next ...

Record recording
I need help in creating an automatic record form a file to an individual record. Example: You would make a work order for three different employees the first employees work order enter name, date, location, time, cost, and then this information would automatically be recorded to a permanent work sheet record specific to that employee. I have tried to manipulate VLOOKUP for this but to no avail. Any clue or help would be greatly appreciated. -- cdixon ------------------------------------------------------------------------ cdixon's Profile: http://www.excelforum.com/member.php?action=ge...

change signs in a column of numbers
I have a column of figures that includes both positive and negative numbers. I want to change the positive numbers to negative ones, and vice-versa. Any way to do this in Excel 2002? Thanx. Type -1 to any empty cell Copy that cell select the range that you will change the number sign On the worksheet menu, EDIT > PASTE SPECIAL Choose values and multiply, click OK "bob" <anonymous@discussions.microsoft.com> wrote in message news:065601c39b8e$a0babdc0$a301280a@phx.gbl... > I have a column of figures that includes both positive and > negative numbers. I want to c...

Start Up Macro
How do I create a macro that runs automatically when the spreadsheet i opened -- colinfraser@prosealuk.co ----------------------------------------------------------------------- colinfraser@prosealuk.com's Profile: http://www.officehelp.in/member.php?userid=523 View this thread: http://www.officehelp.in/showthread.php?t=126905 Posted from - http://www.officehelp.i right click the excel icon in the upper left next to file>select workbook open> put it in there -- Don Guillett SalesAid Software dguillett1@austin.rr.com "colinfraser@prosealuk.com" <colinfraserprosea...

VBA MACRO Emergency!!!!
My spreadsheet has hundreds of rows with blank rows in between. The only way I know that I am at the end of the data is to find 20 consecutive blank rows. Example below. I need to evaluate row, if this is not the last row then proceed with my other logic so forth and so on. If it is the last row, I need to return to A7 and proceed with my other logic. A 1.XXXXXXXX 2. 3.XXXXXXXX 4.XXXXXXXX 5.XXXXXXXX 6. 7. 8. 9.XXXXXXXX 10.XXXXXXXX 11-20 are blank Thanks in advance OS The easiest way to find your end of data is : LastRow = [A65000].End(xlUp).Row Regards. Daniel > My spread...

excel macro mail send
Hello, I'm using this vb macro code for sending mail. --------------------------------------------------------------- Sub mailto_Selection() Dim Email As String, Subj As String, cell As Range Dim response As Variant Dim msg As String, url As String Email = "" 'create list below Subj = "Family Newsletter" msg = "Here needs to be named range from excel(some text)" '-- Create the URL For Each cell In Selection Email = Email & cell.Text & "; " Next cell url = "mailto:" &...

Relative position of QuickLaunch and TaskBar
In my WinMe, I have a single-height TaskBar above a double-height QuickLaunch Bar. I thought this should also be possible in WinXp, but if I try to move the two-line-QuickLaunch bar below the TaskBar, the TaskBar takes up two lines (with the lower one empty) and so pushes the bottom line of QuickLaunch out of view below the bottom of the screen. Can that be remedied, so that I have the TaskBar on top and two lines of QuickLaunch below it ? -- jphilippa ...

aspx page not getting all rows from stroed procedure
Im working on an issue with an aspx page. the code is below StoredProcsDataContext storedProc = new StoredProcsDataContext ( ); var results = storedProc.ssp_GetAllAccountsForProducerCode ( producerCode ); int Reccnt = results.Count(); IList<ssp_GetAllAccountsForProducerCodeResult> resultList = results.ToList ( ); LossReportBOC searchResults = new LossReportBOC ( ); LossReportBO item; its only returning 32 rows, when i run the stored procedure standup i get all 400+ rows that im expecting. any ideas on where to look? thanks Woody any sugestion or comment made ...

Help with sql which counts records
Could someone help me to extend this sql to include: 1. a count of txtsole where the field is a YES/NO field and I want a count of where the answer is YES 2. a count of txtmulti where the field is a YES/NO field and I want a count of where the answer is YES 3. a count of txtsole where the field is a YES/NO field and I want a count of where the answer is YES 4. a count of txtnbrparts where the field is a number field and I want a count of where the answer greater than 1 I think I need to extend the WHERE statement? SELECT tblhvdealspt1.txtablhybrid, Count(*) AS totals, tblhvdealspt...

Selecting specific records
Is it possible to create a form with a "select" field (yes/no box), to select several items (say employees) and then output this information to a report with only the fields selected? -- Lori A. Pong How about multi-select list box? HTH -- -Larry- -- "Lori" <lpong@sternconstruction.com> wrote in message news:CAE5922D-92E5-4A01-83B2-6484024725C5@microsoft.com... > Is it possible to create a form with a "select" field (yes/no box), to select > several items (say employees) and then output this information to a report > with only the fields sele...

Multiple records in table to display single records
My table has 3 columns, A is account numbers, B is first name, C is last name. If an account has 2 owners, the account number is listed twice in C1, like this: A B C 123 Hank Jones 123 Ann Jones 231 Mike Smith 321 Jim John I want to query this table to display the names side by side if there is more than one owner, so that my data appears like this: A B C D E 123 Hal Jones Ann Jones 231 Tim Smith 321 Jim John Help please! Sorry, got the names mixed up! "Hurrikane4" wrote: > My table has 3 columns, A is account numb...

change column to row display
I have some data that I paste into a spreadsheet that results in a column of data. Is there a way to paste this column data into a row, or can I convert existing column data in a spreadsheet into a row? Russ Assuming you have less than 256 cells of data (unless you're using XL2007) Try this: Select from the SECOND item down through the bottom of the list. From the Excel Main Menu: <edit><copy> Select the single cell to the right of the FIRST item in the list <edit><paste special>...Check: Transpose....Click [OK] Clear the cells under the first cell. Is that ...

How do I trigger an event on opening a record
I have an Access 2003 form where I would like to take an action (e.g. via an event procedure or macro) whwnever the user opens a record for editing (e.g. via the arrowed record selector buttons). Effectively what I want is something like an On Enter or On Open control but relating to a whole record. It seems that such a control is only available for (e.g.) a text box, which relates to a single field in a record. I thought there would be something in the Detail section of the form as I thought that section related to whole records (?) but there isn't such a control there. Any idea...

Filtered Rows #2
in the spreadsheet i am using filter and in one of the macros, i need to select the first visible row below the header. The header is at row 10. eg . row10: header row11 - 90: (filtered out, not viewable) row91: first visible row below row 10 i need the macro to select row 91 in this case how can i do that. -- shimeel ------------------------------------------------------------------------ shimeel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15830 View this thread: http://www.excelforum.com/showthread.php?threadid=273638 You have one more response to ...

Update Query and Too Many Rows
I have 700 records I want to update. When I view the the data sheet view of the query, it looks correct, but when I actually run the query, I get a message that I am about to update 800 rows. Why is it going to update 100 more rows than I specified? On Tue, 23 Feb 2010 14:34:05 -0800, mwilsonh <mwilsonh@discussions.microsoft.com> wrote: >I have 700 records I want to update. When I view the the data sheet view of >the query, it looks correct, but when I actually run the query, I get a >message that I am about to update 800 rows. Why is it going to update 100 ...

Text box displaying columns from a combo box
I'm using text boxes to display the column data from a combo box. Trouble is when I move to another record or close the form and ome back in, those results are not displayed anymore and I have to reslect the values in the combo boxes to fill in the text boxes. Is there a way to lock the results to the text box for each record? TIA -- Don, Sydney Australia Don, I take it that you have a combo bound to a field in your table. When the combo is dropped down, you see several columns, but when it's not dropped down you can see only one. Therefore you have added some other text boxe...

How to: 3 side by side columns with overlayed (?) data
Hi there, Hope someone can help me create some column charts. I have 2 economic sectors, S1 and S2. Each sector consumes 3 types of energy - E1, E2 and E3 - both directly and indirectly. For each energy type, the indirect amount is larger than the direct amount. How can I graph this information so that: 1. There is one column for each of the "E's" that shows both direct and indirect amounts. The 3 E columns are side by side for each of the sectors. So from left to right the columns read: S1E1, S1E2, S1E3 S2E1, S2E2, S2E3 2. Each of the E columns show both di...

Object Source / Record Source
Hello I have created a database regarding the School System. I have created three forms (one form and two subforms) one main form is (Students) where i enter student's name, father's name, phone, address etc. now on the main Student form i want to place a subform. But the thing i want to know is: I want to make two buttons on my main form (Fees) and (Progress).. because i have two subforms (sbffees and sbfprogress) now the record source of subformbox should be controled with these two buttons. when i click the ( Fee) button the Source Object should be (sbf-fee) and when i c...

Macro Keys no functioning via Keystroke
I have set up a number of marcos assigned to Function keys, but though they function fine when triggering them with the mouse, you can't use them via a Keystroke. ie Alt F11. Does anyone have any suggestions? were they created with a mouse? try creating them with the keyboard only. "Tom" <ttaylor@cherrybrook.com> wrote in message news:563701c5258c$4e5cf050$a501280a@phx.gbl... > I have set up a number of marcos assigned to Function > keys, but though they function fine when triggering them > with the mouse, you can't use them via a Keystroke. ie Alt >...

Functional Equivalent Of xlSheetVeryHidden For Rows?
I'm creating a rather intricate and interrelated .XLS via VBA code and the .XLS itself has quite a bit of VBA code in it. User has decided that they do not want to see certain rows on the final product. Problem with just doing a Worksheet(rownum).Delete is that a deleted row may be referenced in the VBA code. I could change the VBA code, but now we're talking about a major rewrite and a lot of man hours. Just hiding it would simplify the change greatly. The zinger is that the user does not want anybody to be able to un-hide the hidden rows. My first fallback pos...

Inventory Reconcile Macro
Does anyone have a macro already recorded to enter dynamics and run and inventoroy reconcile (including the manufacturing module where it reconiles picklists.)? If so would they like to share it? If I have to I will come in after hours to record but would like to avoid this if possible. thanks -- Doug You'll need a tool called auto-dim to make this work. It's available from support. That will allow you to schedule a job that will launch GP, run a macro and then exit unattended. I'm not aware of any limitations on the macro(s) that can be run. -- Jim@TurboChef &quo...