Filling in a history log table from several sheets

JPMarr

New Member
Joined
Apr 25, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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;

VBA Code:
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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.

VBA Code:
Case "Delivery", "Sheet2", "Sheet3", "Etc"

VBA Code:
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

New Member
Joined
Apr 25, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,127,990
Messages
5,628,001
Members
416,286
Latest member
ko15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top