Hello, Folks
I have a problem that I simply can't work out for myself. I need some
ideas, please.
On page 1, I need to copy the address of a cell to a variable. I can do
this and have it show up in a MsgBox. I need to perform other calculations,
cut, paste, etc..,
on the same sheet as well as on another sheet.
At some point, I need to return to sheet 1 and call the variable to make it
the active cell again.
Any help would be much appreciated. Thanks
Randy

You are best off to not move the active cell around at all. You can do
everything that you need with range objects. Post your code and let us take a
look at it. Essentially we can help you remove the .selects and to declare
and manipulate range objects to do what you need. It will make the code
shorter and faster, with more options.

--
HTH...

Jim Thomlinson


"RAP" wrote:

Hello, Folks
I have a problem that I simply can't work out for myself. I need some
ideas, please.
On page 1, I need to copy the address of a cell to a variable. I can do
this and have it show up in a MsgBox. I need to perform other calculations,
cut, paste, etc..,
on the same sheet as well as on another sheet.
At some point, I need to return to sheet 1 and call the variable to make it
the active cell again.
Any help would be much appreciated. Thanks
Randy

Hello, Jim
Thanks for the post. I will past the code, although I feel slightly
embarrassed to do so. I have to say that this is my first VB experience, no,
make that first programming experience I've ever attempted. I'm really doing
nothing more than using code to cursor around in the file, attempting to
replicate manual commands. But, school is scheduled, and until I start my
"official" training, I have to reply on reference manuals, Excel help, and
this discussion group. OK, I've babbled on too long. Here's the code.
Thanks for any assistance, Jim.

I'm taking comments from DataInput page and copying them to the Comments
page, then finding the equipment and date for which the comment was made.
This is most often NOT the current date (no NOW or TODAY commands) Anyway,
it's long because it's not efficient. Thanks again.

Sub CopyCommentsInfo()

' Select DataInput page
Worksheets("DataInput").Select
Dim BlankCell As Integer
Dim Comment As Integer
Dim X As Range
Dim LastCommentCell As Range
Set LastCommentCell = Range("$H$56") ' REPLICATION POINT: = TO LAST
COMMENT CELL ADDRESS

'Go to the first cell in the comments column
Range("$H$2").Select
' Check to see if it is empty. If true, then increment by 1 and loop. If
false, end loop

Line1:
If ActiveCell.Value = "" Then
MsgBox ActiveCell.Address ' Temporary check point
If ActiveCell.Address <= LastCommentCell.Address Then '
Check to see if it's the last comment cell
ActiveCell.Offset(1, 0).Select
MsgBox ActiveCell.Address ' Temporary check point
GoTo Line1
Else:
GoTo LastLine
End If
Else:
GoTo Line2
End If
Line2:
' Set X variable as current activecell in comments column (to return to
later)
Set X = Range(ActiveCell.Address)
MsgBox X.Address & "= X Address"
MsgBox ActiveCell.Address & "= ActiveCell Address" ' TEMPORARY (IT
WORKS!)
' copies comment
ActiveCell.Copy
' Select Comments page
Worksheets("Comments").Select
' Find first empty cell on Comments page in the comments column, column
5 (E)
BlankCell = Application.WorksheetFunction.CountA(Worksheets("C omments") _
.Range("E:E")) + 1
'select the next blank cell in column 5 (E)
Worksheets("Comments").Cells(BlankCell, 5).Select
Selection.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
' Go back to DataInput page and get equipment listed on comment row
' Select DataInput page
Worksheets("DataInput").Select
' Move cursor left 7 columns to Equipment column and select cell (column
A)
ActiveCell.Offset(0, -7).Select
' MsgBox ActiveCell.Value 'Display what value program is picking up
' Copy ActiveCell text ' TEMPORARY
ActiveCell.Copy

' Select Comments page
Worksheets("Comments").Select
' From activeCell, move cursor left 1 column to Equipment column and
select cell
ActiveCell.Offset(0, -1).Select
'Paste
Selection.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

' COPY & PASTE User-defined ENTRYDATE (Not NOW or TODAY command) ONTO
COMMENT SHEET (Column C)
' Move active cell one cell to the left of equipment column
ActiveCell.Offset(0, -1).Select
' Get EntryDate value, copy & paste to column on comments page
Range("EntryDate").Copy
'Paste
Selection.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

