-1

I have a code which consolidates a specific sheet from multiple workbooks where the sheet name of the opened file is always a number from 1-30. Right now, I have to indicate the sheet name/number one at a time. Is there a way to do it like 3 or 7 times? like a loop? e.g 1-7 or 25-27.. It is always ascending so i thought a code like the one below will work? Thoughts?

For sName = sNameStart To sNameEnd

Sheets(sName).Activate

On Error GoTo 0
Range("d11:j11").Select
        Range(Selection, Selection.End(xlDown)).Copy
ThisWorkbook.Sheets("Sheet1").Activate
Range("b2").Select
 Do
If IsEmpty(ActiveCell) = False Then
 ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.PasteSpecial Paste:=xlPasteValues


Next sName

where sName is the sheet name, sNameStart is the start sheet and sNameEnd is the end sheet. However, i get there's this error when I start this code.. Help?

Addendum:

sName is a number that is always ascending and sNameStart and sNameEnd are both ranges indicated as range("i1") and range("k1") respectively.

TrevorDhien
  • 25
  • 1
  • 2
  • 7
  • possible duplicate of [macro that loops through worksheets](http://stackoverflow.com/questions/22201350/macro-that-loops-through-worksheets) – RubberDuck Apr 30 '14 at 13:23
  • I think this [answer](http://stackoverflow.com/a/22201978/3198973) will get your started. – RubberDuck Apr 30 '14 at 13:24
  • thanks but that code will cycle through all worksheets in the entire workbook whilst i'm only looking for a certain range of worksheets.. The sheets are numbered 1-30 (for day 1, day 2, etc).. and consolidation will happen on a weekly basis e.g sheets 3-10.. – TrevorDhien Apr 30 '14 at 14:13

1 Answers1

0

Make the loop like this:

For i = 1 To 7
    sName = "Sheet" & i
    Sheets(sName).Activate
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • problem is the sNameStart and sNameEnd are both ranges.. they're indicated in range("i1") and range("k1") respectively.. – TrevorDhien Apr 30 '14 at 14:08
  • So the Sheet names are stored in cells?? – Gary's Student Apr 30 '14 at 14:12
  • not exactly.. the sheet names are numbered 1-30 (for day 1, day 2, etc).. and consolidation will happen on a weekly basis e.g sheets 3-10.. both sNameStart and sNameEnd are ranges, which will correspond to any number between 1 and 30. – TrevorDhien Apr 30 '14 at 14:33