Need Macro help with Copy and paste off of active cells

[Zoso4]

Hi,

I have a macro in one of my excel files that basically selects a list of cells from another page in the workbook and pastes those into the active cell. (it is a list of locations and nominal values) for example I would run this macro and paste it into say cell H30 tand it would fill in down to H90. then in A30-G30 I add in the date, and other disctribing factors to what is being measured and input and copy and paste that down by hand.

is there anyway to run a macro on the tail end of my current macro to move over 6 cells to the left, highlight the descriptive data, then paste it down with the rest of the nominals?

the descriptive data is different and the locations change as I add data to the spread sheet. I feel like this is possible but I do not have the from scratch macro skills to get it down. Help is greatly appreciated =]

 

[Teeroy]

Put in the date and descriptive data first before you run the macro, then at the beginning of your macro insert the line:

Dim rDescript As Range, lLastRow As Long
Set rDescript = ActiveCell.Offset(0, -7).Resize(1, 7)

at the end of the macro add:

lLastRow = Range("H" & Rows.Count).End(xlUp).Row
rDescript.Resize(lLastRow + 1 - rDescript.Row, 7) = rDescript.Value

I've based the code on your request to use the activecell but for your general info it is not great practice to run a macro based on the activecell position. If you start the above with the activecell in other than Column H you'll get either unexpected results, or an application error.

See the second last line of code I put in above as to how you can find the end of a row of data to add to it.