How do I increment a value that I want to link to a 'sheet' name?

[NRGZ]

Hi,

I will be importing a number of CSV files into a workbook and this will create individual worksheets, generic name sheet1, sheet2........., I will be pulling the same 4 items of data from each sheet into 'Totals' sheet.
My question is, I have the Total amount of sheets counted and stored as 'shts', Is it possible to have an incremental variable / string linked to the sheet name so that i can retrieve the data from each sheet as the total amount of imports/sheets may vary?

Code So far,
Dim shts As String

shts = ThisWorkbook.Sheets.Count

' This is a check count
' Cells(3, 3) = shts


Sheets("Totals").Range("A2").Value = Sheets("sheet2").Range("A2").Value
Sheets("Totals").Range("B2").Value = Sheets("sheet2").Range("B2").Value
Sheets("Totals").Range("C2").Value = Sheets("sheet2").Range("I8").Value
Sheets("Totals").Range("D2").Value = Sheets("sheet2").Range("I7").Value


End Sub

Can the '2' in the Sheet name be replaced by a variable name?

Thanks In Advance.
o/

 

[RobVos]

Sheets("sheet" & variablename)

 

[NRGZ]

Sheets("sheet" & variablename)

That works perfectly! but i have a new issue...

Sheets("Totals").Cells(a, 3).Value = Sheets("sheet" & a).WorksheetFunction.Sum(Range("C:C")).Value

I'm not sure what i'm doing wrong but it doesnt like it...
Ideas?

 

[JGordon11]

try

Sheets("Totals").Cells(a, 3).Value =Application.WorksheetFunction.Sum( Sheets("sheet" & a).Range("C:C"))

 

[NRGZ]

try

Sheets("Totals").Cells(a, 3).Value =Application.WorksheetFunction.Sum( Sheets("sheet" & a).Range("C:C"))

For some reason that didnt work, it didnt give an error msg either.... but whilst trawling for an answer i customised this with the parameters i needed,

Sheets("totals").Cells(a, 4).Value = WorksheetFunction.Sum(Worksheets("sheet" & a).Range(Worksheets("sheet" & a).Cells(2, 3), Worksheets("sheet" & a).Cells(999, 3)))

probably longer than needed but as its works i'm going with it.

Thanks to All.
o/