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
561 Views

Similar Articles

[PageSpeed] 37

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:

Macro Button Question
Heya All, I am trying to insert and delete rows using macros. Ive assigned the Macro to a button, but would like to be able to insert (and delete) exactly where the button is on the worksheet, no matter where the activecell is.. So, I guess my question is, is it possible to get the cell reference behind a graphic button? Or, even better, assign a macro to a cell? ta, Maria Maria There are "insert row" and "insert column" buttons available from Tools>Customize>Commands>Insert Also "delete row" and "delete column" buttons available from To...

Macro to break up a csv file
Please help This is a few lines of a csv file that I have. I have it open in an Excel workbook. I need to write/record a macro that creates a new worksheet and cuts and pastes the data into them at every line that contains an 'R' on the left hand side. The amount of rows that contain data after the 'R' row are variable throughout the file. Thank you. H 11/12/2003 08:00:12 R 7926 1601 N B 11/12/2003 11/12/2003 10:15 L BX10A T10 2 18 6 L BX10A T10 5 71 29 L T20 43 63 22 L T21 BM 9 1 L T21 MB 6 1 ...

Identify Duplicates (Group Records First)
Hello, I have an invoice table that includes the fields CustomerNumber and DivisionNumber. Customers can deal with multiple divisions. CustomerNumber DivisionNumber ABC Co 10 ABC Co 10 ABC Co 10 Smith Inc 10 Smith Inc 10 Jones Corp 20 Jones Corp 20 Jones Corp 10 XYZ Inc 20 XYZ Inc 20 I am trying to come up with a query that will identify that Jones Corp has records in both division 20 and 10. I would like the results ...

Macro do delete values in cells
If I tipe a value (text) in N I want the values in the same row in O P & A to be deleted. Please help ...

SIMPLE MACRO!!!
I would like to write a macro in Excel that moves the cursor down one row, and then over to column J. I assume I am mixing a relative reference with an absolute referance so that moving one row down is relative, and moving over to column J is absolute. I tried writing a macro where I entered the "home" key (which took me to the beginning of the row), then one arrow key down (which obviously took me one row down), and then the right arrow key nine times (which landed me in column J where I wanted to be). I tried the macro in relative and absolute mode, but the mixing of the ...

Item record in RM00101
Does anyone know how an item record is entered into RM00101? I only have one in there and I can't figure out how it got there. Thanks. Chris, Do you mean there is an inventory item in there? If so, most likely there was either a data entry error or an import direct to table error. If that's not what you mean, can you please elaborate? -- Victoria Yudin Dynamics GP MVP Want to use Crystal Reports with GP? http://www.flex-solutions.com/gpreports.html blog: http://www.victoriayudin.com "Chris" <Chris@discussions.microsoft.com> wrote in message news:AEA12C29-07...

Can't edit publisher_address_list after appending records with Acc
With Office 2000 if I create a publisher_address_list and then use Access to append records to it, I cannot use the Edit publisher_address_list feature in Publisher to edit or add records. Is there a setting I need to change? ...

running a Macro in 2007 which was created in 2003
Hi there, I hope you can help, I am not VB literate unfortunately but I am trying to run a macro with the following line of code which creates an error: LastRow = Master_Wksht.Range("N" & Rows.Count).End(xlup).Row Is there a change between versions which means this line won't work any more? Simon, That line is fine in E2007 provided you have 'SET' Master_Wksht but if you hadn't done that it wouldn't have run in earlier versions of Excel Set Master_Wksht = Sheets("Sheet1") What have you dimmed LastRow as it should be long. ...

