Simple VBA editing.. need some insight!

Moving this over to the proper forum...

Alright, I'm poked about in VBA for excel, but I'm having trouble learning how to reference a particular column to reproduce

currently this is the original code:

Please Login or Register  to view this content.

What I need to do is force data from columns B, H, J on the current selected row to populate on the new row. The current code only copies the 2 existing formulas. How do you reference data from those columns on the selected row to copy down to the new row below? And lasty, how do I remove the prompt to ask how many rows to reproduce? I just want it to default to reproduce 1 single additional row.

thanks very much for any help!

And I appreciate your help, I'm excited to learn more about excel and using macros.

I have a spreadsheet I use for taking minutes and assigning tasks.

Frequent copy and insert row activities so I now have included a right-click context menu item to copy and insert a copy of the entire row where the right-click takes place.

In ThisWorkbook I have

Please Login or Register  to view this content.

In a regular module I have

Please Login or Register  to view this content.

First, I missed the copy a column and insert as a row.

Is this what you really want to accomplish? A column contains 65,536 cells and a row only contains 256 cells, at least in Excel up through 2003. This is not reasonable, at least to me.

Next, the code I show works for me, I have sent it to people in different companies with different versions of Excel and it works for them.

Did you place the first two routines into ThisWorkbook via the VBE Window? And you put the Third Routine into a normal module?

Let's try a little trouble shooting:

• Insert a new sheet into your workbook that you can delete when done.
• In cell B5 and B6 enter something different for each.
• Select Cell B6 as the active cell.
• Press the key combination <Alt><F8> to bring up the macro dialog
• Select Copy_Insert_Row
• Run the macro

Do you now have three cells with entries(B5, B6, & B7?) Cells B6 and B7 should have the same data.

I applogize, I just read the entire thread again.

I will modify my routine to insert a single new row, and copy the formula from the previous row cells in columns B, H, & J into the same columns in the new row.

This all depends on relative referencing in formulas and not ansolute referencing.

Also, formulas in the row pushed down will still reference the original row and do not change to reference the new row.

If that is what you want then this should work.

Please Login or Register  to view this content.

Thanks for wrapping your code

How exactly do you reference a column within a row? - that is my major hang up .. recording macros only grabs a specific cell, but not a column within the row... allowing it to be unique for each row.

These snippets may help

Please Login or Register  to view this content.

will return column number

Please Login or Register  to view this content.

Will look at row 3 and activecolumn number to return address.

Please Login or Register  to view this content.

is a range from Row1 & column to Row 5 and Column

This link may also help

http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx

VBA Noob

Check your maths. Col H is the 8th Column

VBA Noob

Originally Posted by Pyrex238

HA! I deleted my "D" column! the math was right, the alphabet wasn't!

Deleting Column D would simpply shift the columns to the left. Column D must have been hidden.

One simple way to double check was in VBA Noob's earlier response.

Another that I took was to write myself a simple macro and put it in PERSONAL.XLS.

Please Login or Register  to view this content.

I then put a macro button assigned to this on my Standard toolbar. I just set the image on the button to read R1C1, and clicking it will change the column headers back and forth between R1C1 notation and A1 Notation.

No counting involved, just observe what the changes are.

alright! got it! thank you so much everyone!

BUT! one last question... it leaves the last cell with a copy halo... how do you set the macro to deselect all cells, then select column 1 so you can begin data entry?



..edit:

nevermind! I figured it out with a little intuition:

Please Login or Register  to view this content.

but I still have the halo... how do you remove that? ... how do you code in a keystroke? IE: Keystroke.esc - should effectively remove that halo

Think you need

Please Login or Register  to view this content.

VBA Noob

Not really. Took me a while to figure out what the halo was .

Quick tip. Use the recorder to copy the cell then double click the cell....

VBA Noob