Copy data from one workbook to another after comparing date of entry

Question

I want to copy data from one workbook to another by comparing current system date.

Billings ECCS is the file I want to copy my data from to other workbook called New, and the worksheet is called Billing Details.

I want to copy only those data which belong to the current date of system. The Billing ECCS workbook contains worksheet called Billing List in which the first column is Date of Entry.

I want to compare that date with the systems current date and then copy the corresponding data to the other worksheet by finding the next empty row.

Option Explicit
Sub SendToBilling()
Option Explicit
Sub Macro2()
Dim LastRow As Long
Dim eRow As Long
Dim i As Long
Dim wbMaster As Workbook
Set wbMaster = Workbooks.Open("file:///C:\Users\mrisingh\Desktop\Billing.xlsx")
With wbMaster.Worksheets("Billing Sheet")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Cells(i, 1) = Date Then
Range(Cells(i, 1), Cells(i, 6)).Select
Selection.Copy  
eRow = Worksheets("Billing Details").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(eRow, 1).Select
ActiveSheet.Paste
End If
Next i
End With    
End Sub

Please understand that Stack Overflow is not a free code writing service. Please read [ask] and [No attempt was made](http://idownvotedbecau.se/noattempt/) to edit and improve your question. Show what you already have tried and ask a proper question to it. — Pᴇʜ, Jun 12 '18 at 08:16
@Pᴇʜ I've updated my problem. Kindly look into it. Thanks — Mrinal Singh, Jun 12 '18 at 11:09
Your code doesn't look too bad. What errors do you get? What goes wrong? • Some things I see: `wbMaster.Range` doesn't work because a workbook doesn't have a range. Instead use `.Range` the leading dot makes the range use the sheet from the `With` statement. All other `Range` and `Cells` should start with a dot too to use the `With` statement. Instead of `ActiveSheet` specify a sheet by its name like `Worksheets("New")`. Also have a look at: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Jun 12 '18 at 11:21
And don't use `Integer` for row counting. Excel has more rows than `Integer` can handle. It is recommended [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA as there is no benefit in using Integer at all. — Pᴇʜ, Jun 12 '18 at 11:25
Dim LastRow As Long Dim eRow As Long Dim i As Long Dim wbMaster As Workbook Set wbMaster = Workbooks.Open("file:///C:\Users\mrisingh\Desktop\Billing.xlsx") With wbMaster.Worksheets("Billing Sheet") LastRow = .Range("A" & Rows.Count).End(xlUp).Row For i = 2 To LastRow If Cells(i, 1) = Date Then Range(Cells(i, 1), Cells(i, 6)).Select Selection.Copy eRow = Worksheets("Billing Details").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ActiveSheet.Cells(eRow, 1).Select ActiveSheet.Paste End If Next i End With — Mrinal Singh, Jun 12 '18 at 11:43
① Code in comments is not readable please always add code in your original question. ② If you don't tell which errors you get or what exactly is going wrong we cannot help. — Pᴇʜ, Jun 12 '18 at 11:45
@Pᴇʜ I've made the suggested changes. There's no error coming but it is also not working. It just opens the other workbook and the respective worksheet I want to copy my data from. — Mrinal Singh, Jun 12 '18 at 11:50

score 0 · Accepted Answer · answered Jun 12 '18 at 11:47

0

You need to be explicit about which sheet is which

Sub SendToBilling()

    Dim LastRow As Long
    Dim eRow As Long
    Dim i As Integer

    Dim wbMaster As Workbook
    Set wbMaster = Workbooks.Open("C:\Users\mrisingh\Desktop\Billing ECCS.xlsx")

   Dim SourceSheet as worksheet
   Dim DestinationSheet as worksheet  

   Set Sourcesheet = wbmaster.worksheets("Billing List")
   set destinationsheet = Workbooks("New").worksheets("Billing Details")

'I think I've got these the right way round? Is your workbook really called NEW?


        LastRow = destinationsheet.Range("A" & Rows.Count).End(xlUp).Row
        For i = 2 To LastRow
            If sourcesheet.Cells(i, 1) = Date Then
                 eRow = destinationSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                sourcesheet.Range(Cells(i, 1), Cells(i, 6)).copy destinationsheet(erow,1)


              End If
        Next i
    End With  
End Sub

answered Jun 12 '18 at 11:47

Harassed Dad

4,669
1
10
12

What are your sheets called - which is the source and which the destination? You wrote " to other workbook called New, and the worksheet is called Billing Details." – Harassed Dad Jun 12 '18 at 12:16
I want to copy from Workbook called "Billing ECCS" and worksheet called "Billing Sheet" to Workbook called "New" and worksheet called "Billing Details" – Mrinal Singh Jun 12 '18 at 12:19
Format: Workbook>Worksheet, Source: Billing ECCS>Billing Sheet, Destination: New>Billing Details – Mrinal Singh Jun 12 '18 at 12:21
Change to Set Sourcesheet = wbmaster.worksheets("Billing sheet") Though your original question wrote " The Billing ECCS workbook contains worksheet called Billing List " – Harassed Dad Jun 12 '18 at 12:26
I'm sorry for that but the required changes have been made. Still it's just opening the source sheet, i.e., Billing Sheet and not working further. – Mrinal Singh Jun 12 '18 at 12:30
use F8 to step through the code and see where it stops - are you sure there's some data to be copied? – Harassed Dad Jun 12 '18 at 16:08

Copy data from one workbook to another after comparing date of entry

1 Answers1