Excel VBA: How to use variables for worksheet name and range?

Question

By way of background, I'm a relative newbie to complex VBA scripting; I've written some over the years that move data, look stuff up, etc. However, for my current project, which is collecting stats for a certain type of pool game, I've reached my skill limit. Here's the background on the sheets in the project:

Input: User values for a match, which consists of up to 19 individual games (a match is over when one player gets 10 wins).. At the conclusion of each game in a match, the macro will copy the results to one of nine worksheets (see below) and then reset the Input sheet for the next game.

On the Input sheet, the player can choose from nine variations of the game; the chosen variation affects where the data is copied—they can choose to play with anywhere from 2 to 10 balls. I named this range Game_Level.

Nine worksheets: These store both individual game results for a match (using columns, six columns of data per game) and match (one row per match). The sheets are named quite simply the numbers 2 through 10, to match the game level the player has chosen to play.

NutsAndBolts: On this sheet, I store base values, list contents, and (importantly) I create a six-column single-row of data to be copied to the target sheet's location, named Game_Results. (The Input sheet is laid out for input, not easy data copy/paste.).

On each game worksheet, I have to keep track of the column that will be used for the next game's pasted data, as well as the active row on which to paste the data. This will vary on each sheet, because the player will not play the same number of each type of game. I know how to do this in Excel formulas, and I store the cell locations in a cell, which I can then reference (non-VBA, at least) via INDIRECT().

But I'm struggling with how to refer to a variable-dependent worksheet with a non-fixed cell address stored for use as an INDIRECT() reference. In VBA pseudo-code, it seems easy to me:

Sheets("NutsAndBolts").Range("Game_Results").Copy Sheets(Game_Level).Range(Indirect(some_cell on Game_Level sheet)

I tried the real-code version of that, but it doesn't work for many reasons. One of the biggest is that it seems you can't use INDIRECT() in a macro, so that really has me stuck.

Can someone explain the best way to refer to a sheet whose value depends on user input? And to then find the proper spot (row/column) at which to paste my data? Hopefully this explanation has been clear enough.

thanks;
-rob.

Answer 1

Update: I mostly have this working as I wanted. The solution, which is probably not ideal given my skill level, was the following. I added an indirect formula on NutsAndBolts that displays the cell location I store on the various result worksheets-G9 to start. I then named this cell Target_Cell.

I can now use the following code to copy and paste the results:

Dim targetSheet As String
targetSheet = Range("Game_Level")
targetCell = Range("Target_Cell")

Range("Game_Results").Copy
Worksheets(targetSheet).Range(targetCell).PasteSpecial Paste:=xlPasteValues

There are probably better ways to do this (or at least more correct ways to do this), but it's working for me.

-rob.