Hello everyone. I only discovered this forum a month ago and regret no
knowing of it sooner. What a fantastic resource it is and how I coul
have used it before I retired.

I've always been able to get the results I want by recording a macr
but this time it doesn't work the way I want it to. Here is what
want to achieve.

Start on Sheet 5 (or any Sheet from 2 to 10) and make a cell active b
clicking on it.

Now I want to click on a button to start a macro to do the following:
Go to a named range ("Field") on Sheet 1 and copy it (This par
recorded properly), now return to the original Sheet 5 (my macro di
this but because I want to put buttons for the same macro on othe
sheets I don't want it to specify Sheet 5). Once on Sheet 5 I want i
to paste the values only in the original active cell but again I don'
want to specify the cell by name because it will be different the nex
time I run the same macro. Next I want to move 3 cells to left o
active cell (without specifying cell address) and insert today's date.
Next I want to move 1 row up and 12 columns over, select 20 adjacen
cells (which contain formulae) and copy them to the row below them.
Finally I want to end up at the cell directly below the original activ
cell.

The basic problem I'm having is the moving to the various cells on th
Sheet without specifying their addresses because I need the macro t
drop down a row each time it is run.

Thanks for taking the time to read this and for any thoughts you ma
have on it

--
Message posted from http://www.ExcelForum.com

Hi

I don't quite follow you all the way, but here are techniques that I hope
will get you started:

Sub test()
Dim R As Range
Set R = Selection(1)
R.Value = Sheets(1).Range("Field").Value
R.Offset(0, -3).Value = Now
R.Offset(-1, 12).Resize(4, 5).Select
End Sub

HTH. Best wishes Harald


"Cutter " skrev i melding
...
Hello everyone. I only discovered this forum a month ago and regret not
knowing of it sooner. What a fantastic resource it is and how I could
have used it before I retired.

I've always been able to get the results I want by recording a macro
but this time it doesn't work the way I want it to. Here is what I
want to achieve.

Start on Sheet 5 (or any Sheet from 2 to 10) and make a cell active by
clicking on it.

Now I want to click on a button to start a macro to do the following:
Go to a named range ("Field") on Sheet 1 and copy it (This part
recorded properly), now return to the original Sheet 5 (my macro did
this but because I want to put buttons for the same macro on other
sheets I don't want it to specify Sheet 5). Once on Sheet 5 I want it
to paste the values only in the original active cell but again I don't
want to specify the cell by name because it will be different the next
time I run the same macro. Next I want to move 3 cells to left of
active cell (without specifying cell address) and insert today's date.
Next I want to move 1 row up and 12 columns over, select 20 adjacent
cells (which contain formulae) and copy them to the row below them.
Finally I want to end up at the cell directly below the original active
cell.

The basic problem I'm having is the moving to the various cells on the
Sheet without specifying their addresses because I need the macro to
drop down a row each time it is run.

Thanks for taking the time to read this and for any thoughts you may
have on it.


---
Message posted from http://www.ExcelForum.com/

Thank you very much for your reply Harald

It has helped me to get started but it's not doing what I want. You
line:
R.Offset(-1, 12).Resize(4, 5).Select

I have adjusted it to read:
R.Offset(-1, 12).Resize(1, 20).Select

Now I want to copy the contents of those cells and paste them one ro
down. I tried this:
Selection.Copy
R.Offset(0, 20).Select

But everything I try for the next line to get it to paste the content
creates an error.

A little bit more help and I think I've got it

--
Message posted from http://www.ExcelForum.com

Harald, thank you very much for your reply. It has helped me to almos
get it solved. Here is what I have so far:

Sub test()
Dim R As Range
Set R = Selection(1)
R.Value = Sheets(1).Range("Field").Value
R.Offset(0, -10).Value = Now
R.Offset(-1, 20).Resize(1, 24).Select
Selection.Copy
R.Offset(0, 20).Select
ActiveSheet.Paste
R.Offset(1, 0).Select
End Sub


The only problem is that "Field" is a range of cells 1R by 18C so I en
up with the value of the left-most cell of "Field" in my starting activ
cell but the 17 cells to its right are empty. Everything else in th
macro works great. I'm almost there

--
Message posted from http://www.ExcelForum.com

If I understand you correctly:

Sub test()
Dim R As Range
Set R = Selection(1)
R.Value = Sheets(1).Range("Field").Value
R.Offset(0, -10).Value = Now
R.Offset(-1, 20).Resize(1, 24).Copy _
R.Offset(0, 20).Resize(1, 24)
End Sub

HTH. Best wishes Harald

"Cutter " skrev i melding
...
Harald, thank you very much for your reply. It has helped me to almost
get it solved. Here is what I have so far:

Sub test()
Dim R As Range
Set R = Selection(1)
R.Value = Sheets(1).Range("Field").Value
R.Offset(0, -10).Value = Now
R.Offset(-1, 20).Resize(1, 24).Select
Selection.Copy
R.Offset(0, 20).Select
ActiveSheet.Paste
R.Offset(1, 0).Select
End Sub


The only problem is that "Field" is a range of cells 1R by 18C so I end
up with the value of the left-most cell of "Field" in my starting active
cell but the 17 cells to its right are empty. Everything else in the
macro works great. I'm almost there.


---
Message posted from http://www.ExcelForum.com/

Thanks again for your reply Harald. The macro as I posted works fin
except for the line:

R.Value = Sheets(1).Range("Field").Value

This line puts the value of the first cell of "Field" in my activ
cell. But "Field" is a row of 18 adjacent cells. What I want is a wa
to paste the values of the 18 cells in "Field" to my active sheet s
that my active sheet has a row of 18 adjacent cells with each cel
showing the value of the corresponding 18 cells from the sheet wher
"Field" exists.

I want the macro to duplicate the manual method of:
Starting from active cell on active sheet - click on "Field" in th
name box - click on copy - return to the active cell on the startin
sheet - paste special (values).

As I said, the rest of the macro does the rest of the steps I need an
I thank you very much for your help with that

--
Message posted from http://www.ExcelForum.com

Hi

You should be able to do this with the Resize medthod already provided (it's
not obvious, no critisism intended):

Set R = Selection(1)
R.Resize(Sheets(1).Range("Field").Rows.Count, _
Sheets(1).Range("Field").Columns.Count).Value = _
Sheets(1).Range("Field").Value

HTH. Best wishes Harald

"Cutter " skrev i melding
...
Thanks again for your reply Harald. The macro as I posted works fine
except for the line:

R.Value = Sheets(1).Range("Field").Value

This line puts the value of the first cell of "Field" in my active
cell. But "Field" is a row of 18 adjacent cells. What I want is a way
to paste the values of the 18 cells in "Field" to my active sheet so
that my active sheet has a row of 18 adjacent cells with each cell
showing the value of the corresponding 18 cells from the sheet where
"Field" exists.

Thanks for the help Harald. Like I said in my original post I've onl
ever used the recorder to create macros so nothing about VBA is obviou
to me. I've just started to try my hand at it without the recorde
because of the recorder's limitations. I'll try to figure out wha
you've given me

--
Message posted from http://www.ExcelForum.com