Replace Fill Down, Complicated Equation

I have an equation in H1 ie "=HYPERLINK(CONCATENATE("file:///\\uranus\dckgen\Brands\Zoom\Brand - Zoom\Upload Photos\2012\week",B1,"\BHS_WK",B1,"_LR\",E1,".jpg"))"

I want to fill down as far as Column B AND OR E is filled out.

Initially I was selecting A so that the whole column was selected and then edit > Fill down.

This method starts to make the sheet very large.

How can I can write a macro that will do this? I would need to work on some sheets and not others. So sheet2 sheet4 sheet 9 etc.

it would also need to automatically happen as opposed to a button to run the macro.
This is a version of the code I have from jraj1106

Sub FORMULADATA()
    Dim rcnt As Long
    
    rcnt = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 1 To rcnt
        If Range("A" & i).Value <> "" Then
            Range("B" & i).Value = Range("A" & i).Value - 95
        End If
    Next
End Sub

Once I have this working, how might I add it to individual sheets so that it happens my default with no need to press buttons.

Many Thanks

Matt

Hello there,

Could you attach a sample of your workbook?

Thanks!

Wouldn't upload to big, here it is below

http://wtrns.fr/aYAz503ozFTw4B

Alrighty try the below code:

Option Explicit


Private Sub Workbook_SheetActivate(ByVal Sh As Object) ' on worksheet selected

Dim ws As Worksheet    'declare variable ws as a worksheet
Dim LR As String    'declare variable LR as a string

Select Case ActiveSheet.Name    'if the activesheet's name
    Case "BHS"  'is BHS then (you can add more sheet names you want to include here
                'by adding , and the name in quotes after "BHS". Enter the names you do
                'not want to copy the value in H1
        
        'then do nothing
    
    Case Else 'if it's not those list then
           
        For Each ws In ThisWorkbook.Sheets   'for each worksheet in this workbook
            
            Select Case ws.Name   'if worksheet name
        
                Case Is = "BHS"  'is entitled one of the following sheet names then replace sheet names to skip with your sheet names
          
                      'Do nothing - These sheets are to be skipped over
          
                Case Else   'if the worksheet name is not one of the listed above
            
                        With ws
               
                            .Range("H1").Select     'select H1 in worksheet
                            LR = .Range("B6555").End(xlUp).Row      'sets variable LR equal to the last row that contains data in column B
                            Selection.Copy 'copy H1
                            .Range("H2:H" & LR).PasteSpecial    'paste the formula all the way down column H
                        End With
          
            End Select  'end the select case
        
        Next ws     'move to the next worksheet

End Select  'end the first select case

End Sub 'exit the code

To insert the code follow the below steps

1. Open VBA (Alt+F11)
2. In the Project Explorer on the far left (if not there press Ctrl+R) double click on This
Workbook.
3. Paste the above code here. Anything that appears in green is a comment to help you
understand the code.
4. Exit out of VBA

The code is set to run whenever you select a worksheet.

Let me know if this works for you or if you have any questions.

Thanks!

The code is set to run whenever you select a worksheet.

I haven't run the code as yet but I notice that you mentioned the above. The only thing is, that sheet will never be viewed (referenced but not viewed directly)

They way the sheet works at the moment is that the data is pasted into BHS then the submitted button clicked (this outputs a CSV) then pastes it on to the bottom of the list in BHSDB. Maybe I should be running the macro from the end of that macro so that it does the fill down?

Also on the complete sheet there are many other sheets.

Just going to experiment with the little knowledge that I have.

Many Thanks so FAR!!!

I've uploaded the sheet i am working on here

I noticed that as soon as anything gets added it plays up I can only assume its active on all work sheets or its effect by other Macros that I have running.

The Fill we are trying to run here is to run on all the sheets ending in DB (Database)

The way it works at the moment is that code gets pasted into a sheet without a DB so BHS and then a click of the submit adds it to the database on BHSDB its here I am trying to achieve the fill down.

I'll take other pointers if you know anyway in which I might be able to get the file size down.