THIS IS WHERE I'M HAVING PROBLEMS. I NEED TO GO BACK TO THE CELL I WAS
PREVIOUSLY IN WHILE IN THE COMMENTS COLUMN. I'M TRYING TO GO BACK TO THE
LAST ACTIVE CELL (I COPIED EARLIER) THEN OFFSET BACK TO COMMENTS COLUMN.
IT'S PASTING THE VALUE OF THE ACTIVECELL, NOT THE ADDRESS.

Worksheets("DataInput").Select
' Set activecell back to X
MsgBox ActiveCell.Address
' Range("X").Select
ActiveCell = "X"
' offset activecell down one row
ActiveCell.Offset(1, 0).Select

GoTo Line1

LastLine:

' Return to Save&Repeat macro
End Sub

--------------------------------------------------------



"Jim Thomlinson" wrote:

You are best off to not move the active cell around at all. You can do
everything that you need with range objects. Post your code and let us take a
look at it. Essentially we can help you remove the .selects and to declare
and manipulate range objects to do what you need. It will make the code
shorter and faster, with more options.

--
HTH...

Jim Thomlinson


"RAP" wrote:

Hello, Folks
I have a problem that I simply can't work out for myself. I need some
ideas, please.
On page 1, I need to copy the address of a cell to a variable. I can do
this and have it show up in a MsgBox. I need to perform other calculations,
cut, paste, etc..,
on the same sheet as well as on another sheet.
At some point, I need to return to sheet 1 and call the variable to make it
the active cell again.
Any help would be much appreciated. Thanks
Randy

RAP,

Here is a shot. First I removed all the unnecessary selecting and Goto's,
introducing referencing and loops.

But I also saw that it was just a block copy, so I also include a second,
even trimmer version.

I haven't been able to test it properly as I have no idea of the data, and
dissecting the code was not that simple (sorry, but it was a bit of a mess
with all the Goto's et al), so you will need to test it and post back any
problems.

Version 1

Sub CopyCommentsInfo()
Dim X As Range
Dim iLastComment As Long
Dim i As Long
Dim iLastRow As Long
Dim shComments As Worksheet
Dim iComments As Long

' Select DataInput page
Worksheets("DataInput").Select

iLastComment = 56 ' REPLICATION POINT: = TO LAST
' COMMENT CELL ADDRESS
iLastRow = Range("$H$56").End(xlDown).Row

Set shComments = Worksheets("Comments")
iComments = shComments.Range("E1").End(xlDown).Row

If iLastRow iLastComment Then
For i = LastCommentCell + 1 To iLastRow
Set X = Range("H" & i)
MsgBox X.Address & "= X Address"
X.Copy shComments.Cells(iComments, "E")
X.Offset(0, -7).Copy shComments.Cells(iComments, "D")
Range("EntryDate").Copy shComments.Cells(iComments, "C")
X.Value = "X"
iComments = iComments + 1
Next i
End If

Set shComments = Nothing

End Sub


Version 2

Sub CopyCommentsInfo()
Sub CopyCommentsInfo()
Dim iLastRow As Long
Dim shComments As Worksheet
Dim iComments As Long
Dim cRows As Long

' Select DataInput page
Worksheets("DataInput").Select

iLastRow = Range("$H$56").End(xlDown).Row
cRows = iLastRow - 56

Set shComments = Worksheets("Comments")
iComments = shComments.Range("E1").End(xlDown).Row

If cRows 0 Then
Range("A" & iLastComment + 1).Resize(cRows).Copy _
Destination:=shComments.Cells(iComments, "C")
Range("EntryDate").Copy _
Destination:=shComments.Cells(iComments, "D").Resize(cRows)
Range("H" & iLastComment + 1).Resize(cRows).Copy _
Destination:=shComments.Cells(iComments, "E")
End If

Set shComments = Nothing

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"RAP" wrote in message
...
Hello, Jim
Thanks for the post. I will past the code, although I feel slightly
embarrassed to do so. I have to say that this is my first VB experience,
no,
make that first programming experience I've ever attempted. I'm really
doing
nothing more than using code to cursor around in the file, attempting to
replicate manual commands. But, school is scheduled, and until I start my
"official" training, I have to reply on reference manuals, Excel help, and
this discussion group. OK, I've babbled on too long. Here's the code.
Thanks for any assistance, Jim.

I'm taking comments from DataInput page and copying them to the Comments
page, then finding the equipment and date for which the comment was made.
This is most often NOT the current date (no NOW or TODAY commands)
Anyway,
it's long because it's not efficient. Thanks again.

Sub CopyCommentsInfo()

' Select DataInput page
Worksheets("DataInput").Select
Dim BlankCell As Integer
Dim Comment As Integer
Dim X As Range
Dim LastCommentCell As Range
Set LastCommentCell = Range("$H$56") ' REPLICATION POINT: = TO LAST
COMMENT CELL ADDRESS

'Go to the first cell in the comments column
Range("$H$2").Select
' Check to see if it is empty. If true, then increment by 1 and loop. If
false, end loop

Line1:
If ActiveCell.Value = "" Then
MsgBox ActiveCell.Address ' Temporary check point
If ActiveCell.Address <= LastCommentCell.Address Then '
Check to see if it's the last comment cell
ActiveCell.Offset(1, 0).Select
MsgBox ActiveCell.Address ' Temporary check point
GoTo Line1
Else:
GoTo LastLine
End If
Else:
GoTo Line2
End If
Line2:
' Set X variable as current activecell in comments column (to return
to
later)
Set X = Range(ActiveCell.Address)
MsgBox X.Address & "= X Address"
MsgBox ActiveCell.Address & "= ActiveCell Address" ' TEMPORARY (IT
WORKS!)
' copies comment
ActiveCell.Copy
' Select Comments page
Worksheets("Comments").Select
' Find first empty cell on Comments page in the comments column,
column
5 (E)
BlankCell =
Application.WorksheetFunction.CountA(Worksheets("C omments") _
.Range("E:E")) + 1
'select the next blank cell in column 5 (E)
Worksheets("Comments").Cells(BlankCell, 5).Select
Selection.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
' Go back to DataInput page and get equipment listed on comment row
' Select DataInput page
Worksheets("DataInput").Select
' Move cursor left 7 columns to Equipment column and select cell
(column
A)
ActiveCell.Offset(0, -7).Select
' MsgBox ActiveCell.Value 'Display what value program is picking up
' Copy ActiveCell text ' TEMPORARY
ActiveCell.Copy

