Need help using VBA to copy data from one sheet into another sheet based on specific date

B1EADDED

New Member
Joined
Nov 28, 2016
Messages
6
Hi all,

I'm very new to VBA and need help copying data from one sheet into another sheet based on a specific date.

I am in charge of invoicing business for the hours employees have worked. This is a large business with a large number of employees and copying and pasting manually takes a lot of time.

I have two sheets. One called 'TIMESHEET' which logs all employees hours that worked. The other called 'DAILY INVOICE' which is in the form of an invoice that shows the client all employees and the hours they worked.

I would like to:
- Enter a specific date in 'DAILY INVOICE' in box I3.
- Excel automatically searches for this date in column B in sheet 'TIMESHEET'
- Excel then copies that data from 'TIMESHEET' for that specific date from columns C, D, E, F, G, H.
- Then paste it into 'DAILY INVOICE' in row 12, column A, (B+C), (D+E+F), G, H, I. (brackets mean the columns are merged). Columns are merged due to invoice information from rows 1 - 10.

The headings for the columns in both sheets are:
ID/BADGE NONAMETRADESTART TIMEEND TIMETOTAL HOURS

<tbody>
</tbody>
These appear in row1, starting at column C, of 'Timesheet', and row12, column A of 'DAILY INVOICE'.

Is this possible?

Any help would be so greatly appreciated!! thank you!!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
So we will only be copying over one row?
You said:
"Then paste it into 'DAILY INVOICE' in row 12,"

Do you mean starting in row 12?
And next time in row 13
 
Upvote 0
Hi all,

I'm very new to VBA and need help copying data from one sheet into another sheet based on a specific date.

I am in charge of invoicing business for the hours employees have worked. This is a large business with a large number of employees and copying and pasting manually takes a lot of time.

I have two sheets. One called 'TIMESHEET' which logs all employees hours that worked. The other called 'DAILY INVOICE' which is in the form of an invoice that shows the client all employees and the hours they worked.

I would like to:
- Enter a specific date in 'DAILY INVOICE' in box I3.
- Excel automatically searches for this date in column B in sheet 'TIMESHEET'
- Excel then copies that data from 'TIMESHEET' for that specific date from columns C, D, E, F, G, H.
- Then paste it into 'DAILY INVOICE' in row 12, column A, (B+C), (D+E+F), G, H, I. (brackets mean the columns are merged). Columns are merged due to invoice information from rows 1 - 10.

The headings for the columns in both sheets are:
ID/BADGE NONAMETRADESTART TIMEEND TIMETOTAL HOURS

<tbody>
</tbody>
These appear in row1, starting at column C, of 'Timesheet', and row12, column A of 'DAILY INVOICE'.

Is this possible?

Any help would be so greatly appreciated!! thank you!!

Hello,

It's absolutely possible.

I do something similar for my machine automation operations LOL. I know two different kettles of fish.

I can give you something that looks for a specific value on 2 seperate worksheets which then will say yes I found it, copies a range from A:F (for example) then pastes into a specific sheet starting at the next blank row found in column A on whatever sheet you specify.

So yes 100% easy and possible.

But just confirm if this is what you're after.

The only thing I won't be sure on in my code is the merged cells.

Luke
 
Upvote 0
Hi,

Yes sorry I mean starting at row 12 and then next in row 13, and so on until all data for that specific date have been filled.

Thanks
 
Upvote 0
Hi Luke,

I think it wont work on merged cells. so I edited the invoice sheet to not have merged cells anymore.

The code i'm using for it now works, its:

Option Explicit


Sub CnP()


Dim nDate, DateRow, FoundDate, NextDate, LastDate, xxx As Range


Set nDate = Sheets("DAILY INVOICE").Range("F3")


Sheets("DAILY INVOICE").Range("A12:E29").ClearContents


DateRow = 1
Do While True
If Sheets("Timesheet").Cells(DateRow, 2).Value = nDate Then Exit Do
DateRow = DateRow + 1
Loop
FoundDate = DateRow


NextDate = FoundDate + 1
Do While True
If Sheets("Timesheet").Cells(NextDate, 2).Value <> nDate Then Exit Do
NextDate = NextDate + 1
Loop
LastDate = NextDate - 1


Sheets("Timesheet").Range(("C" & FoundDate), ("G" & LastDate)).Copy
Sheets("DAILY INVOICE").Range("A12").PasteSpecial Paste:=xlPasteValues


Application.CutCopyMode = False


End Sub

------

but now I'm having the problem that its also copying the blank cells. For example, employees still need to be listed even if they didn't work that day, so their start time and end time will be blank. but they will show up on the invoice.

is there a way for the code to identify to blank cells and not copy them over into the invoice sheet?
 
Upvote 0
Ok I see what you're doing but you're only looking for date.

You'll need something within you're IF statement to say IF ( your statement ) AND cell range ( whatever it is ) is NOT BLANK or is <=0 then Do the procedure.

I would use less than 0 I use it all the time and seems to sort these issues out.
 
Upvote 0
Try this:

Code:
Sub_Row_Dates()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Dim ans As Date
ans = Sheets("DAILY INVOICE").Range("I3").Value
'MsgBox ans
Lastrow = Sheets("TIMESHEET").Cells(Rows.Count, "B").End(xlUp).Row
Lastrowa = 12
    For i = 1 To Lastrow
        If Sheets("TIMESHEET").Cells(i, "B") = ans Then
            Sheets("TIMESHEET").Range("C" & i & ":H" & i).Copy Destination:=Sheets("DAILY INVOICE").Range("A" & Lastrowa)
            Lastrowa = Lastrowa + 1
        End If
    Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thank you for your help, that code didn't work tho. The code I posted above works but I just have the problem of it copying blank columns of the START TIME and END TIME (columns F and G respectively in 'Timesheet'). I'm not sure how to incorporate an IF statement in the code to tell it not to proceed if the START and END time are blank.

Thanks!
 
Upvote 0
Sorry My Aswer IS This!!! The code does work!! Thanks! I was copying it wrong!
Is there a way for it to not copy over blank cells, for example: the Start and End time are blank, therefore the code tells it not to copy these rows over if these are blank.

Thanks!!
 
Upvote 0
In your original post you said:

Excel then copies that data from 'TIMESHEET' for that specific date from columns C, D, E, F, G, H.
I take this to mean copy Columns "C:H"

So tell me what columns you want copied over
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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