How can I use a variable when refering to a sheet, when the name of the sheet is

Question

How can I use a variable when refering to a sheet, when the name of the sheet is contained in a certain cell?

Hello All,

I'm stuck on something that is probably simple to many of you but has me completely baffled. I have a workbook where I paste billing data for all customers to one sheet. My macro creates a list of unique customer IDs from that sheet and then creates a new sheet for each customer, using the customer ID as the sheet name.

Once all of the sheets are created, I want to filter the original list by customer ID and put the detail for each customer ID on the sheet with the same name. The macro is looping through the list of customer names to filter for each customer, just as it did to create each sheet. However I can't figure out how to use the name of the sheet in the paste function. I've set up a variable (Dest.sh) to hold each sheet name in the list during the loop process. I'll post the code for the entire macro below, but here's specifically the line that I think I need help with:   Set Dest.sh = Sheets(br)

ALSO - I haven't tried to do this yet, but ultimately I'd like to create a new workbook for each individual customer sheet, with the name of the workbook being the Customer ID plus the date of the billing (which I could get from user input) in YYYY-MM-DD format and saved in the same folder as the original workbook. I'd like the original workbook to retain each of the sheets as well. If anyone could help me with the code to do this, it would save a ton of time for me, because I am still very slow with creating macros - lots of trial & error.

Any help with this would be much appreciated!

Here's the entire macro. The section that's giving me problems begins after the row of ****

Sub Copy_BLANK_2()
' PMC August 2014
' Creates a sheet for each customer in the "DETAIL" sheet
' October 2014 Updated to move detail to each customer sheet


    Dim LastRow, LastRow2, LastRow3, LastRow4 As Long
    Dim endOfList As String
    Dim copySheet As Worksheet
    Dim numberList As Range
    Dim anyNumber As Range
    Dim NoSheets As Long
    Dim My_Rng, BrRng As Range
    Dim DestSh As Worksheet, Source As Worksheet
    Dim CalcMode2 As Long
    Dim ViewMode2 As Long

' Clear Out LIST sheet
    Sheets("List").Select
    LastRow4 = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A1:A" & LastRow4).ClearContents
    Range("A1").Select

' Remove Asterisks from Cust ID and Create List of Unique IDs on LIST sheet
    Sheets("DETAIL").Select
    LastRow = Cells(Rows.Count, "I").End(xlUp).Row
  
    Range("I3:I" & LastRow).Select
    Selection.Replace What:="~*", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
  
    Selection.Copy
    Sheets("LIST").Select
    Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    LastRow2 = Cells(Rows.Count, "A").End(xlUp).Row

    ActiveSheet.Range("$A$1:$A$" & LastRow2).RemoveDuplicates Columns:=1, Header:=xlNo
  
    LastRow3 = Cells(Rows.Count, "A").End(xlUp).Row
  
    ActiveWorkbook.Worksheets("LIST").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("LIST").Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("LIST").Sort
        .SetRange Range("A2:A" & LastRow3)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

  
' Copy the BLANK sheet for all unique Cust IDs on LIST sheet


    Const SheetToCopy = "BLANK" ' name of sheet
    Const ListSheetName = "LIST" ' name of sheet
    Const startOfList = "A1" ' first of list's address
    endOfList = "A65" ' end of list's address

    Set copySheet = ThisWorkbook.Worksheets(SheetToCopy)
    Set numberList = ThisWorkbook.Worksheets(ListSheetName). _
    Range(startOfList & ":" & endOfList)

    For Each anyNumber In numberList
        copySheet.Copy After:=Sheets(Worksheets.Count)
        On Error Resume Next
        'if sheet name already exists, will generate an
        'error that we must deal with
        ActiveSheet.Name = anyNumber
    Next

'*************************************************************************************************


'Filter full list of customer detail and put appropriate detail on customer pages

' Find range of Sheet Names
    NoSheets = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
    Set BrRng = Sheet1.Range("A1:A" & NoSheets)
    Sheet2.Select

'Set filter range on ActiveSheet
    Set Source = Sheet2
    Mycount = Cells(Rows.Count, "A").End(xlUp).Row
    Set My_Range = Range("A3:R" & Mycount)

    'Remove any existing AutoFilter
    My_Range.Parent.AutoFilterMode = False

    For Each br In BrRng
        'If Br.Value > 0 Then
        'Filter and set the filter field and the filter criteria :
            My_Range.AutoFilter Field:=9, Criteria1:=br
            With My_Range.Parent.AutoFilter.Range
                On Error Resume Next
                ' Set rng to the visible cells in My_Range without the header row
                Set Rng = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
                      .SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
                If Not Rng Is Noothing Then
                'Copy and paste the cells into DestSh beginning in cell A3
                   Set Dest.sh = Sheets(br)
                    Rng.Copy
                    With DestSh.Range("A3")
                    .PasteSpecial xlPasteValues
                    End With
                End If
            End With
        'End If
    Next

'Housekeepinng
    My_Range.Parent.AutoFiltterMode = False
    Application.CutCopyMoode = False
    Application.ScreenUpdating = True

Explanation / Answer

Re: unknown code problem

All variables should be declared.
Using "Option Explicit" as the first line in each module will prompt a notification of undeclared variables. Its use is good programming practice.

As for "Set Dest.sh = Sheets(br)"
Dest.sh should be DestSh
br is not declared
'---

Something like the following is requirred...
Dim br As Range
Dim DestSh As Worrksheett

Set DestSh = Sheets(br.Value))

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.