' Select Comments page
Worksheets("Comments").Select
' From activeCell, move cursor left 1 column to Equipment column and
select cell
ActiveCell.Offset(0, -1).Select
'Paste
Selection.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

' COPY & PASTE User-defined ENTRYDATE (Not NOW or TODAY command) ONTO
COMMENT SHEET (Column C)
' Move active cell one cell to the left of equipment column
ActiveCell.Offset(0, -1).Select
' Get EntryDate value, copy & paste to column on comments page
Range("EntryDate").Copy
'Paste
Selection.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

THIS IS WHERE I'M HAVING PROBLEMS. I NEED TO GO BACK TO THE CELL I
WAS
PREVIOUSLY IN WHILE IN THE COMMENTS COLUMN. I'M TRYING TO GO BACK TO THE
LAST ACTIVE CELL (I COPIED EARLIER) THEN OFFSET BACK TO COMMENTS COLUMN.
IT'S PASTING THE VALUE OF THE ACTIVECELL, NOT THE ADDRESS.

Worksheets("DataInput").Select
' Set activecell back to X
MsgBox ActiveCell.Address
' Range("X").Select
ActiveCell = "X"
' offset activecell down one row
ActiveCell.Offset(1, 0).Select

GoTo Line1

LastLine:

' Return to Save&Repeat macro
End Sub

--------------------------------------------------------



"Jim Thomlinson" wrote:

You are best off to not move the active cell around at all. You can do
everything that you need with range objects. Post your code and let us
take a
look at it. Essentially we can help you remove the .selects and to
declare
and manipulate range objects to do what you need. It will make the code
shorter and faster, with more options.

--
HTH...

Jim Thomlinson


"RAP" wrote:

