Referring to Previous Worksheet - coding

[Denis]

Hello,

I want to use code to programatically copy cells "A3"
and "D7" from WorksheetA to cells "B12" and "D4"
respectively in WorksheetB.

The catch is that WorksheetA will be reproduced regularly,
while "B12" and "D4" on WorksheetB will be dependent onthe
selected, reproduced WorksheetA.

My biggest difficulty (I think) is to refer back from
WorksheetB to the previous sheet from which the 2nd 'copy'
operation took place.

Any assistance would be greatly appreciated.

Thank you Denis

 

[Frank Kabel]

Hi
ee:
http://www.dicks-blog.com/archives/2004/12/02/referring-to-previous-she
et/trackback/

 

[Denis]

Thank you Frank, although the worksheets, from where the
code will originate from, may not necessarily be adjacent
to the WorksheetB, you have given me something to start
with.
Thanks again

Denis

 

[Gord Dibben]

Denis

Copy/paste this User Defined Function to a general module in your workbook.

Function PrevSheet(rg As Range)
N = Application.Caller.Parent.Index
If N = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(N - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(N - 1).Range(rg.Address).Value
End If
End Function

'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1.

OR call the Function in your code.

Gord Dibben Excel MVP

 

[Denis]

Thank you Gord, unfortunately I tried to figure it out
and modify it somewhat, but admittedly, it was a little
above my head.

I have managed to come up with the following code. Not
very pretty nor sleek, but it's doing what I was looking
for.

Cheers!

Sub CopyToSecond()
'
' CopyToSecond Macro
' Macro recorded 12/9/2004 by Denis Bisson
'
If ActiveSheet.Name = "Second" Then ' if this sheet is
named 'Second' then exit the subroutine
Exit Sub
Else
'
Range("A3").Select 'select and paste the first cell
(cell to be determined)
Selection.Copy
Range("C19").Select 'cell number may need to be
changed - NOTE lock cell!
ActiveSheet.Paste
Application.CutCopyMode = False

Range("C3").Select 'select and paste the second cell
(cell to be determined)
Selection.Copy
Range("D19").Select 'cell number may need to be
changed - NOTE lock cell!
ActiveSheet.Paste
Application.CutCopyMode = False

' The value of the two copied cells are now pasted in
adjacent cells.
' Now, both cells will be copied; any
lines/formatting will be removed;
' the font will be changed to white (we don't want to
see the text);and
' both cells will be copied


Range("C1919").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle =
xlNone
Selection.Font.ColorIndex = 2 'White
Selection.Copy

' Now the 'Second' sheet is selected; two adjacent
cells are selected; and
' the values (as well as any formatting) from the
previous sheet are pasted
Sheets("Second").Select
Range("C1919").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select 'set the focus to cell A1
End If
End Sub

 

[Gord Dibben]

Denis

Your code can be cleaned up somewhat.......very rarely do you need to "select"
objects such as ranges and sheets.

Sub CopyToSecond()

If ActiveSheet.Name = "Second" Then
Exit Sub
Else
Range("C19").Value = Range("A3").Value
Range("D19").Value = Range("C3").Value
With Range("C1919")
.ClearFormats
.Font.ColorIndex = 2 'White
.Copy Destination:=Sheets("Second").Range("C19")
End With
Range("A1").Select 'set the focus to cell A1

End If

End Sub

Your comments on "cell to be determined" lead me to believe that you would be
having some sort of user input.

Check out the use of InputBox and Application.InputBox for choosing cells.

Gord

 

[Denis]

Gord,
Yes indeed, there was much potential for cleaner code
(quite new at this) and I appreciate your help in this
matter. I learned something new today - life is good!

Oh, the user input thing.... should have been removed from
my code for the posting... it was simply a reminder for
me - the cells will be different.

Cheers!
Denis

 

[Gord Dibben]

Thanks for the feedback Denis.

Gord