Copying

  • Thread starter Thread starter wayne
  • Start date Start date
W

wayne

Hi,
I would like to set up 2 workbooks, available & allocated. How do I
choose one cell from 'avaikable' and cut & paste it to its
corresponding cell address in 'allocated'. Just one click would be
nice!

yours amatuerishsly

Wayne
TIA
 
From the destination book copying from source wb with same sheet and range
Sub copysht()
ms = ActiveSheet.Name
mr = ActiveCell.Address
Workbooks("source.xls").Sheets(ms).Range(mr).Copy ActiveCell
End Sub
 
How about 2 clicks? One click will select the cell you want to move. The
other click would be on a button and would move the contents of that cell.
Or the second click could be a key combination if you wish.
Or, if the cells in question can all be defined as being in some range, and
you don't normally select any of these cells for any purpose other than to
move their contents, it can be done with one click.
Post back with some more details. HTH Otto
 
On Wed, 23 Jul 2008 17:33:24 -0400, "Otto Moehrbach"

I can live with 2 clicks"!. What I need to do is to move the contents
of a cell backwards and forwards between the 2 workbooks. The contents
will be time slots which are exclusive and need to be so as once
allocated, they cannot be used (seen?) until 'released' from the
allocated workbook.
Thank you very much for your prompt reply.

Wayne
 
On Wed, 23 Jul 2008 16:28:02 -0500, "Don Guillett"

Thank you very much, I will do my best to try this

Wayne
 
I don't know what you just said. Are you saying that you have a range in
each of 2 workbooks, and if the user selects a cell in that range of either
workbook, and clicks on the button, you want the contents of that cell moved
(cut and paste) to the other workbook in the same cell address in the same
sheet name it came from? I don't understand what you mean by "seen". HTH
Otto
 
On Thu, 24 Jul 2008 17:34:37 -0400, "Otto Moehrbach"

That is pretty much it Otto.
Sorry to cause you confusion. When a cell (a period of time) from a
range in 'Avvailable' is chosen, it has to move to an equivalent range
in 'Allocated' and should not be available again until it is clicked
on in the 'Allocated' workbook, which then moves it back to
'Available'
The 'seen' thing was just so anyone looking at the workbooks would
know which times were available and which had been allocated.
 
Wayne
Here are 2 macros. As you can see, the 2 macros are identical except
for the macro name and the "Const...." line. As written, you must have 2
files named Allocated.xls and Available.xls. Both files MUST be open or an
error will result. The MoveToAvailable macro goes in the "Allocated.xls"
file and the other one goes in the "Available.xls" file.
As written, the destination file must have a sheet named the same as the
source sheet in the source file. Place a button in each file and assign the
corresponding macros to those buttons. The macros move the contents of the
active cell. Post back if you need more. Otto
Sub MoveToAvailable()
Const OtherWBName As String = "Available.xls"
Dim ws As Worksheet
Dim TheCell As Range
Dim OtherWB As Workbook
Set ws = ActiveSheet
Set TheCell = ActiveCell
Set OtherWB = Workbooks(OtherWBName)
With OtherWB.Sheets(ws.Name)
TheCell.Cut .Range(TheCell.Address)
End With
End Sub

Sub MoveToAllocated()
Const OtherWBName As String = "Allocated.xls"
Dim ws As Worksheet
Dim TheCell As Range
Dim OtherWB As Workbook
Set ws = ActiveSheet
Set TheCell = ActiveCell
Set OtherWB = Workbooks(OtherWBName)
With OtherWB.Sheets(ws.Name)
TheCell.Cut .Range(TheCell.Address)
End With
End Sub
 
Back
Top