PDA

View Full Version : [SOLVED] Loop through sheets based on cell contents



gteamer
12-06-2016, 02:58 PM
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,




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

Kenneth Hobs
12-06-2016, 06:37 PM
Welcome to the forum!

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

gteamer
12-07-2016, 06:25 AM
Hello Kenneth,

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

Thank you.