Copying and pasting with macros
 

Hi,
I have a workbook that I created for a hospital setting that has one
sheet for each hallway in the facility. Each hallway has a block of
information that is dedicated to a single room with 16 rooms per
hallway. So sheet1 shows rooms 1 to 16.

Each block has information like Name, Diagnosis, Ambulation status, etc
with an adjacent blank cell for that information to be added. The
blocks have quite a bit of information and I have set it up so that the
person entering this data can click on a button to the right of each
block to clear the added information while keeping all of the static
titles (Name, Diagnosis, etc..). The way I did this was to record a
macro where I selected all of the cells that I wanted to clear, deleted
the information and then assigned that macro to a button.

This method has worked fine for clearing the blocks but the issue I am
having is that I need a way to move information from one block to
another - as in when a resident switches rooms. I have the sheets
locked so that certain information cannot be changed - the above titles
as well as a few cells that contain dates which link from a seperate
sheet.

What I am wondering is if it is possible to set up a similar macro that
would copy the same select cells when you click on a 'Copy' button and
then paste that information onto a different block when you click that
blocks 'Paste' button. The same record macro technique I used to clear
each block doesn't work for the copy command.

Thanks for any advice/help. It may be that the way I have set this up
limits my ability to do this.

Matt

Copying and pasting with macros
 

There are so many variables that it would be hard to give a really good
answer. The best advice I can give is to first unprotect the sheet, next
record a macro and do all of the copy and pasting etc, then stop the macro.
This will put all of the code in a seperate module that you can look at and
adapt the selection ranges and paste ranges to be dynamic. Post any
additional questions on getting that done and once it is then, if necessary,
you can learn to protect and unprotect programatically, its not too
difficult.

--
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"mCassidy" wrote in message
ps.com...
Hi,
I have a workbook that I created for a hospital setting that has one
sheet for each hallway in the facility. Each hallway has a block of
information that is dedicated to a single room with 16 rooms per
hallway. So sheet1 shows rooms 1 to 16.

Each block has information like Name, Diagnosis, Ambulation status, etc
with an adjacent blank cell for that information to be added. The
blocks have quite a bit of information and I have set it up so that the
person entering this data can click on a button to the right of each
block to clear the added information while keeping all of the static
titles (Name, Diagnosis, etc..). The way I did this was to record a
macro where I selected all of the cells that I wanted to clear, deleted
the information and then assigned that macro to a button.

This method has worked fine for clearing the blocks but the issue I am
having is that I need a way to move information from one block to
another - as in when a resident switches rooms. I have the sheets
locked so that certain information cannot be changed - the above titles
as well as a few cells that contain dates which link from a seperate
sheet.

What I am wondering is if it is possible to set up a similar macro that
would copy the same select cells when you click on a 'Copy' button and
then paste that information onto a different block when you click that
blocks 'Paste' button. The same record macro technique I used to clear
each block doesn't work for the copy command.

Thanks for any advice/help. It may be that the way I have set this up
limits my ability to do this.

Matt

Copying and pasting with macros
 

Hi Matt:

This is an example of copy/paste with a macro:

Sub matt()
Dim r1 As Range, r2 As Range
Set r1 = Range("A3:B11")
Set r2 = Range("E12")
r1.Copy r2
End Sub


This copies the block of cells from A3 thru B11 to E12 thru F20
--
Gary's Student


"mCassidy" wrote:

Hi,
I have a workbook that I created for a hospital setting that has one
sheet for each hallway in the facility. Each hallway has a block of
information that is dedicated to a single room with 16 rooms per
hallway. So sheet1 shows rooms 1 to 16.

Each block has information like Name, Diagnosis, Ambulation status, etc
with an adjacent blank cell for that information to be added. The
blocks have quite a bit of information and I have set it up so that the
person entering this data can click on a button to the right of each
block to clear the added information while keeping all of the static
titles (Name, Diagnosis, etc..). The way I did this was to record a
macro where I selected all of the cells that I wanted to clear, deleted
the information and then assigned that macro to a button.

This method has worked fine for clearing the blocks but the issue I am
having is that I need a way to move information from one block to
another - as in when a resident switches rooms. I have the sheets
locked so that certain information cannot be changed - the above titles
as well as a few cells that contain dates which link from a seperate
sheet.

What I am wondering is if it is possible to set up a similar macro that
would copy the same select cells when you click on a 'Copy' button and
then paste that information onto a different block when you click that
blocks 'Paste' button. The same record macro technique I used to clear
each block doesn't work for the copy command.

Thanks for any advice/help. It may be that the way I have set this up
limits my ability to do this.

Matt