Using data from an array ..... VBA

[spydey]

I have a data set in a range, A3:B104.

Using the following code, I can assign that data set to an array.

Public Sub ItemArray()


Dim rg As Range
Set rg = ThisWorkbook.Worksheets("Summary").Range("A3:B104")


Dim Tracking As Variant


Tracking = rg.Value


Dim i As Long, j As Long


For i = LBound(Tracking) To UBound(Tracking)
    For j = LBound(Tracking, 2) To UBound(Tracking, 2)


    Next i
Next j


End Sub

I need to save a copy of the workbook with a filename of the values found in the array, but combined.

So basically whatever is in A3 & B3 needs to be the file name of the copy, then A4 & B4, then A5 & B5, etc.

I am having difficult combining my array data points to a single string so that the end result is as I stated above.

Once I can get it combined, I can get the rest, but this step is causing me some headache ......

I am currently researching, studying, and trying to learn everything I can on VBA arrays, but I don't think I have gotten there yet.

Any assistance, hint, advice, etc, is appreciated. I feel like my head is going to explode as I try and wrap my brain around this!!

-Spydey

 

[spydey]

So I guess essentially I would be looking for something like this:

Public Sub ItemArray()


Dim rg As Range
Set rg = ThisWorkbook.Worksheets("Summary").Range("A3:B104")


Dim Tracking As Variant
Tracking = rg.Value

Dim Path as String
Path= "C:\Folder\Where\Files\Are\To\Be\Saved\"

Dim filename as String


Dim i As Long, j As Long


For i = LBound(Tracking) To UBound(Tracking)
    For j = LBound(Tracking, 2) To UBound(Tracking, 2)

'filename= i(1,1) & "-" & j(1,2)
ActiveWorkbook.SaveCopyAs Filename:=Path & filename



    Next j
Next i


End Sub

I am aware that the 'filename=i(1,1) & j(1,2)' are mostly likely in the wrong location, and are written incorrectly. I am just using them as an indication as to what I am looking for and where the data point is coming from in the array. Also, the "1,1 & 1,2" would change appropriately as the code advances through the array.

I hope that helps more than it confuses ......

-Spydey

P.S. In my original post, I accidentally switched the i & j at the end of the code. My apologies.

 

[Norie]

Spydey

In the array the first indice is for the row and the second is for the column.

So Tracking(1,1) would refer to the value in the first row of the first column in the range A3:B104 i.e. A3.

Similarly Tracking(1,2) would refer to the value in the second row of the first column in the range A3:B104 i.e. B3.

So to loop down the rows and setting the filename to the values from columns A and B you could try something like this.

Public Sub ItemArray()
Dim rg As Range
Dim Tracking As Variant
Dim strPath As String
Dim strFileName As String
Dim I As Long

    Set rg = ThisWorkbook.Worksheets("Summary").Range("A3:B104")

    Tracking = rg.Value

    strPath = "C:\Folder\Where\Files\Are\To\Be\Saved\"

    For I = LBound(Tracking) To UBound(Tracking)

        strFileName = Tracking(I, 1) & "-" & Tracking(I, 2)
        ActiveWorkbook.SaveCopyAs filename:=strPath & strFileName

    Next I

End Sub

 

[xenou]

Also be sure your filename has a (correct) file extension in it. I assume that the use of ThisWorkbook and ActiveWorkbook is intentional but also will just point out that these can refer to two different workbooks (if the activeworkbook is not the one with the code in it).

 

[spydey]

Spydey

In the array the first indice is for the row and the second is for the column.

So Tracking(1,1) would refer to the value in the first row of the first column in the range A3:B104 i.e. A3.

Similarly Tracking(1,2) would refer to the value in the second row of the first column in the range A3:B104 i.e. B3.

So to loop down the rows and setting the filename to the values from columns A and B you could try something like this.

Public Sub ItemArray()
Dim rg As Range
Dim Tracking As Variant
Dim strPath As String
Dim strFileName As String
Dim I As Long

    Set rg = ThisWorkbook.Worksheets("Summary").Range("A3:B104")

    Tracking = rg.Value

    strPath = "C:\Folder\Where\Files\Are\To\Be\Saved\"

    For I = LBound(Tracking) To UBound(Tracking)

        strFileName = Tracking(I, 1) & "-" & Tracking(I, 2)
        ActiveWorkbook.SaveCopyAs filename:=strPath & strFileName

    Next I

End Sub

Norie,

I just wanted to say thank you for assisting me. I have been on vacation over the last week+, so I didn't have a moment to thank you. Sorry if it seemed I shrugged you off and wasn't appreciative.

-Spydey

 

[spydey]

Also be sure your filename has a (correct) file extension in it. I assume that the use of ThisWorkbook and ActiveWorkbook is intentional but also will just point out that these can refer to two different workbooks (if the activeworkbook is not the one with the code in it).

Understood. Thanks for pointing that out!

-Spydey

 

[spydey]

Question:

If instead of using a static range of "A3:B104", I wanted to use a dynamic range, starting @ A3:Bx ('x' being the last row that has data in it), how would I do that?

I was thinking of something like this:

Set rng = ThisWorkbook.Worksheets("Summary").Range("A3:B3").End(xlDown)

But when I use that, I get a Run-time error of 424: Object required.

So I ran the macro recorder and came up with this:

Sub SelectDynamicRange()

    Range("A3:B3").Select
    Range(Selection, Selection.End(xlDown)).Select


End Sub

But now I need to find a way to implement this into the already existing code that Norie provided me .... ... hhhhmmmmmmm

Maybe something like this (???):

Public Sub ItemArray()
Dim Start As Range
Dim rng As Range
Dim Tracking As Variant
Dim strPath As String
Dim strFileName As String
Dim I As Long


    Set Start = ThisWorkbook.Worksheets("Summary").Range("A3:B3")
    Set rng = Range(Start, Start.End(xlDown))
    
    Tracking = rng.Value


    strPath = "C:\Folder\Where\Files\Are\To\Be\Saved\"
    
    Debug.Print "File Name"


    For I = LBound(Tracking) To UBound(Tracking)


        strFileName = Tracking(I, 1) & " - " & Tracking(I, 2)
        Debug.Print strFileName


    Next I


End Sub

Is there perhaps a cleaner way of doing what I am shooting for?

My starting point will always be "A3", my ending column will always be "B", but my ending row is dynamic.

-Spydey

 

[spydey]

P.S., when I mean "cleaner" I don't mean Norie's code. I just mean selecting from"A3:Bx" in as few steps as possible. Norie's code is great and I am grateful for the help.

-Spydey

 

[spydey]

Ok, so I think that I cleaned it up a bit more by getting rid of the "start" variable that I was using. I realized I could have just used the same "rng" variable.

Public Sub ItemArray()

Dim rng As Range
Dim Tracking As Variant
Dim strPath As String
Dim strFileName As String
Dim I As Long


    Set rng = ThisWorkbook.Worksheets("Summary").Range("A3:B3")
    Set rng = Range(rng, rng.End(xlDown))
    
    Tracking = rng.Value


    strPath = "C:\Folder\Where\Files\Are\To\Be\Saved\"
    
    Debug.Print "File Name"


    For I = LBound(Tracking) To UBound(Tracking)


        strFileName = Tracking(I, 1) & " - " & Tracking(I, 2)
        Debug.Print strFileName


    Next I


End Sub

Anything else that I am missing?

-Spydey