Hello, Folks
I have a problem that I simply can't work out for myself. I need some
ideas, please.
On page 1, I need to copy the address of a cell to a variable. I can
do
this and have it show up in a MsgBox. I need to perform other
calculations,
cut, paste, etc..,
on the same sheet as well as on another sheet.
At some point, I need to return to sheet 1 and call the variable to
make it
the active cell again.
Any help would be much appreciated. Thanks
Randy

Hello, Bob
Thanks for both versions. I've been looking at them and have learned quite
a bit.
Comments on both versions for you. Version one pulls the correct
information, but the first paste results in the headers being pasted into the
correct cells on the Comments page... on the last row of the page. (I'm
looking for the data to be pasted in the first empty line) Also, the line:
X.Copy shComments.Cells(iComments, "E") is pasting an X in the header cell of
the DataInput page. The sheet was protected and gave me that error first.
When I unprotected the page to see what was trying to be written, that's what
appeared.
I don't need anything pasted into the DataInput page as a result of this
macro. I'm trying to copy information from a column of comments (that can
have blanks or no comments at all in the column) and paste them (along with
the corresponding equipment data and date info... which worked wonderfully)
into a "Comments" sheet in the next blank row.
This information was probably not available to you from my first post.

Version 2 produced: Run Time Error 1004 The copy and paste areas are not
the same size and shape. Thats as far as I got with V2.

Still trying to utilize the commands to make it work. If you have time for
any further suggestions, I will be happy to give them a go. Thanks again.
- Randy


"Bob Phillips" wrote:

RAP,

Here is a shot. First I removed all the unnecessary selecting and Goto's,
introducing referencing and loops.

But I also saw that it was just a block copy, so I also include a second,
even trimmer version.

I haven't been able to test it properly as I have no idea of the data, and
dissecting the code was not that simple (sorry, but it was a bit of a mess
with all the Goto's et al), so you will need to test it and post back any
problems.

Version 1

Sub CopyCommentsInfo()
Dim X As Range
Dim iLastComment As Long
Dim i As Long
Dim iLastRow As Long
Dim shComments As Worksheet
Dim iComments As Long

' Select DataInput page
Worksheets("DataInput").Select

iLastComment = 56 ' REPLICATION POINT: = TO LAST
' COMMENT CELL ADDRESS
iLastRow = Range("$H$56").End(xlDown).Row

Set shComments = Worksheets("Comments")
iComments = shComments.Range("E1").End(xlDown).Row

If iLastRow iLastComment Then
For i = LastCommentCell + 1 To iLastRow
Set X = Range("H" & i)
MsgBox X.Address & "= X Address"
X.Copy shComments.Cells(iComments, "E")
X.Offset(0, -7).Copy shComments.Cells(iComments, "D")
Range("EntryDate").Copy shComments.Cells(iComments, "C")
X.Value = "X"
iComments = iComments + 1
Next i
End If

Set shComments = Nothing

End Sub


Version 2

Sub CopyCommentsInfo()
Sub CopyCommentsInfo()
Dim iLastRow As Long
Dim shComments As Worksheet
Dim iComments As Long
Dim cRows As Long

' Select DataInput page
Worksheets("DataInput").Select

iLastRow = Range("$H$56").End(xlDown).Row
cRows = iLastRow - 56

Set shComments = Worksheets("Comments")
iComments = shComments.Range("E1").End(xlDown).Row

If cRows 0 Then
Range("A" & iLastComment + 1).Resize(cRows).Copy _
Destination:=shComments.Cells(iComments, "C")
Range("EntryDate").Copy _
Destination:=shComments.Cells(iComments, "D").Resize(cRows)
Range("H" & iLastComment + 1).Resize(cRows).Copy _
Destination:=shComments.Cells(iComments, "E")
End If

Set shComments = Nothing

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"RAP" wrote in message
...
Hello, Jim
Thanks for the post. I will past the code, although I feel slightly
embarrassed to do so. I have to say that this is my first VB experience,
no,
make that first programming experience I've ever attempted. I'm really
doing
nothing more than using code to cursor around in the file, attempting to
replicate manual commands. But, school is scheduled, and until I start my
"official" training, I have to reply on reference manuals, Excel help, and
this discussion group. OK, I've babbled on too long. Here's the code.
Thanks for any assistance, Jim.

I'm taking comments from DataInput page and copying them to the Comments
page, then finding the equipment and date for which the comment was made.
This is most often NOT the current date (no NOW or TODAY commands)
Anyway,
it's long because it's not efficient. Thanks again.

Sub CopyCommentsInfo()

' Select DataInput page
Worksheets("DataInput").Select
Dim BlankCell As Integer
Dim Comment As Integer
Dim X As Range
Dim LastCommentCell As Range
Set LastCommentCell = Range("$H$56") ' REPLICATION POINT: = TO LAST
COMMENT CELL ADDRESS

'Go to the first cell in the comments column
Range("$H$2").Select
' Check to see if it is empty. If true, then increment by 1 and loop. If
false, end loop

Line1:
If ActiveCell.Value = "" Then
MsgBox ActiveCell.Address ' Temporary check point
If ActiveCell.Address <= LastCommentCell.Address Then '
Check to see if it's the last comment cell
ActiveCell.Offset(1, 0).Select
MsgBox ActiveCell.Address ' Temporary check point
GoTo Line1
Else:
GoTo LastLine
End If
Else:
GoTo Line2
End If
Line2:
' Set X variable as current activecell in comments column (to return
to
later)
Set X = Range(ActiveCell.Address)
MsgBox X.Address & "= X Address"
MsgBox ActiveCell.Address & "= ActiveCell Address" ' TEMPORARY (IT
WORKS!)
' copies comment
ActiveCell.Copy
' Select Comments page
Worksheets("Comments").Select
' Find first empty cell on Comments page in the comments column,
column
5 (E)
BlankCell =
Application.WorksheetFunction.CountA(Worksheets("C omments") _
.Range("E:E")) + 1
'select the next blank cell in column 5 (E)
Worksheets("Comments").Cells(BlankCell, 5).Select
Selection.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
' Go back to DataInput page and get equipment listed on comment row
' Select DataInput page
Worksheets("DataInput").Select
' Move cursor left 7 columns to Equipment column and select cell
(column
A)
ActiveCell.Offset(0, -7).Select
' MsgBox ActiveCell.Value 'Display what value program is picking up
' Copy ActiveCell text ' TEMPORARY
ActiveCell.Copy