Pivot Table Report: Getting average of difference of two date columns
Hello I have data in excel sheet as: WORK_ID PROJECT START_DT END_DT 1001 P1 1/1/2006 1/10/2006 1002 P1 1/25/2006 2/20/2006 1003 P1 3/15/2006 3/20/2006 I want to have a report that will give me average time taken to finish a work in a project. That is, I have to take average of (end_dt - start_dt) for a project group. How do I do this in a pivot table report. When I use a calculate field to get the days worked for a work_id using DATEDIF(START_DT, END_DT, "D") then excel computes only sum on that (pivot table report ...

Deploying Macros with Exchange Server 5.5
Ok, now that I have my macro written, I need to deploy it to about 60 workstations without having to go to each one. I am running Exchange Server 5.5. Any ideas? Thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Does anyone know where the vba code in outlook is actually stored on th hard drive? Maybe we could just copy the code to all of th computers.. ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and ...

Macro to open non-Visio document on double-click
When I double-click an object in my flowchart I would like to open up a Word document or an Excel Document, but I can't see that there is an easy way to do this. Looking under the Double-Click behavior of a shape I see that I can assign a macro to run upon double-click, but I don't know how to create a new one. Can I create a macro or Visual Basic script in the Standard version of Vision that I have which would perform the "Open Document" task that I need done? visio's integration with M$ office is a common desire. there are a couple of good examples on how to ...

Unhiding rows
In Excel 2000, someone has hid the first 45 rows of the worksheet. I can't unhide them. The sheet is NOT protected. I tried various things including Edit/Go to/A1/Format/Row/Unhide but nothing works. Any clues? .......Baffled in Ontario row height? -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Connie" <marverncon@hotmail.com> wrote in message news:0b4001c35c24$077545d0$a601280a@phx.gbl... > In Excel 2000, someone has hid the first 45 rows of the > worksheet. I can't unhide them. The sheet is NOT > protected. I t...

delete empy rows
Hi, I have a sheet with 3000 lines, I am using only thre coomns A, B, C How can I delete all rows that has empty cells in colomn C i.e. row 5 contains Sam in colomn A , 2 in colomns B , nothing in colomn C. I want to delete this row row 6 contains 3 in colomn A, nothing in colomns B, 34 in colomn C. I want to keep this row. can this be done Khalil Try this on a *spare* copy .. Select col C Press F5 > Special > Blanks > OK Right-click on the selection > Delete > Entire row > OK -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>y...

Need to verify cell location before running a macro, how?
I am requesting users to click on a cell in column B (in which they are actually selecting a row of reference information), then to click on an icon which launches a macro that runs relevant to the row selected in column B. I would like Excel to verify that the selected cell is actually in column B prior to running the macro. If a cell in column B is not selected, I want to present a pop up message reminding them to select a cell in column B. Please help! If ActiveCell.Column <> 2 Then MsgBox "Don't Do That" Exit Sub End If -- Jim Cone Portland, Oregon USA http:...

Rows to Columns
I have data arranged in rows across a spread sheet, for example B2, C2, D2, etc. How can I change that to a column, for example A1, A2, A3, etc without reentering all the data? Thank you --Select B2:D2. Copy the range --Select A1. Right click>PasteSpecial>Transpose>OK If this post helps click Yes --------------- Jacob Skaria "Walter Seaton" wrote: > I have data arranged in rows across a spread sheet, for example B2, C2, D2, > etc. > How can I change that to a column, for example A1, A2, A3, etc without > reentering all the data? >...

assing macro to an an image, shape, text in Word 2007
I would like to run one of my macros when either an image, or a shape, or a text is clicked in Word 2007 document. Thanks, Use an ActiveX control, as they have a _Click event. Lev wrote: >I would like to run one of my macros when either an image, or a shape, or a >text is clicked in Word 2007 document. >Thanks, -- Gerry Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/word-programming/201004/1 Another method is to insert a MacroButton field and paste the image into the field code where the display text would be. See http://www.word...

How to Edit the Macro that exists behind an existing Command Butto
Hi There, Hoping someone can help me out ... I have inherited the task of managing an Access 2007 application. Within this application I have an Access 2007 form that has a Command Button. I'm not sure how this Command Button was created (Macro, Query, etc.) but I do know what it does. I would like to edit the Query/Macro this Command Button executes. I'd rather not edit the VBA coding as I am a VBA Beginner. Can you pleae let me know how I can edit the Command Button's Query/Macro. Thank you, Jason A command button can run a macro or a VB event proce...

65536 Rows for how long????
Hi there! I was wondering: is Microsoft planning on expanding the 65536 row limit? They most deffinitely should! Best regards, Albert "Albert" <Albert@discussions.microsoft.com> wrote > I was wondering: is Microsoft planning on expanding the 65536 row limit? > They most deffinitely should! Why "certainly"? -- Bob http://www.kanyak.com Well, not "certainly", but its certainly my opinion. I think 65536 is not enough. I believe that, among other things, Excel was made for managing large amounts of information efficiently. I think that in this d...

How can I color every other row
Help please. I want to make it easier to use a large spreadsheet where two rows are used for each record. Filling in the background color of every second row prevents mistakes when entering data. I want to color only the used range, not the entire row. I recorded a macro and got the following: TheRange.Activate With Selection.Interior .ColorIndex = 36 .Pattern = xlSolid End With I really appreciate the help. On way: Dim iCtr As Long With ActiveSheet.UsedRange For iCtr = .Row To .Rows(.Rows.Count)....

selecting rows using a variable
I am using excel 2003 and I need to select rows using a variable. Here is what I have now: Dim name As String name = Range("e800") ActiveWorkbook.Sheets("individual stats").Range("a1:a1540").Select Selection.EntireRow.Hidden = True Dim Start As Integer Dim finish As Integer Start = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), Range("t800:t881")) finish = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), Range("u800:u881")) ActiveWorkbook.Sheets("individual stats&q...

change range of relative formulas to absolute
Excel 2003. I'd like to change an entire range of cell formulas from relative to absolute. Mark You would need VBA to make global changes to cell references. Here are four........ Sub Absolute() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlAbsolute) End If Next End Sub Sub AbsoluteRow() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlAbsRowRelColumn) ...

select and delete all blank rows
I could save a lot of time if I knew how to select and delete all blank rows from a document, and also separate things. An example of separating things would be a list contains fruits, vegetables and meat. I could separate all the fruits from the list easily. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then clic...

Filtering rows with tracked changes
Hi I am trying to work out how to filter the rows that have been updated over a period of a couple of days. I understand that you can highlight records with tracked changes in a certain time period � but I would like to be able to just have those rows that have highlighted cells to look at. Any ideas out there? You can list changes on a new sheet but it doesn't allow you to see the whole row. Cheers, Mike P.s � is it possible to highlight changes between dates � I can only see highlighting changes since � ...

Filtered list & maximum no columns problem
Hi I have a filtered list with a considerable amount of associated data, which I am performing calculations on. I have run out of columns for this data. It is not practical to split the data into other sheets, as I am making frequent changes to the filter criteria. Any suggestions? Bertie -- claytorm ------------------------------------------------------------------------ claytorm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11610 View this thread: http://www.excelforum.com/showthread.php?threadid=398516 ...

Photos not moving with rows when sorted!
Excel 2000. A worksheet with names of people, and various data about them. Each row is a record--the person's first name, last name, and various data, each column is a field. One column is labeled "Photo", and has a small photo of the person in it. I notice that when I sort the rows, by data in one of the columns, such as "first name", "last name", etc., the photos do not sort correctly with their rows. Why? Do photos just remember their place on the page, their cell position (E7 or whatever), and not the row they are in, are not considered part of the row...