excel course in london - macro recording

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel course in london - Macro recording

excel course in london - Macro recording

resolvedResolved · Low Priority · Version Standard

Delia has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Excel VBA Intro Intermediate course

Macro recording

I want to use a macro to copy data down to fill in all the blank cells in the column below the data until there is a cell with data in in and then move down to the cell with data in it to copy the procedure.

When recording the macro I selected the range to copy to by using the shift+end+ the down arrow key, then went back up a cell. When I used the macro to copy the next entry down the macro only copied the same number of lines, and did not fill in all the blank cells.

How do I get it to fill all the blank cells?

RE: Macro recording

Delia

Whenever I get a problem with a Marco that does not work out as I want it to, the first thing I think about is breaking the result down into a step by step process, and then going through it manually, as if I was going to be recording the macro.

From what you have described, Try the following:



Before you start recording the Macro, select ONE cell that contains the data you want to copy. (You can only select multiple cells if the destination is exactly the same number of cells or a multiple of the selected ones). Selecting one keeps it simple for the moment.

1. Start recording the maco. Ensure you click on the RELATIVE REFERENCING button next to the STOP button on the Macro toolbar. (This ensures that Excel always selects the cell you choose prior to running the macro, NOT an absolute cell - ie. not $A$4).

2. COPY the Cell using CTRL+C

3. Navigate to the empty cell using the keyboard.
- (END+DOWN, then DOWN key again).

4. Navigate to the empty cell using the keyboard.
- (SHIFT+END+DOWN key, then holding SHIFT, the UP key ONCE).

5. Paste the copied cell using CTRL+V

6. Click once on the ORIGINAL cell you selected and copied. (This will loop the macro, so when you run it again, excel will start at step 2, and run the macro again.

DONE.


Let me know how you do. It may take some testing. You have to think for and like Excel to get it right. Adding step 6 in makes the whole thing work very fast. Once you have the correct result in the macro, you can just hold down the shortcut key and Excel will do all your work for you.

Richard


 

Excel tip:

Make a quick copy of a worksheet

Hold down the Ctrl key, then click and drag on a sheet tab to make a copy of that sheet. Though this process usefully copies the formats of the original sheet, note that any Range Names you have on the original sheet will be duplicated too.

To make a copy of a worksheet's contents and formats without duplicating range names: (1) Ensure that you have a blank worksheet to paste to. (2) On the sheet to copy, click on the sheet selection square to the left of Column A's heading to select the whole sheet. (2) Copy the whole sheet. (3) Paste to the blank worksheet.



View all Excel hints and tips


Server loaded in 0.07 secs.