Need macro to move to first blank cell in column

Hi. I am trying to use a macro to move from A1 to the first blank cell in col 
A. There are no gaps in the data. When the first blank cell is selected, data 
will be copied and pasted from another worksheet. Then the adjacent cell in 
col B must be selected and again data will be copied and pasted there from 
another worksheet. Finally, the adjacent cell in col C must be selected but 
the formula from the cell above must be extended down one cell. I have 
recorded a macro which does all this but the problem is that when I repeat 
the macro the same row is always selected instead of the next blank one. Here 
are the steps I did while recording the macro:
1 .Select & copy A1
2.Press <cntl> + <end> (this brings me to last filled cell in col C)
3. Press down arrow key 1 time and left arrow key 2 times to move to 1st 
blank cell in col A. 
4. Paste in col A blank cell
5. Copy cell from other worksheet (always the same cell #)
6. Select original worksheet. Press right arrow key once to move to col B.
7. Paste in col B blank cell
8. Press right arrow once and up arrow once to move to last filled cell in 
col C.
9. Extend the formula down one cell in col C.
Now the previous 3 blank  cells in col A, B & C are filled. But when I 
repeat the macro the same cells are filled, not one down each time. Can 
someone help? 

Thanks,
Joe M.
0
Utf
12/16/2009 7:25:01 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
991 Views

Similar Articles

[PageSpeed] 2

If the description of what you are doing is all you would like to achieve, 
then this could be acheived by formulae in each cell.

From your description:
 cell A1 is copied down column A of the same sheet
 cell B2 and subsequent cells in column B are the same cell reference from 
another sheet (assume named 'sheet2' and cell ref is B1)
 cell C1 is copied down column C of the same sheet.

If you put =$A$1 in cell A2; ='sheet'!$B$1 in cell B2; =C1 in cell C2; and 
and the select/highlight the three cells A2,B2,C2 together and then click and 
drag down the formulas in the columns you will get the result as described, 
without the use of macros.

The reason that the recorded macro doesent work though, is that it is 
'static'. Unfortunately Excel is unable to detirmin when an incremental macro 
is required, for example to step through rows. The macro would need to be 
viewed in the VB editor and edited to allow the row counts to increment, or 
for the last blank cell to be identified and that reference used each time 
the maro is run.

Not an ideal answer i know, but i hope it helps...


-- 
Kind regards

Rik


"Joe M." wrote:

> Hi. I am trying to use a macro to move from A1 to the first blank cell in col 
> A. There are no gaps in the data. When the first blank cell is selected, data 
> will be copied and pasted from another worksheet. Then the adjacent cell in 
> col B must be selected and again data will be copied and pasted there from 
> another worksheet. Finally, the adjacent cell in col C must be selected but 
> the formula from the cell above must be extended down one cell. I have 
> recorded a macro which does all this but the problem is that when I repeat 
> the macro the same row is always selected instead of the next blank one. Here 
> are the steps I did while recording the macro:
> 1 .Select & copy A1
> 2.Press <cntl> + <end> (this brings me to last filled cell in col C)
> 3. Press down arrow key 1 time and left arrow key 2 times to move to 1st 
> blank cell in col A. 
> 4. Paste in col A blank cell
> 5. Copy cell from other worksheet (always the same cell #)
> 6. Select original worksheet. Press right arrow key once to move to col B.
> 7. Paste in col B blank cell
> 8. Press right arrow once and up arrow once to move to last filled cell in 
> col C.
> 9. Extend the formula down one cell in col C.
> Now the previous 3 blank  cells in col A, B & C are filled. But when I 
> repeat the macro the same cells are filled, not one down each time. Can 
> someone help? 
> 
> Thanks,
> Joe M.
0
Utf
12/16/2009 7:46:01 PM
Thanks, but extending a formula down will not help for what I need. I need a 
macro as this worksbook is to automatically fill the values. Also, I am not 
copying down from A2 to A3 to A4 etc. I am copying A1 to i.e. A43. B43 is 
copied from another w/s fixed cell location. C43 is filled by a formula 
extended from C42. When the macro is run again the next row; A44, B44 and C44 
are to be filled. I hope this better explains what I am trying to do.
Thanks,
Joe M.

"Rik_UK" wrote:

> If the description of what you are doing is all you would like to achieve, 
> then this could be acheived by formulae in each cell.
> 
> From your description:
>  cell A1 is copied down column A of the same sheet
>  cell B2 and subsequent cells in column B are the same cell reference from 
> another sheet (assume named 'sheet2' and cell ref is B1)
>  cell C1 is copied down column C of the same sheet.
> 
> If you put =$A$1 in cell A2; ='sheet'!$B$1 in cell B2; =C1 in cell C2; and 
> and the select/highlight the three cells A2,B2,C2 together and then click and 
> drag down the formulas in the columns you will get the result as described, 
> without the use of macros.
> 
> The reason that the recorded macro doesent work though, is that it is 
> 'static'. Unfortunately Excel is unable to detirmin when an incremental macro 
> is required, for example to step through rows. The macro would need to be 
> viewed in the VB editor and edited to allow the row counts to increment, or 
> for the last blank cell to be identified and that reference used each time 
> the maro is run.
> 
> Not an ideal answer i know, but i hope it helps...
> 
> 
> -- 
> Kind regards
> 
> Rik
> 
> 
> "Joe M." wrote:
> 
> > Hi. I am trying to use a macro to move from A1 to the first blank cell in col 
> > A. There are no gaps in the data. When the first blank cell is selected, data 
> > will be copied and pasted from another worksheet. Then the adjacent cell in 
> > col B must be selected and again data will be copied and pasted there from 
> > another worksheet. Finally, the adjacent cell in col C must be selected but 
> > the formula from the cell above must be extended down one cell. I have 
> > recorded a macro which does all this but the problem is that when I repeat 
> > the macro the same row is always selected instead of the next blank one. Here 
> > are the steps I did while recording the macro:
> > 1 .Select & copy A1
> > 2.Press <cntl> + <end> (this brings me to last filled cell in col C)
> > 3. Press down arrow key 1 time and left arrow key 2 times to move to 1st 
> > blank cell in col A. 
> > 4. Paste in col A blank cell
> > 5. Copy cell from other worksheet (always the same cell #)
> > 6. Select original worksheet. Press right arrow key once to move to col B.
> > 7. Paste in col B blank cell
> > 8. Press right arrow once and up arrow once to move to last filled cell in 
> > col C.
> > 9. Extend the formula down one cell in col C.
> > Now the previous 3 blank  cells in col A, B & C are filled. But when I 
> > repeat the macro the same cells are filled, not one down each time. Can 
> > someone help? 
> > 
> > Thanks,
> > Joe M.
0
Utf
12/16/2009 8:06:01 PM
What you are doing is a mix of Relative and Staic references. You can record 
a mocro that will do this but it is tricky. Oddly enough it is much easier to 
write from scratch.

If you want to record the macro then Before you do the down arro operation 
followed by the subsequent down arrow, first select the relative opton on the 
Stop recording tool bar. This makes your movements relative to the active 
cell and not absolute. To complete this macro you will have to switch it on 
and off fairly often and plan on getting it wrong more than once...

Written from scratch it will look something like this...

Sub CopyStuff()
 dim rngToPaste as range

 set rngtopaste = cells(rows.count, "A").end(xlup).offset(1,0)
 'rngtopaste is now at the first blank cell in column A
 sheets("Sheet1").Range("C1").copy rngtopaste 'paste in A
 Sheets("Sheet2").range("D100").copy rngtopaste.offset(0,1) 'Paste in B
 rngtopaste.offset(-1, 2).copy rngtopste.offset(0,2) 'Paste in C
 
end sub
-- 
HTH...

Jim Thomlinson


"Joe M." wrote:

> Hi. I am trying to use a macro to move from A1 to the first blank cell in col 
> A. There are no gaps in the data. When the first blank cell is selected, data 
> will be copied and pasted from another worksheet. Then the adjacent cell in 
> col B must be selected and again data will be copied and pasted there from 
> another worksheet. Finally, the adjacent cell in col C must be selected but 
> the formula from the cell above must be extended down one cell. I have 
> recorded a macro which does all this but the problem is that when I repeat 
> the macro the same row is always selected instead of the next blank one. Here 
> are the steps I did while recording the macro:
> 1 .Select & copy A1
> 2.Press <cntl> + <end> (this brings me to last filled cell in col C)
> 3. Press down arrow key 1 time and left arrow key 2 times to move to 1st 
> blank cell in col A. 
> 4. Paste in col A blank cell
> 5. Copy cell from other worksheet (always the same cell #)
> 6. Select original worksheet. Press right arrow key once to move to col B.
> 7. Paste in col B blank cell
> 8. Press right arrow once and up arrow once to move to last filled cell in 
> col C.
> 9. Extend the formula down one cell in col C.
> Now the previous 3 blank  cells in col A, B & C are filled. But when I 
> repeat the macro the same cells are filled, not one down each time. Can 
> someone help? 
> 
> Thanks,
> Joe M.
0
Utf
12/16/2009 9:10:01 PM
to find the next empty row
you need to insert

Range("A65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select

this will select the next empty cell in column A
greetings from New Zealand


"Joe M." <JoeM@discussions.microsoft.com> wrote in message 
news:6EB0BA29-964A-4F34-886F-086F77A7C85B@microsoft.com...
> Thanks, but extending a formula down will not help for what I need. I need 
> a
> macro as this worksbook is to automatically fill the values. Also, I am 
> not
> copying down from A2 to A3 to A4 etc. I am copying A1 to i.e. A43. B43 is
> copied from another w/s fixed cell location. C43 is filled by a formula
> extended from C42. When the macro is run again the next row; A44, B44 and 
> C44
> are to be filled. I hope this better explains what I am trying to do.
> Thanks,
> Joe M.
>
> "Rik_UK" wrote:
>
>> If the description of what you are doing is all you would like to 
>> achieve,
>> then this could be acheived by formulae in each cell.
>>
>> From your description:
>>  cell A1 is copied down column A of the same sheet
>>  cell B2 and subsequent cells in column B are the same cell reference 
>> from
>> another sheet (assume named 'sheet2' and cell ref is B1)
>>  cell C1 is copied down column C of the same sheet.
>>
>> If you put =$A$1 in cell A2; ='sheet'!$B$1 in cell B2; =C1 in cell C2; 
>> and
>> and the select/highlight the three cells A2,B2,C2 together and then click 
>> and
>> drag down the formulas in the columns you will get the result as 
>> described,
>> without the use of macros.
>>
>> The reason that the recorded macro doesent work though, is that it is
>> 'static'. Unfortunately Excel is unable to detirmin when an incremental 
>> macro
>> is required, for example to step through rows. The macro would need to be
>> viewed in the VB editor and edited to allow the row counts to increment, 
>> or
>> for the last blank cell to be identified and that reference used each 
>> time
>> the maro is run.
>>
>> Not an ideal answer i know, but i hope it helps...
>>
>>
>> -- 
>> Kind regards
>>
>> Rik
>>
>>
>> "Joe M." wrote:
>>
>> > Hi. I am trying to use a macro to move from A1 to the first blank cell 
>> > in col
>> > A. There are no gaps in the data. When the first blank cell is 
>> > selected, data
>> > will be copied and pasted from another worksheet. Then the adjacent 
>> > cell in
>> > col B must be selected and again data will be copied and pasted there 
>> > from
>> > another worksheet. Finally, the adjacent cell in col C must be selected 
>> > but
>> > the formula from the cell above must be extended down one cell. I have
>> > recorded a macro which does all this but the problem is that when I 
>> > repeat
>> > the macro the same row is always selected instead of the next blank 
>> > one. Here
>> > are the steps I did while recording the macro:
>> > 1 .Select & copy A1
>> > 2.Press <cntl> + <end> (this brings me to last filled cell in col C)
>> > 3. Press down arrow key 1 time and left arrow key 2 times to move to 
>> > 1st
>> > blank cell in col A.
>> > 4. Paste in col A blank cell
>> > 5. Copy cell from other worksheet (always the same cell #)
>> > 6. Select original worksheet. Press right arrow key once to move to col 
>> > B.
>> > 7. Paste in col B blank cell
>> > 8. Press right arrow once and up arrow once to move to last filled cell 
>> > in
>> > col C.
>> > 9. Extend the formula down one cell in col C.
>> > Now the previous 3 blank  cells in col A, B & C are filled. But when I
>> > repeat the macro the same cells are filled, not one down each time. Can
>> > someone help?
>> >
>> > Thanks,
>> > Joe M. 


0
Bill
12/16/2009 9:10:30 PM
Jim,

I looked for the Relative option when I go to TOOLS > MACRO > STOP 
RECORDING, I don't see this option. I don't see a macro toolbar. I'm using 
Excel 2003.
Many thanks,
Joe M.

 
"Jim Thomlinson" wrote:

> What you are doing is a mix of Relative and Staic references. You can record 
> a mocro that will do this but it is tricky. Oddly enough it is much easier to 
> write from scratch.
> 
> If you want to record the macro then Before you do the down arro operation 
> followed by the subsequent down arrow, first select the relative opton on the 
> Stop recording tool bar. This makes your movements relative to the active 
> cell and not absolute. To complete this macro you will have to switch it on 
> and off fairly often and plan on getting it wrong more than once...
> 
> Written from scratch it will look something like this...
> 
> Sub CopyStuff()
>  dim rngToPaste as range
> 
>  set rngtopaste = cells(rows.count, "A").end(xlup).offset(1,0)
>  'rngtopaste is now at the first blank cell in column A
>  sheets("Sheet1").Range("C1").copy rngtopaste 'paste in A
>  Sheets("Sheet2").range("D100").copy rngtopaste.offset(0,1) 'Paste in B
>  rngtopaste.offset(-1, 2).copy rngtopste.offset(0,2) 'Paste in C
>  
> end sub
> -- 
> HTH...
> 
> Jim Thomlinson
> 
> 
> "Joe M." wrote:
> 
> > Hi. I am trying to use a macro to move from A1 to the first blank cell in col 
> > A. There are no gaps in the data. When the first blank cell is selected, data 
> > will be copied and pasted from another worksheet. Then the adjacent cell in 
> > col B must be selected and again data will be copied and pasted there from 
> > another worksheet. Finally, the adjacent cell in col C must be selected but 
> > the formula from the cell above must be extended down one cell. I have 
> > recorded a macro which does all this but the problem is that when I repeat 
> > the macro the same row is always selected instead of the next blank one. Here 
> > are the steps I did while recording the macro:
> > 1 .Select & copy A1
> > 2.Press <cntl> + <end> (this brings me to last filled cell in col C)
> > 3. Press down arrow key 1 time and left arrow key 2 times to move to 1st 
> > blank cell in col A. 
> > 4. Paste in col A blank cell
> > 5. Copy cell from other worksheet (always the same cell #)
> > 6. Select original worksheet. Press right arrow key once to move to col B.
> > 7. Paste in col B blank cell
> > 8. Press right arrow once and up arrow once to move to last filled cell in 
> > col C.
> > 9. Extend the formula down one cell in col C.
> > Now the previous 3 blank  cells in col A, B & C are filled. But when I 
> > repeat the macro the same cells are filled, not one down each time. Can 
> > someone help? 
> > 
> > Thanks,
> > Joe M.
0
Utf
12/16/2009 9:32:02 PM
Joe

You have lost the stop recording toolbar?

Go to Tools>Macro>Record new macro.

Click OK on the macroname and where to store dialog.

Tools>Customize...............hit Stop Recording which will show the SR
Toolbar with a stop button and relative reference button,

Click on the stop recording button........DO NOT click on the "x" or you
will lose the toolbar again.

Delete the module with your dummy macro.

Next time you record a macro the SR toolbar should come up.


Gord Dibben  MS Excel MVP

On Wed, 16 Dec 2009 13:32:02 -0800, Joe M. <JoeM@discussions.microsoft.com>
wrote:

>Jim,
>
>I looked for the Relative option when I go to TOOLS > MACRO > STOP 
>RECORDING, I don't see this option. I don't see a macro toolbar. I'm using 
>Excel 2003.
>Many thanks,
>Joe M.
>
> 
>"Jim Thomlinson" wrote:
>
>> What you are doing is a mix of Relative and Staic references. You can record 
>> a mocro that will do this but it is tricky. Oddly enough it is much easier to 
>> write from scratch.
>> 
>> If you want to record the macro then Before you do the down arro operation 
>> followed by the subsequent down arrow, first select the relative opton on the 
>> Stop recording tool bar. This makes your movements relative to the active 
>> cell and not absolute. To complete this macro you will have to switch it on 
>> and off fairly often and plan on getting it wrong more than once...
>> 
>> Written from scratch it will look something like this...
>> 
>> Sub CopyStuff()
>>  dim rngToPaste as range
>> 
>>  set rngtopaste = cells(rows.count, "A").end(xlup).offset(1,0)
>>  'rngtopaste is now at the first blank cell in column A
>>  sheets("Sheet1").Range("C1").copy rngtopaste 'paste in A
>>  Sheets("Sheet2").range("D100").copy rngtopaste.offset(0,1) 'Paste in B
>>  rngtopaste.offset(-1, 2).copy rngtopste.offset(0,2) 'Paste in C
>>  
>> end sub
>> -- 
>> HTH...
>> 
>> Jim Thomlinson
>> 
>> 
>> "Joe M." wrote:
>> 
>> > Hi. I am trying to use a macro to move from A1 to the first blank cell in col 
>> > A. There are no gaps in the data. When the first blank cell is selected, data 
>> > will be copied and pasted from another worksheet. Then the adjacent cell in 
>> > col B must be selected and again data will be copied and pasted there from 
>> > another worksheet. Finally, the adjacent cell in col C must be selected but 
>> > the formula from the cell above must be extended down one cell. I have 
>> > recorded a macro which does all this but the problem is that when I repeat 
>> > the macro the same row is always selected instead of the next blank one. Here 
>> > are the steps I did while recording the macro:
>> > 1 .Select & copy A1
>> > 2.Press <cntl> + <end> (this brings me to last filled cell in col C)
>> > 3. Press down arrow key 1 time and left arrow key 2 times to move to 1st 
>> > blank cell in col A. 
>> > 4. Paste in col A blank cell
>> > 5. Copy cell from other worksheet (always the same cell #)
>> > 6. Select original worksheet. Press right arrow key once to move to col B.
>> > 7. Paste in col B blank cell
>> > 8. Press right arrow once and up arrow once to move to last filled cell in 
>> > col C.
>> > 9. Extend the formula down one cell in col C.
>> > Now the previous 3 blank  cells in col A, B & C are filled. But when I 
>> > repeat the macro the same cells are filled, not one down each time. Can 
>> > someone help? 
>> > 
>> > Thanks,
>> > Joe M.

0
Gord
12/16/2009 10:22:11 PM
Reply:

Similar Artilces:

The database "First Storage Group\Public Folder Store (2000SVR)" has 38 megabytes of free space after online defragmentation has terminated.
Hello, I have a Windows 2000 Server (SP4) Exchange (SP3) Box and the Exchange Database hit his Limit With this Error. Event Type: Warning Event Source: ESE Event Category: Space Management Event ID: 445 Date: 5/13/2004 Time: 6:15:02 PM User: N/A Computer: 2000SVR Description: Information Store (2272) The database D:\Program Files\Exchsrvr\mdbdata\priv1.edb has reached its maximum size of 16383 MB. If the database cannot be restarted, an offline defragmentation may be performed to reduce its size. However I did fix the retention policy to 0 and unchecked the permenantly delete mailboxs...

Macro #4
I would like to create a macro which would run a particular rule on the Junk mail folder. Unfortunately in Outlook it is not possible to create the macro by recording keystrokes as in Excel and I am not familiar with using the VB editor. Is there information available about how to create the above macro? Your help will be greately appreciated. Al http://www.outlookcode.com -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, ...

Moving database between servers
Hi I have 2 Exchange servers in the same domain name. I want to know if there is a good way to move the database from one server to the other. I know I could use the Wizard in Active directory and computer to move the users from one server to a other but I would like to know if there is a better way to do the operation. Thank you for your help Sebastien The best way is to migrate/move the users, as you have already mentioned. Bob "Sebas" <Sebas@discussions.microsoft.com> wrote in message news:DBB0D2CD-B07E-49A8-A8EA-EC3642B575E2@microsoft.com... > Hi > > I ha...

Moving users to new business unit
I setup one main business unit and have 3 users assigned to it. We are now looking into getting CRM for another one of our companies, but do not want the users of these 2 companies to see each other's contacts etc. I have setup 2 new business units, each children of the one parent. If I move user A (whom can read all, edit all contacts is db right now) into New Unit 1, will they still be able to have all the same access? If I create a NEW user under New Unit 2, will that user be able to see the contacts for User A? Basically it comes down to is how to restrict complete access b...

exceltips.net needs moderators
I'm looking for assistance with my website http://exceltips.net. If anyone is interested in becoming an editor, or a forum moderator please respond. I could use any help. thanks alot, brian -- exceltips ------------------------------------------------------------------------ exceltips's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25808 View this thread: http://www.excelforum.com/showthread.php?threadid=392084 hi, i might be interested. depends. FSt1 "exceltips" wrote: > > I'm looking for assistance with my website http://excelt...

Rubbing out a part of an image/shape. HELP NEEDED!
In one part of an image I am creating, I have used the curved edge of numerous circles in Publisher (simple circles from AutoShapes). I do want one part of the curved edge but NOT the entire circle. Is there any way of erasering or rubbing out the unwanted part of the circle(s)? I tried using the simple program 'paint' by copying and pasting the image there and the 'eraser' tool that program has is exactly what I need. The only problem is, that when transfering the image from Publisher to Paint, it gets distorted and all messed up. So, what I am asking for is, if anyone ...

deleting a row with macro
NOOBIE here... Here's what Ive got. I am trying to create a "Delete Vendor" button I know there is a much better way to do this.. On one sheet is the linked cell of a combobox. I am selecting that cell, copying and switching to the data sheet. There I want to find the copied cell's value, select that complete ro and delete it. (Only the content, not the row itself because I wil have a sort run afterward and it is an array so I want to keep th size.) My problem is that "Rows(ActiveCell.Row).Select" is executing befor the find dialog box has time to appear effecti...

Forms: can a cell itself be clickable?
Hi! I'm trying to create a form for my boss. I'm using the forms toolbar, but what it does is throw in a check box for instance, but if I change the cell in any way, the "picture" of the click box stays where it's at. Any way to change this? In other words, I don't want to the click box to be a picture, I want it to be an integral part of the cell, so I can modify it as needed (ie. place it centrered horizontally or vertically etc. Am I dreaming in colour? Thanks! Carmen I've seen a macro that will insert a checkmark when you select the cell, but I&#...

Moving email folders, accounts, rules etc from Outlook 2003 to 200
Please would you be willing to help me [a raw beginner] to retrieve email folders, the emails in the folders, accounts, rules etc from harddrive with Outlook 2003 and Windows XP, and move them successfully to new laptop with Outlook 2007 [Windows 7]. So far I have 1. searched for all pst files on old harddrive and copied all of them to memory stick using another XP laptop. Could not successfully do with the new laptop due to "access" issues. 2. imported pst files to outlook 2007--contacts folders arrived, calendar arrived, some of inbox, some of deleted, some of sen...

Removing the First Server Exchange Server
We are currently trying to remove an older exchange 2000 server. We followed through the appropriate KB articles and then turned off the server before permanently removing it. Everything seems to be fine that day, but by the next morning some users complain that exchange is running very, very slow. But its only a few people. These symptoms go away once the server is turned back on. Any thoughts or suggestions? Have you replicated all the system public folders to the new server and are all the users pointed to the new server we found a couple that did not "follow" the m...

Personal Folder (pst) messages being moved to deleted items folder
A few users I assist have recently had the cleansing of their Personal Folders data. All of this data if broken into dozens of sub-folders and for an unknown reason moved into the "Deleted Items" folder. The good news is the messages are all there, but the bad news, it's no longer sorted logically. 1) Is there any way to reverse this? 2) How can we prevent this from happening again or to others? (I've verified auto archiving is turned off - and noone has run it on their own) Windows XP SP3 - Outlook 2007 SP2. Thank you, Jim 1) Restoring from backup wi...

Pictures in Excel cells
Dear friends I want to create a book with personal descriptions, including pictures. But how can I get a picture in an Excel cell? I thought that =HYPERLINK("file.jpg") would be the thing, but the pictures are not retrieved when I combine the Excel sheet to the final Word file. Please help. -- Jos´┐Ż Do you object if ... Alt > i > p > f and select the picture what you want to import. HTH -- MRT "Jose" <jose@127.0.0.1> wrote in message news:e4nsg59mm09cuqgr2etqtm06jm87idk343@4ax.com... > Dear friends > > I want to create...

How to lock cell
Hi, How to lock a particular cell without locking the whole work sheet Pls help me. -- Moideen Moideen;1599749 Wrote: > Hi, > > How to lock a particular cell without locking the whole work sheet > > Pls help me. Do you mean have one particular cell that cannot be updated/altered but the rest can? Or do you mean have a locked cell but without protecting the work sheet? -- Spencer101 By default all cells on a sheet are locked when the sheet is protected. Select all cells(CTRL + a...........twice in Excel 2003) then go to format>cells>protection and unlo...

Header and cell referencing
My issue is this: I have 6 sheets. The first sheet contains the clients information. I want to take the last name of the client from the first sheet and have it appear in the header of every sheet after it. How do I create a cell reference inside a header? David David Sub Path_All_Sheets() Set wkbktodo = ActiveWorkbook For Each ws In wkbktodo.Worksheets ws.PageSetup.RightHeader = Sheets("Clients").Range("A1").Text Next End Sub Assumes client's name is in A1 Note: you can do the same thing by right-click on first sheet tab and "select all ...

Copy column headings AND formula totals to blank spreadsheet
Hi Everyone! I apologize that this was likely asked before but could someone show me how I can copy my 'column headings, column widths and totals formula' to a blank spreadsheet tab? I have set up budget spreadsheet where I input my receipts to keep track of monthly expenditures. Could someone explain how to copy the column headings, the column widths, AND the formulas of each column to a blank spreadsheet tab so each month will contain the 'same headings, same column widths and the same formula for each column'? Thanks so much for any help!!! katy Assuming you alr...

Cell Number Format
Hi, when i type for example this any number with more than 15 digits, the first 15 digits appear like i wrote it but the rest of them are changed by zeros. EX: i write this 123456789123456789 and the number that is kept is 123456789123456000 Does anyone experienced this and knows how to resolve it. Thanks, Ricardo As you can verify in XL Help ("Specifications"), XL is limited to 15 decimal digits of precision for numbers. If you're entering, say, a credit card number, which doesn't require any math to be performed on it, you can preformat the cell as Text or prefix ...

cell content into a note
hey guys, just curious if this can be achieved or how it can be achieved say i have this Column A Row 1 = Short Description Column B Row 1 = Long descriptoin B2:B10 = are all the short descriptions B2:B10 = are all the long descriptions is there a way to just put the comment into the short description cells from the long description cells via a note and then hide the long description column? can anyone advise as to how this can be achieved? cheers Don't multipost. You could add Comments to the column A cells with the text from Column B cells and have Column B hidden. Sub Comment...

squashed columns
Apologies if this has been asked before. One of my users occasionally has a problem where the columns in her spreadsheet appear all "squashed up" when she opens the file. She is using Office 97 SR-2. Any ideas why this is happening? ~Paul. Did she or any one else save the file with a zoom factor (far) less then 100% ? -- Regards, Auk Ales * Please reply to this newsgroup only * * I will not react on unsolicited e-mails * "Paul Birney" <pbirney@eircom.ie> wrote in message news:uqstZ%23QAEHA.2484@TK2MSFTNGP12.phx.gbl... > Apologies if this has been asked ...

Move to match cells
My spreadsheet looks like this: A B C D E 1 000801025-8 009601025-3 2007 000801025-8 2007 2 000801026-9 009601026-4 2004 000801026-9 2004 3 000801028-1 009601028-6 2006 000801028-1 2006 4 000801036-8 009601036-3 2008 000801036-8 2008 5 000801044-5 009601044-0 2007 000801044-5 2007 6 000801044-5 009601055-0 2004 000801055-5 2004 7 000801055-5 009601064-8 2001 000801064-3 2001 8 000801055-5 009601091-2 2006 000801091-7 2006 9 000801064-3 009601095-6 2002 000801095-1 2002 10 000801091-7 009601097-8 1998 00...

Nonadjacent Cell Text Overflow
Is there a way to merge nonadjacent cells for text overflow. What I' trying to do is have text entered into a row of merged cells(e.g. Row A1:F1) but if the text is more than the cells can contain to overflo into another nonadjacent row of merged cells(Row3 A1:F1). Thanks fo any help with this -- Db171 ----------------------------------------------------------------------- Db1712's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1591 View this thread: http://www.excelforum.com/showthread.php?threadid=27498 ...

Column that needs separation
I am very new with excel and I am putting together a worksheet that has a column consisting of two primary names. I would like to format the entire column with the result being both names having their own color throughout the column. Alternatively, I could go to each row and highlight the name but there are about 1000 rows. Thanks, Aaron Say column A has entries like: James Ravenswood Run this small macro: Sub Colorizer() Dim A As Range, r As Range Set A = Intersect(ActiveSheet.UsedRange, Range("A:A")) For Each r In A v = r.Value s = Split(v, ...

Formatting Phone Numbers in a Merged Cells...
If I want to merge a "Company Name", "Phone Number" and a "Fax" number into 1 cell (for use in a drop down box), is there a way to maintain the 10 digit format for the phone/fax numbers? The phone/fax #'s appear as a string of 10 digits when they are merged, and are hard to read. Currently using a formula like this: =A3&" Phone: "&B3&" Fax: "&C3 I am tired of trying to get it to work, and can't seem to find the answer here. Thank you in advance! One way: =A3 & TEXT(B3,""" Phone:...

blank page prints after group (sometimes)
I have report of students with the class_id defined as a group. "Force New Page" is defined "Before Section". Whether I have a page and a group header defined (they can be the same anyway) or just a page header defined, I am getting the same problem: 27 students can fit onto a page. However, if there is a class of 25 students, the next page is blank except for the header. (I have not seen a class of 27 students so I have not tested that yet). I can't seem to find a way out of it. Where I am I going wrong? -- Message posted via AccessMonster.com http://www.acce...

Macro to copy cell down
I want to create a macro that will copy the cell above to the active cell. OR the built-in key that will do that tenbob@optonline.net Bob A few seconds with the macro recorder set to "Relative Reference" gave me this. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 11/3/2003 by Gord Dibben ' ' Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False End Sub Gord Dibben XL2002 On Tue, 04 Nov 2003 04:26:28 GMT, Bob K. <tenbob@optonline.net> wrote: >I want to create a mac...

Two cells divided into next cell
Hope this makes sense. I have F column that totals down to F16. Then I have a total on D16. I need both totals to be divided and show up on G16. Every cell has $ Amts, but the only cells not totaled down the row are E,G and J. So G16 is blank. Is this possible to do and If so how. I don't even know the formula to Divide and I need this to automatically happen every time I Insert the Amts in the cells. Please help In G16: =D16/F16 "Day" <Day@discussions.microsoft.com> wrote in message news:62C49FCC-273A-425B-9EEF-DCF2109A0DD9@microsoft.com... > > ...