Variant Question

[ummjay]

Hi! i'm trying to set a variant to a specific cell on the macro workbook, and retain that throughout my code. for some reason it' showing as empty, even though I have a value in that cell on the macro WB.

Any ideas?

Set macroWB = ActiveWorkbook
Name = macroWB.Range("P10").Value

'set Range
Dim lr&: lr = ActiveSheet.Cells.Find("*", [A1], _
    xlFormulas, SearchDirection:=xlPrevious).Row
Set WBrng = Range("A1:J" & lr)
WBrng.Copy

Set Wb = Workbooks.Add(1)
    With Wb.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0

    Set Wb = ActiveWorkbook ' or a specific open workbook
    sPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    sFile = "Name " & Format(Now(), "mmddyyyy") & ".xlsx"
    Wb.SaveAs Filename:=sPath & "\" & sFile

    End With
''end

 

[Phuoc]

Change: Name = macroWB.Range("P10").Value ' The name of the sheet is missing

To: Name = macroWB.Sheets("abc").Range("P10").Value

 

[ummjay]

worked like a charm, thanks!