Copy Data From One Sheet To Another With VBA

[wavery]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Gaming Month
[/TD]
[TD]Gaming Month Name
[/TD]
[TD]Gaming Year
[/TD]
[TD]Denom
[/TD]
[TD]Game Type
[/TD]
[TD]Jackpot
[/TD]
[TD]Coin In
[/TD]
[TD]CIPUPD
[/TD]
[TD]Coin Out
[/TD]
[TD]Actual Win
[/TD]
[TD]Theo Win
[/TD]
[TD]Handle Pulls
[/TD]
[TD]Days on Floor
[/TD]
[TD]Fee Amt
[/TD]
[TD]Asset Number
[/TD]
[TD]Area
[/TD]
[TD]Section
[/TD]
[TD]Location
[/TD]
[TD]MFG
[/TD]
[TD]THEME
[/TD]
[TD]EPROM
[/TD]
[TD]WPUPD
[/TD]
[TD]LEASE or NOT
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1/1/2018
[/TD]
[TD]2018
[/TD]
[TD]1
[/TD]
[TD]VR
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8.00
[/TD]
[TD]1234
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lease
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2/1/2018
[/TD]
[TD]2018
[/TD]
[TD]5
[/TD]
[TD]VP
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8.00
[/TD]
[TD]4321
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Not Lease
[/TD]
[/TR]
</tbody>[/TABLE]
Hello,
I am new to this message board, so I hope I am posting this in the correct area. Here is what I need to accomplish. I have a workbook that I track our machines asset numbers. We have 800 machines some are leased most are not leased. I keep a running table "DATADump", data is dumped into this table monthly. I want to copy the current "Asset Numbers", "Machine Type" & "Lease Cost" for Lease Machines only, from "DATADump" to another worksheet named "Leased Machines". I hope I explained this ok.

Thank you,
Wade

 

[alansidman]

Here is a VBA solution. I made two assumptions.
1. Your data starts in Column A
2. Your headers are the same in both sheets

Option Explicit


Sub Leased()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("DATADump")
    Set s2 = Sheets("Leased Machines")
    Dim lr1 As Long, lr2 As Long, i As Long
    lr1 = s1.Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 2 To lr1
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        If InStr(s1.Range("W" & i), "Lease") = 1 Then
            s1.Range("A" & i & ":W" & i).Copy s2.Range("A" & lr2 + 1)
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "Complete"
End Sub

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[mumps]

I can see "Asset Numbers" but in which columns are "Machine Type" & "Lease Cost"? Where on the "Leased Machines" sheet do you want to paste the data?

 

[wavery]

Thank you for your replies.

mumps: It is "Game" Type" sorry. I would like for it to be in order "Asset Number", "Game Type", "Lease Cost", also I would like to run it for a "Gaming Month Name" example 5/1/2018. I have a worksheet in this workbook "REPORTDATE" that all worksheets use the date posted in cell C2. Keep in mind that my DATADump Table has 14,000 Rows of Data. I am not sure if that matters? Please let me know if you need any more Info.

Thank you,
Wade

 

[wavery]

Alansidman,
I tried the code and it retuned no Data. I would also like for it to run with a "Gaming Month Name" Example 5/1/2018. Please let me know f you need any more info.

 

[mumps]

I assume that "Lease Cost" is "Fee Amt". Is that correct? Also, what do you mean by:

I would like to run it for a "Gaming Month Name" example 5/1/2018

 

[wavery]

Yes "Lease Cost" is "Fee Amount". "Gaming Month Name" is the month date, 1/1/2018, 2/1/2018, 3/1/2018 and so on. All Asset Numbers for any given month will have the same "Gaming Month Name" 1/1/2018, 2/1/2018, 3/1/2018 and so on. The Database where I get my monthly data list it this way??? I hope that helps

Thank you,
Wade

 

[mumps]

Are you saying, then, that you want to copy the data based on a certain month of the year only for those machines that are leased? If this is correct, how do you decide which month?

 

[wavery]

I am saying that I need the data based on a certain month of the year. The month is decided by the date entry on worksheet "REPORTDATA" of this workbook in cell C2. I guess we could have the macro ask for a date?

 

[mumps]

You have a column which contains the "Year". Is it possible that you can have data spanning more than a one year period? For example, can you have data for 2 or more years? If so, then the month criteria will return data for more than one year. Please clarify.