Macro to Copy/Paste data from one Sheet to Another

[Seibs90]

Hi All,

Trying to eliminate some unnecessary data entry here at work with some macros. I'm trying to create a document that will track volume levels in tanks at our plant. I've got one tab that I call "Tank Gauges - Current" where today's tank information is entered and another one called "Tank Gauges - History" where ideally I'd like a history of the daily tank levels to be. I've looked up plenty of similar questions to this but I think that my lack of experience with VBA is really hindering me here. I'm not sure how to tweak the codes the others had to fit my needs... So that's why I'm here. Here's what I want to be able to do:

• Once someone enters the data on the "Tank Gauges - Current" tab I want to have a macro that will copy that data and paste the formats/values onto the next empty row in the "Tank Gauges - History" tab. After that I would love for the data that was entered on the "Tank Gauges - Current" tab to be deleted (pretty sure I can do this on my own, so I'm not too concerned about it)

I know this can be done, but I'm just looking for some help in doing it! If anyone can help or provide a link with something that might work it would be much appreciated!

 

[Grasor]

Some more information on the structure of your sheet would be helpful. How is the first tab structured? How many tanks are we talking about? How does the second sheet look?

 

[dreid1011]

This is exactly what I just made for some tax exemptions I wanted to track. The first sub copies the data from the input cells to the next empty row in the list on the second sheet. Then, it will clear the fields after moving the data. The second sub is a clear button if just want to clear the fields without adding it to the list.

Sub Record_Click()
    Dim dataIn(0 To 9) As String
    Dim dataOut(0 To 9) As String
    Dim i As Integer
    i = 0
    dataIn(0) = "B5"
    dataIn(1) = "D5"
    dataIn(2) = "B8"
    dataIn(3) = "F8"
    dataIn(4) = "M22"
    dataIn(5) = "D11"
    dataIn(6) = "M23"
    dataIn(7) = "M24"
    dataIn(8) = "B14"
    dataIn(9) = "D14"
    dataOut(0) = "B"
    dataOut(1) = "C"
    dataOut(2) = "D"
    dataOut(3) = "E"
    dataOut(4) = "F"
    dataOut(5) = "G"
    dataOut(6) = "H"
    dataOut(7) = "I"
    dataOut(8) = "J"
    dataOut(9) = "K"
    Range("M22").Value = cboLetter.Value
    Range("M23").Value = cboExAmt.Value
    Range("M24").Value = cboNew.Value
    lr = Worksheets("Vet List").Range("B" & Rows.Count).End(xlUp).Row + 1
    For i = 0 To 9
    Range(dataIn(i)).Copy Worksheets("Vet List").Cells(lr, dataOut(i))
    Next
    MsgBox "Record Added"
    Call Reset_Click
End Sub


Sub Reset_Click()
    Dim dataIn(0 To 9) As String
    Dim i As Integer
    dataIn(0) = "B5"
    dataIn(1) = "D5"
    dataIn(2) = "B8"
    dataIn(3) = "F8"
    dataIn(4) = "M22"
    dataIn(5) = "D11"
    dataIn(6) = "M23"
    dataIn(7) = "M24"
    dataIn(8) = "B14"
    dataIn(9) = "D14"
    i = 0
    For i = 0 To 9
    Range(dataIn(i)).Value = ""
    Next
    cboLetter.Value = ""
    cboExAmt.Value = ""
    cboNew.Value = ""
End Sub

My entry form is mainly text input with a few combo boxes.

 

[Seibs90]

Here's what the sheet looks like. Both the Current and History tabs look identical, just looking to paste everything past the column titles down (A6:K78). There are a total of 73 tanks (or things I need to keep track of) on site.

Date	Tank Number	Tank Table	Product	Temperature	Feet	Inches	1/4 Inches	Liquid Gallons	Vapor Gallons	Total Gallons
6/5/2015	1
6/5/2015	2
6/5/2015	3
6/5/2015	4
6/5/2015	5
6/5/2015	6
6/5/2015	7
6/5/2015	8
6/5/2015	9

<tbody>
</tbody>

 

[Grasor]

How does this work for you? Link "CopyToHistory" subroutine to a command button or hot key. Requires the following workbook structure:

Two Worksheets Named:

Tank Gauges - Current
Tank Gauges - History

Two Named Ranges:

On "Tank Gauges - Current" Tab: Range $A$2:$A$1000 named "CurrentRange"

On "Tank Gauges - History" Tab: Range $A:$A named HistoryHeader

Headers on Each Worksheet as follows (Modify titles as necessary):

Date

Tank Number

Tank Table

Product

Temperature

Feet

Inches

1/4 Inches

Liquid Gallons

Vapor Gallons

Total Gallons

<tbody>
</tbody>

You may also Download Workbook from dropbox.

Option Explicit


Type TankInfo
    RecordDate As Date
    TankID As Integer
    TankTable As String
    TankProduct As String
    TankTemp As Variant 'Convert to decimal with CDec() later.
    TankFeet As Integer
    TankInch As Integer
    TankQtrInch As Integer
    LiquidGallons As Integer
    VaporGallons As Integer
    TotalGallons As Integer
End Type


Sub CopyToHistory()


Call BuildArray


End Sub


Sub BuildArray()


'Activate Correct Sheet to Start
ActiveWorkbook.Sheets("Tank Gauges - Current").Activate


'Build Array of Data on Current Tab.


Dim CurrentRange, Cell As Range


'TankData Array.  Start without parameters so it can be redimensioned as needed.
Dim TankData() As TankInfo
ReDim TankData(0 To 0)
Dim ArrElement As Integer


'Fills array from Row 2 through Row 1000 (for future expansion).
'Note: Requires an entry in date column to be detected
Set CurrentRange = Range("A2:A1000")


'Fill Array with Data
For Each Cell In CurrentRange
    If Cell <> "" Then
        ReDim Preserve TankData(LBound(TankData) To ArrElement)
        TankData(ArrElement).RecordDate = Cell.Value
        TankData(ArrElement).TankID = Cell.Offset(0, 1)
        TankData(ArrElement).TankTable = Cell.Offset(0, 2)
        TankData(ArrElement).TankProduct = Cell.Offset(0, 3)
        TankData(ArrElement).TankTemp = CDec(Cell.Offset(0, 4))
        TankData(ArrElement).TankFeet = Cell.Offset(0, 5)
        TankData(ArrElement).TankInch = Cell.Offset(0, 6)
        TankData(ArrElement).TankQtrInch = Cell.Offset(0, 7)
        TankData(ArrElement).LiquidGallons = Cell.Offset(0, 8)
        TankData(ArrElement).VaporGallons = Cell.Offset(0, 9)
        TankData(ArrElement).TotalGallons = Cell.Offset(0, 10)
        ArrElement = ArrElement + 1
    End If
Next Cell


CopyOver TankData




End Sub
    
Sub CopyOver(ByRef TankData() As TankInfo)


'Activate History Sheet
ActiveWorkbook.Sheets("Tank Gauges - History").Activate


Dim ArraySize, i As Integer
ArraySize = UBound(TankData) + 1


'Insert enough rows to handle new data.  Is based on "..Current" tab entries with dates. Entries without dates are skipped.
Range(Range("HistoryHeader").Offset(1, 0), Range("HistoryHeader").Offset(ArraySize, 0)).EntireRow.Insert


'Paste data from array to new rows on history tab.
For i = 0 To UBound(TankData)
        Range("HistoryHeader").Offset(1 + i, 0) = TankData(i).RecordDate
        Range("HistoryHeader").Offset(1 + i, 1) = TankData(i).TankID
        Range("HistoryHeader").Offset(1 + i, 2) = TankData(i).TankTable
        Range("HistoryHeader").Offset(1 + i, 3) = TankData(i).TankProduct
        Range("HistoryHeader").Offset(1 + i, 4) = TankData(i).TankTemp
        Range("HistoryHeader").Offset(1 + i, 5) = TankData(i).TankFeet
        Range("HistoryHeader").Offset(1 + i, 6) = TankData(i).TankInch
        Range("HistoryHeader").Offset(1 + i, 7) = TankData(i).TankQtrInch
        Range("HistoryHeader").Offset(1 + i, 8) = TankData(i).LiquidGallons
        Range("HistoryHeader").Offset(1 + i, 9) = TankData(i).VaporGallons
        Range("HistoryHeader").Offset(1 + i, 10) = TankData(i).TotalGallons
Next i


'Set formatting to include thin black borders and size 10 regular Verdana font.
Range(Range("HistoryHeader").Offset(1, 0), Range("HistoryHeader").Offset(ArraySize, 10)).Select
  With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    
    With Selection.Font
        .Name = "Verdana"
        .Size = 10
        .Bold = False
    End With


End Sub

 

[Seibs90]

I appreciate the effort for this! I tried it in my spreadsheet but I kept getting the Run-time error 13. I tried going through the code to debug it and fix things but it's all still a little bit over my head... I'm going to download the workbook you put on dropbox though and see where I can get with that. I'm starting to think now though that if I'm not really able to understand how the macro is put together that maybe it's not the best idea for me to be using macros. At least in this workbook, I'm a little afraid if something goes wrong and I'm not able to fix it... Might just be easier to put all of the tabs where data is entered in the right format and avoid the whole current tab versus history tab...

 

[Grasor]

Runtime 13 Error means you are assigning a value to a data type that isn't meant to handle it. For instance, a variable declared as an integer cannot accept a string. Try the workbook and see if you can use that. If not, you might have to play with the code a bit to make it work. You can try changing all declarations to type Variant which might alleviate the issue. A Variant type can accept all data types but is slower as the project gets bigger. You may not notice a difference for your application. I'm here to help, let me know if you need any more help.
/