VBA for hlookup, copy and paste.

[pete4monc]

Hi all

Please, I am in need of some help with my code, I have recorded some code and have the basics which does work, once!
I have done this with excel - Hlookup and it works well but each time the text changes (daily) on the Master sheet all the other sheets change. That's why I thought vba with a button command would work better.

Basically I would like the vba to find the last text in sheet "Furnace 1", column A, find the matching text in sheet "Master" (could be any where from E5:N24). Copy the cell below the matching text in sheet "Master"and paste it into the cell next to the original text in sheet "Furnace 1", column B.
Then repeat this for the other sheet "Furnace 2", column A up until "Furnace 10" if there are any occurrences, if not then nothing to copy.

Many thanks for your help.

Below is my code.
Sub Macro1()
'
' Macro1 Macro
'
Sheets("FURNACE 1").Select
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & lastRow).Select
Cells.Find(What:="h1831", After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Sheets("Master").Select
Cells.FindNext(After:=ActiveCell).Activate
Range("E5").Select
Selection.Copy
Sheets("FURNACE 1").Select
Range("B4").Select
ActiveSheet.Paste
End Sub

 

[Snakehips]

@pete4monc Can you please confirm or otherwise:

10 sheets for Furnaces 1- 10
Range E5:N6 in Master sheet relates to Furnace 1. Range E7:N8 to Furnace 2. etc*.
Code to find last Heat No. entered in column A of each Furnace sheet
Find the corresponding Alloy from the appropriate Master sheet range*
Paste the Alloy to column B of the Furnace sheet. Or Blank if nothing found.

 

[Snakehips]

Assuming that my above assumptions are correct then, the following may well be worth a test.

Sub Furnace_Update()

For F = 1 To 10   'Furnace' Sheet suffix

    HtRow = 3 + 2 * F    'Heat No row
    AlRow = HtRow + 1    'Alloy row
    Furnace = "Furnace " & F   ' Furnace sheet name  
    On Error GoTo DuffSheetName   'To redirect  if missing or bad sheet name
   
        With Sheets(Furnace)  'With current Furnace sheet of the loop
            LastRow = .Cells(Rows.Count, "A").End(xlUp).Row   'Last Heat No Row           
            ' Enter Index / Match formula in Furnace sheet col B
            .Range("B" & LastRow).Formula = "=IFERROR(INDEX(Master!E" & AlRow & ":N" & AlRow & ",1,MATCH(A" & LastRow & ",Master!E" & HtRow & ":N" & HtRow & ",0)),"""")"
            .Range("B" & LastRow).Value = .Range("B" & LastRow).Value  'convert the formula to it's resultant value
        End With

    DuffSheetName:    'Sheet name errors re-directed to here
    Resume Next       'Ignore the error
Next F      ' Loop for next Furnace sheet suffix
On Error GoTo 0    'Re-set default error handling
End Sub

Hope that helps.

 

[pete4monc]

Hi Snakehips

@pete4monc Can you please confirm or otherwise:

10 sheets for Furnaces 1- 10
Range E5:N6 in Master sheet relates to Furnace 1. Range E7:N8 to Furnace 2. etc*.
Code to find last Heat No. entered in column A of each Furnace sheet
Find the corresponding Alloy from the appropriate Master sheet range*
Paste the Alloy to column B of the Furnace sheet. Or Blank if nothing found.

Hi Snakehips - Thanks so much for your help.

Master sheet changes daily so the A column in the Furnace sheets needs to search for the Heat number H1831 on the Master sheet. Finds it and copies the alloy (HR5) to the Furnace 1 sheet and paste it next to the corresponding heat number H1831 in column B. Then repeats to find the other matching heat numbers and alloys. In total there are 11 sheets, Master, Furnace 1, Furnace 2 etc etc. up to Furnace 10.

 

[Snakehips]

Please bear in mind that we don't share your intimate involvement in the subject so, what is as clear as day to you can be as clear as mud to the rest of us.
So:
Master sheet changes daily.
The numbers down left side of Master sheet do not relate to a Furnace number? So Furnace 1 Heat No match could be found in section 8 ?
As per your original post you are looking to match the last text in Column A, of each Furnace sheet, with a Heat No in the Master sheet and return the corresponding Alloy ?
Code to loop through 10 Furnace sheets and just check each last row?

 

[pete4monc]

Please bear in mind that we don't share your intimate involvement in the subject so, what is as clear as day to you can be as clear as mud to the rest of us.
So:
Master sheet changes daily.
The numbers down left side of Master sheet do not relate to a Furnace number? So Furnace 1 Heat No match could be found in section 8 ?
As per your original post you are looking to match the last text in Column A, of each Furnace sheet, with a Heat No in the Master sheet and return the corresponding Alloy ?
Code to loop through 10 Furnace sheets and just check each last row?

@Snakehips
The numbers down left side of Master sheet do not relate to a Furnace number? Well spotted, Yes they do.
So Furnace 1 Heat No match could be found in section 8 ? No, Furnace ! heat will only be found in E2:K2
As per your original post you are looking to match the last text in Column A, of each Furnace sheet, with a Heat No in the Master sheet and return the corresponding Alloy ?
Code to loop through 10 Furnace sheets and just check each last row? Yes

 

[Snakehips]

@pete4monc
In that case, then, my original code should pretty much be ok?
Have you actually tried it???

I got the impression from your original post that the Master Heat No data was potentially in columns E : N
If as per your latest post, it is only columns E : K then edit the code row as per below, where i have changes two instances of N to K

.Range("B" & LastRow).Formula = "=IFERROR(INDEX(Master!E" & AlRow & ":K" & AlRow & ",1,MATCH(A" & LastRow & ",Master!E" & HtRow & ":K" & HtRow & ",0)),"""")"

 

[pete4monc]

@Snakehips vba works a treat. I found out why it wasn't working originally, the Row headings had : after the text....lol ...Thanks so much, I really appreciate the help.

How could I get the vba to copy multiple heats on the same day from the same furnace?

 

[Snakehips]

Pleased it helped.

Try this for multiple Heat Nos

Sub Furnace_Update()

For F = 1 To 10   'Furnace' Sheet suffix

    HtRow = 3 + 2 * F    'Heat No row
    AlRow = HtRow + 1    'Alloy row
    Furnace = "Furnace " & F   ' Furnace sheet name
    
    On Error Resume Next   'To redirect  if missing or bad sheet name
    
        With Sheets(Furnace)  'With current Furnace sheet of the loop
            If Err.Number > 0 Then
            Err.Clear
            GoTo DuffSheetName
            End If
            FirstRow = .Cells(Rows.Count, "B").End(xlUp).Row + 1  'First blank Heat No Row
            LastRow = .Cells(Rows.Count, "A").End(xlUp).Row   'Last Heat No Row
            
            ' Enter Index / Match formula in Furnace sheet col B
            .Range("B" & FirstRow & ":B" & LastRow).Formula = "=IFERROR(INDEX(Master!E$" & AlRow & ":N$" & AlRow & ",1,MATCH(A" & FirstRow & ",Master!E$" & HtRow & ":N$" & HtRow & ",0)),"""") "
            .Range("B" & FirstRow & ":B" & LastRow).Value = .Range("B" & FirstRow & ":B" & LastRow).Value  'convert the formula to it's resultant value
        End With

DuffSheetName:   'Sheet name errors re-directed to here

Next F      ' Loop for next Furnace sheet suffix

On Error GoTo 0    'Re-set default error handling
End Sub

Edit the N to K again if appropriate.

 

[pete4monc]

@Snakehips
Fantastic , works a treat. Thank you very much for your help.