Solved: VBA assistance for referencing previous worksheet

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

awedge

Thread Starter
Joined
Jan 11, 2011
Messages
3
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+t
'
ActiveSheet.Select
Application.CutCopyMode = False
ActiveSheet.Copy Before:=Sheets("New Miner")
Range("B31").Select
ActiveCell.Formula = "=Prev(B31)+5"
Range("A19:E30").Select
Selection.ClearContents
Range("A19").Select
End Sub


So this is my macro (which I have very little experience with). Basically, it copies the current sheet, places it before a sheet called "New Miner", in cell B31 is where I want it to sum B17:B30 and the previous worksheet B31, and then it selects A19:E30 and clears the contents, and ends on A19. Can anyone please help???
 
Joined
Sep 4, 2003
Messages
4,912
Just set a variable equal to the current sheet name before the copy and then reference it in your code like I've done below.

Code:
vName = ActiveSheet.Name
ActiveSheet.Copy Before:=Sheets("New Miner")
Sheets(vName).Select
Sheets(vName).Range("B31").Formula = "=Prev(B31)+5"
Sheets(vName).Range("A19:E30").ClearContents
Sheets(vName).Range("A19").Select
Rollin
 

awedge

Thread Starter
Joined
Jan 11, 2011
Messages
3
Rollin - Thank you for the response. I am struggling with the formula line that is trying to add the previous 14 cells in the new sheet plus cell B31 from the previous sheet. Any ideas???

Sub Macro1()
'
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+t
'
ActiveSheet.Select
Application.CutCopyMode = False
ActiveSheet.Copy Before:=Sheets("New Miner")
Range("B31").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-14]C:R[-1]C) +'"PreviousSheet.Name"'!RC"
Range("A19:E30").Select
Selection.ClearContents
Range("A19").Select
End Sub
Range("A19:E30").Select
Selection.ClearContents
Range("A19").Select
End Sub
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top