Filling in a history log table from several sheets

[JPMarr]

Hello,

So I have tried to research this already but happy to be pointed in the right direction to a post that can help with this!
Basically I am creating several checksheets on different sheets that once completed I want to hit a button and it take certain info from each sheet such as the time, date etc and what sheet and have that fill in a table on a separate sheet as a history log. I've used the record feature and just copy and pasted the data and it fills in the data fine from my first trial sheet however I can only get it to fill in the top line of the table and not filter down to the next empty line.

2 querys then how do I get it to fill out the next empty line

secondly will whatever the fix the first problem work for every sheet and separate button that I put in place?

this is my lengthy recorded code so far;

Sub HistoryBTF1()
'
' HistoryBTF1 Macro
'

    Range("E3").Select
Sheets("Delivery").Select
Range("K4:L4").Select
Selection.Copy
Sheets("HISTORY LOG ").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B2").Select
Sheets("Delivery").Select
Range("K5:L5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("HISTORY LOG ").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Delivery").Select
Range("G4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("HISTORY LOG ").Select
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Delivery").Select
Range("E1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("HISTORY LOG ").Select
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Delivery").Select
Range("J26:L26").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("HISTORY LOG ").Select
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3").Select
End Sub

Many thanks

Joe

 

[DanteAmor]

Try this.

This works for all the sheets in your book, just write in the macro, on this line which sheets you want to consider.

Case "Delivery", "Sheet2", "Sheet3", "Etc"

Sub HistoryBTF()
  Dim sh As Worksheet, lr As Long
  With Sheets("HISTORY LOG ")
    For Each sh In Sheets
      Select Case sh.Name
        Case "Delivery", "Sheet2", "Sheet3", "Etc"
          lr = .Range("A" & Rows.Count).End(3).Row + 1
          .Range("A" & lr).Value = sh.Range("K4")
          .Range("B" & lr).Value = sh.Range("K5")
          .Range("C" & lr).Value = sh.Range("G4")
          .Range("D" & lr).Value = sh.Range("E1")
          .Range("E" & lr).Value = sh.Range("J26")
      End Select
    Next
  End With
End Sub

 

[JPMarr]

Thank you Dante Amor!
Worked perfectly
unfortunately I learned nothing however at least I can have a stab at working out what each bit means for next time

Cheers

 

[DanteAmor]

Glad we could help & thanks for the feedback