Loop through sheets based on cell contents

[gteamer]

Hello,

Version of the program - Excel 365 (2016 I guess? I am not sure...)

I am trying to open sheet2 based on the contents of a cell in sheet1. Then copy data from that sheet2, and paste it into sheet1. Then repeat through all of the sheets in a workbook. I am using the message box as a "checker" for now. I am unable to set the variable scorecardName == activecell. I have tried range.value, cell.value, selection.value, activecell.value. I am lost. If I can get this part working, the rest of the macro should be a breeze and I will be able to finish this in a couple minutes. I was thinking that this would work, but for some reason it is not, when I click debug the "red" words are highlighted:


' Scorecards Macro

Dim WS_Count As Integer
Dim I As Integer

'Count worksheets and cycle
WS_Count = ActiveWorkbook.Worksheets.Count

For I = 1 To WS_Count

'Give Name of Worksheet
MsgBox ActiveWorkbook.Worksheets(I).Name

Next I
'
Dim scorecardName As Long
scorecardName = Range("B1").Value

Workbooks.Open "C:\" & scorecardName & ".xls"
Cells.Find(What:="Vendor Summary : Jacmel", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range(ActiveCell.Offset(-2, 0).EntireRow, ActiveCell.End(xlDown)).Select


Any help or better/more creative solutions would be more than welcome!

Thank you,

1. Version of the program
2. What you want it to do
3. Cell references, bookmark names, column letters, row numbers, worksheets, styles, whatever pertains to the information at hand
4. Error messages if any
5. If not the entire code, then at least some of it
6. Sample data (before and after sample worksheets, add as attachments here)
7. Politeness and gratitude
8. Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.

[Kenneth Hobs]

Welcome to the forum!

Maybe because the value of [B1] is not a LONG type?

[gteamer]

Hello Kenneth,

That was it!! I misunderstood the meaning of the word Long. That was so easy, and now I feel silly.

Thank you.