Possible Macro?

[imcfcl]

Hi,

Just wondering if this would be possible? I have a database with a list of Doors in column B (Door 1, Door 36, Door 57 etc) and in C is the location of that door.

All of the doors in column B, have a hidden worksheet in the workbook which is named Door X (exactly the same as whatever is in col B)

I would like to add the word "Location" to cell H5, then I5 to have whatever is in col C for that given door. I've got well over 180 doors to add, its a big worksheet but thankfully its a top spec laptop and copes no problem... if i could do this with code insteadd of manual it would be a big help.

 

[imcfcl]

Any body out there thinking this is possible and could point me in the right direction?

Any other info needed please let me know

Cheers

 

[John_w]

Try this:

Sub Doors()

    Dim lastRow As Long, row As Long
    
    lastRow = Cells(Rows.Count, "B").End(xlUp).row
       
    For row = 2 To lastRow
        With Sheets(Cells(row, "B").Value)
            .Range("H5").Value = "Location"
            .Range("I5").Value = Cells(row, "C").Value
        End With
    Next
    
End Sub

 

[imcfcl]

Hi John,

Thanks for the response, I might not have explained very well, a picture might help.

I have my 'Database' tab in the workbook, and every record in col c (originally B in my op), has a HIDDEN worksheet in the tab. I've unhidden the first one so you can see. what I mean

Hope that makes a little morre sense about what im trying to do?

 

[John_w]

With the data in columns C and D starting in row 3, just modify the code to suit (it should be obvious which lines need changing). The code should work whether the sheets are hidden or not.

But, you show 3 Door 37's with different locations. Which location is the code meant to use?

 

[imcfcl]

John .. Amazing. I thought it would have to be much more complicated than that but its done everything I wanted and I can edit that code to do a few other thing's.

Cheers.