Using concatenated strings as a variable

Question

Evening you fine people!

I am struggling with a particular issue, there are two separate solutions that I have identified but I haven't specifically solved them

I have a list of 37 sheet names in a sheet (A1:A37) and the code I want to run is shown below - I don't know how to set 'z' to the particular cell reference - for example if A1 was Sheet1 I want Z to be Sheet1 and work as a variable. I am using a For loop to loop through the cells.

Workbooks("ED Test.xlsx").Sheets(z).Range("E2:E21").Value = Workbooks("TPT.xlsm").Sheets(z).Range("A2:A21").Value

The second method, more messy was to have the variables set within VBA and using the For loop (i.e. For x = 1 to 37) to concatenate the two values into a variable (e.g. "Sheet" and x) When I do this it gives a different error as it treats the concatenation as a string and not a variable

Please halp :)

JNevill · Accepted Answer · 2017-06-01 19:30:23Z

1

You'll need a loop. If you are trying to set E2:E21 in each worksheet to whatever is in that same worksheet's (but in another workbook) range A2:A21 you will loop through those sheets and do pretty much what you have above:

Sub dothething()
    Dim cellSheetName As Range

    'loop through all the cells holding sheet names in sheet1 (assuming here)
    For Each cellSheetName In Sheet1.Range("A1:A37").Cells
        'Copy the values in whatever sheet we found
        'Noting that the sheetname is held in the cell's value (cellSheetName.value)
        Workbooks("ED Test.xlsx").Sheets(cellSheetName.Value).Range("E2:E21").Value = Workbooks("TPT.xlsm").Sheets(cellSheetName.Value).Range("A2:A21").Value
    Next cellSheetName
End Sub

This could get more robust, but it's a good starting point.

edited Jun 1, 2017 at 19:30

answered Jun 1, 2017 at 19:28

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

OliEshmade Over a year ago

Ahh, as if it is as easy as that to set up the for each loop properly, thank you ery much, I've learned quite a bit today :)

JNevill Over a year ago

You could do for i = 1 to 37 then Sheets(Sheet1.Range("A" & i).value).range("E2:E21").value but that's ugly and for each cell in range.cells just makes more sense here.

Collectives™ on Stack Overflow

Using concatenated strings as a variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related