Copy Data From One Sheet To Another With VBA

wavery

New Member
Joined
Jun 29, 2018
Messages
25
Gaming Month
Gaming Month Name
Gaming Year
Denom
Game Type
Jackpot
Coin In
CIPUPD
Coin Out
Actual Win
Theo Win
Handle Pulls
Days on Floor
Fee Amt
Asset Number
Area
Section
Location
MFG
THEME
EPROM
WPUPD
LEASE or NOT
1
1/1/2018
2018
1
VR
8.00
1234
Lease
2
2/1/2018
2018
5
VP
8.00
4321
Not Lease

<tbody>
</tbody>
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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

Code:
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
 
Last edited:
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top