' Select Comments page
Worksheets("Comments").Select
' From activeCell, move cursor left 1 column to Equipment column and
select cell
ActiveCell.Offset(0, -1).Select
'Paste
Selection.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

' COPY & PASTE User-defined ENTRYDATE (Not NOW or TODAY command) ONTO
COMMENT SHEET (Column C)
' Move active cell one cell to the left of equipment column
ActiveCell.Offset(0, -1).Select
' Get EntryDate value, copy & paste to column on comments page
Range("EntryDate").Copy
'Paste
Selection.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

THIS IS WHERE I'M HAVING PROBLEMS. I NEED TO GO BACK TO THE CELL I
WAS
PREVIOUSLY IN WHILE IN THE COMMENTS COLUMN. I'M TRYING TO GO BACK TO THE
LAST ACTIVE CELL (I COPIED EARLIER) THEN OFFSET BACK TO COMMENTS COLUMN.
IT'S PASTING THE VALUE OF THE ACTIVECELL, NOT THE ADDRESS.

Worksheets("DataInput").Select
' Set activecell back to X
MsgBox ActiveCell.Address
' Range("X").Select
ActiveCell = "X"
' offset activecell down one row
ActiveCell.Offset(1, 0).Select

GoTo Line1

LastLine:

' Return to Save&Repeat macro
End Sub

--------------------------------------------------------



"Jim Thomlinson" wrote:

You are best off to not move the active cell around at all. You can do
everything that you need with range objects. Post your code and let us
take a
look at it. Essentially we can help you remove the .selects and to
declare
and manipulate range objects to do what you need. It will make the code
shorter and faster, with more options.

--
HTH...

Jim Thomlinson


"RAP" wrote:

Hello, Folks
I have a problem that I simply can't work out for myself. I need some
ideas, please.
On page 1, I need to copy the address of a cell to a variable. I can
do
this and have it show up in a MsgBox. I need to perform other
calculations,
cut, paste, etc..,
on the same sheet as well as on another sheet.
At some point, I need to return to sheet 1 and call the variable to
make it
the active cell again.
Any help would be much appreciated. Thanks
Randy