1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: VBA assistance for referencing previous worksheet

Discussion in 'Business Applications' started by awedge, Jan 11, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. awedge

    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???
     
  2. Rollin_Again

    Rollin_Again

    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
     
  3. awedge

    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
     
  4. awedge

    awedge Thread Starter

    Joined:
    Jan 11, 2011
    Messages:
    3
    I got it!!!!
    Thank you for your help!!!!
     
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 733,556 other people just like you!

Loading...
Similar Threads - Solved assistance referencing
  1. Pedro14
    Replies:
    0
    Views:
    354
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/974137

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice