Need help to identify Excel objects as to what type that should be declared as for an Excel VBA operation!

I have the following code that starts out like so:
What I'm having difficulty is dim the correct types for these three types of objects:  nmlist,    formcol and formcolend?

So far I have it declared as the following...

Dim nmlist As Variant
    Dim formcol As Variant
    Dim formcolEnd As Variant
    Dim destrow, i, j As Integer

But what I would like to determine is what nmlist, formcol and formcolend should be declared - not just variant?  I'm guessing that formcol and formcolend should be strings.... but I really have no clue what nmlist is and it's really important I find out!

Can anybody familiar with Excel VBA coding - determine what nmlist, formcol and formcolend should be declared as?

I put in the remarks where they exist in the code below .... ( '*** FIRST ONE TO IDENTIFY ***** and the final one:     '*** SECOND AND THIRD ONE TO IDENTIFY *****)

Thank you in advance for the answer or guess.

Public Sub GenerateOneReport(ByVal sFullName As String)
On Error GoTo Err_This

    Rem iterate through the main worksheet
    Dim awb, nwb As Workbook
    Dim pws, iws, nws, sws, tws As Worksheet
    Dim fn As String

    'originally not in the code.... added to guess
    Dim nmlist As Variant
    Dim formcol As Variant
    Dim formcolEnd As Variant
    Dim destrow, i, j As Integer

    Dim lastProj As String
    Dim thisProj As String
    Dim rgname As String
    Dim pt As PivotTable
    Dim sBodyText As String
    Dim sDirectory As String
    Dim sSubjectLine As String
    Dim sFileName As String
    Dim iArea As Integer

    sDirectory = Application.Worksheets("master").Range("StorageDir").Value

    Set awb = ActiveWorkbook
    '' status
    Application.StatusBar = "Starting process to create report for: " + sFullName

    '' fill the worksheet
    Set nwb = Workbooks.Add

    '*** FIRST ONE TO IDENTIFY *****
    Set nmlist = nwb.Names

    ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)
    ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)
    ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)

    '' scratch tab
    Set iws = nwb.Worksheets(3)
    iws.name = "Scratch"

    '' do the instructions worksheet
    Set iws = nwb.Worksheets(1)
    iws.name = "Instructions"
    Set sws = awb.Worksheets("instructions")

    '' paste instructsions
    sws.Rows.Copy
    iws.Cells(1, 1).PasteSpecial Paste:=XlPasteType.xlPasteValuesAndNumberFormats
    sws.Rows.Copy
    iws.Cells(1, 1).PasteSpecial Paste:=XlPasteType.xlPasteFormats
    sws.Rows.Copy
    iws.Cells(1, 1).PasteSpecial Paste:=XlPasteType.xlPasteColumnWidths


    '' old way sws.Rows.Copy Destination:=iws.Cells(1, 1)

    '' do the billing worksheet
    Set nws = nwb.Worksheets(2)
    nws.name = "Billing & Revenue"
    nws.Activate
    lastProj = ""

    '' get storage dir and other variables
    sDirectory = awb.Worksheets("master").Range("StorageDir").Value
    sSubjectLine = "Billing & Revenue Worksheet for " + awb.Worksheets("master").Range("PeriodEnding").Text

    '*** SECOND AND THIRD ONE TO IDENTIFY *****
    formcol = awb.Worksheets("master").Range("FormulaCol").Value
    formcolend = awb.Worksheets("master").Range("FormulaColEnd").Value

    '' get source
    Set sws = awb.Worksheets("detail")

    '' copy the first 2 rows
    destrow = 1
    sws.Rows(1).Copy Destination:=nws.Rows(destrow)
    destrow = destrow + 1
    sws.Rows(2).Copy Destination:=nws.Rows(destrow)
    destrow = destrow + 1

Open in new window

LVL 1
stephenlecomptejrAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Not exactly what you asked about but when you do
 Dim destrow, i, j As Integer

Open in new window

only j is an Integer and the other two are Variants. So instead do
Dim destrow As Integer, i As Integer, j As Integer

Open in new window

or
Dim destrow As Integer
Dim i As Integer
Dim j As Integer

Open in new window

0
Martin LissOlder than dirtCommented:
Oh, and by the way...

    Dim nmlist As Name
    For Each nmlist In Names
        ' do something
    Next

Open in new window

0
stephenlecomptejrAuthor Commented:
Hey Martin, When I do a Dim nmlist as Name - I get an error:  Run-time error '13' Type mismatch.  Please note attachments.
error1.PNG
error2.PNG
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

stephenlecomptejrAuthor Commented:
Now it seemed to work with Dim nmlist as Names.... though.  Testing it further.
0
Martin LissOlder than dirtCommented:
Names is the collection of Named Ranges while Name is a single Named Range.
 
In the code that you originally posted and also in the pictures you didn't show what you want to do with nmlist. My code shows how to use nmlist to iterate through the Names collection.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stephenlecomptejrAuthor Commented:
Thank you for your help.

Dim nmlist as Names   was the one that worked for me.   I never could have arrived to the correct answer without your help.
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2016
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Coding

From novice to tech pro — start learning today.