Results 1 to 4 of 4

Dynamic Sheet Name in VBA

Threaded View

  1. #2
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2501
    Posts
    1,448

    Re: Dynamic Sheet Name in VBA

    Maybe:
    Sub test()
        Dim wsHome As Worksheet
        Dim wsR As Worksheet
        Dim R As String
    
        'this is the reference to the sheet name. Currently ("Summary 1") in the workbook
        Set wsHome = Sheets("Home")
        R = wsHome.Range("H1").Value
        Set wsR = Sheets(R)
           
        If ActiveSheet.Name = "Home" Then
            'this is where it starts to have issues. I am trying to have "R" be the sheet name referenced from cell H1 above. This should essentially read as Sheets("Summary 1").Select
            wsR.Range("C7:J42").ClearContents
            wsHome.Range(Range("G8:J8"), wsHome.Range("G8:J8").End(xlDown)).Copy
            wsR.Range("C7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            wsHome.Range(wsHome.Range("M8"), wsHome.Range("M8").End(xlDown)).Copy
        End If
    End Sub
    Or even:
    Sub test()
        Dim wsHome As Worksheet
        Dim wsR As Worksheet
        Dim R As String
    
        'this is the reference to the sheet name. Currently ("Summary 1") in the workbook
        Set wsHome = Sheets("Home")
        R = wsHome.Range("H1").Value
        Set wsR = Sheets(R)
           
        If ActiveSheet.Name = "Home" Then
            wsR.Range("C7:J42").ClearContents
            wsHome.Range("G8:J" & wsHome.Range("G" & Rows.Count).End(xlUp).Row).Copy
            wsR.Range("C7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            wsHome.Range("M8:M" & wsHome.Range("M" & Rows.Count).End(xlUp).Row).Copy
            ' something else...
        End If
    End Sub
    Last edited by CheeseSandwich; 05-13-2022 at 12:07 PM.
    If things don't change they stay the same

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Dynamic command button in a dynamic sheet
    By dorabajji in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-05-2019, 07:56 AM
  2. [SOLVED] Clear dynamic named sheet, compare cells from 2 sheets, copy all matched rows to dynamic s
    By drewship in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 12-15-2015, 12:31 PM
  3. Dynamic graph for dynamic sheet.
    By Tona in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-26-2015, 05:05 AM
  4. How to set a dynamic sheet or a line number dynamic from variant variables
    By skulo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-03-2015, 09:58 AM
  5. Replies: 0
    Last Post: 03-04-2013, 03:25 PM
  6. [SOLVED] Macro: Dynamic creation of sheet based on cell value then copy range to the new sheet
    By BeachRock in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-15-2012, 11:31 AM
  7. Creating Dynamic Folders/WorkBook from a Sheet with Dynamic Path.
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 03-28-2011, 05:42 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1