Code to copy paste data from one sheet to other sheet in the same workbook

[VanshLucifer]

I am creating a break calculator which will help user to keep a track of his login hours and break time.
I have already created a the excel file
Please find below the format.
[TABLE="width: 395"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]DATE[/TD]
[TD]08/01/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Login Time[/TD]
[TD]09:30:00[/TD]
[TD]Logout Time[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Break Slots[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Total Login[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15 Min slot[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]15 Min slot[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]30 Min slot[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]Bio break[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]Bio break[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Total Break Time[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Total Login [/TD]
[TD]###########[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Actual login Time[/TD]
[TD]###########[/TD]
[/TR]
</tbody>[/TABLE]

It should not copy the entire numbers just the below details
[TABLE="width: 583"]
<tbody>[TR]
[TD]DATE[/TD]
[TD]LOGIN TIME[/TD]
[TD]LOGOUT TIME[/TD]
[TD]TOTAL BREAK TIME[/TD]
[TD]ACTUAL LOGIN HOURS[/TD]
[TD]DOWNTIME[/TD]
[/TR]
</tbody>[/TABLE]

The pasting range strats from B2 to G2

I need a VBA code to which will paste the above details in the other sheet
I will be pasting data at the day end i have already created vba whcih will take date from the system.
i just need the code to paste to the other sheet.
and it shld not over right data for every new date it shloud paste data in the below cell
say for example it for 8th jan it pasting the data from B2 to G2 range
for 9th jan it should paste data from B3 to G3 range.

urgent help required.

Please help.!!

 

[frank_AL]

Re: Need a vba code to copy paste data from one sheet to other sheet in the same workbook

I believe I can help you but would like further clarification by answering the questions below:

1. For the entry screen, are columns A through D used?
2. Does entry start with Date in Cell A1 and each entry below in row 2 through 8 without and rows skipped?
3. Is Total Break Time summed in cell D9?
4. Is Actual Login Hours taken from cell D10 or does it need to be calculated?
5. Where does "DOWNTIME" come from? Is it Total Login minus Actual Login Time?

Answer those questions and I can get you a solution pretty quickly.

Frank

 

[VanshLucifer]

Re: Need a vba code to copy paste data from one sheet to other sheet in the same workbook

1ANS- NO
2ANS-NO
3ANS-Total Break Time summed in cell K21
4ANS-Its taklen from K24 formula is already applied there
5ANS-I need to add down Time start down time cell would be I22 and end cell would be J22 and J22-I22 will be saved in K22
The date is picked form I11 Cell Login Time I12 Logout Time K12 Total break time K21 Actual login is K24 and Down time will be picked up from K22 Cell

Thanks frank for such a promt reply
appreciate that hope i have answered all your questions please get back to me if you need any more further clarifications.

 

[frank_AL]

Re: Need a vba code to copy paste data from one sheet to other sheet in the same workbook

Also, please provide the Sheet Names for the sheet with the form and the sheet where data is to be stored.

 

[frank_AL]

Re: Need a vba code to copy paste data from one sheet to other sheet in the same workbook

Okay, assuming the form is on Sheet1 and the data is to be copied to Sheet2 I believe this code will work for you.

After you copy the code into a module you can assign a Shortcut key so that you can execute the code with a couple of combined keystrokes.

Let me know if this works for you or if any changes are needed.

Option Explicit


Sub CopyData()


Dim cfws As Worksheet
Dim ctws As Worksheet
Dim nextrow As Long


Set cfws = Sheet1
Set ctws = Sheet2


With ctws
    nextrow = ctws.Cells(ctws.Rows.Count, "B").End(xlUp).Row + 1
End With


' Copy data
cfws.Range("I11").Copy Destination:=ctws.Cells(nextrow, "B")
cfws.Range("I12").Copy Destination:=ctws.Cells(nextrow, "C")
cfws.Range("K12").Copy Destination:=ctws.Cells(nextrow, "D")
cfws.Range("K21").Copy Destination:=ctws.Cells(nextrow, "E")
cfws.Range("K24").Copy Destination:=ctws.Cells(nextrow, "F")
cfws.Range("K22").Copy Destination:=ctws.Cells(nextrow, "G")


End Sub

 

[VanshLucifer]

Re: Need a vba code to copy paste data from one sheet to other sheet in the same workbook

FORM is MASTER and where data needs to be stored is RAWDATA

 

[VanshLucifer]

Re: Need a vba code to copy paste data from one sheet to other sheet in the same workbook

THE CODE IS WORKING AMAZINGLY FINE BUT K16 TO K20 AND THE FORMULA IS APPLIED ON k20 AS SUM OF(K16:K20) ITS GIVING ME #REF ERROR WHEN THE CODE IS COPYING THE DATA IN SHEET2 AND ACTUAL LOGIN HOURS IS K23-K21 AND THE RESULT IS K24 WHEN THE CODE IS PASTING IT IN RAWDATA ITS GIVING GIVING ME ERROR #VALUE HOW CAN I SORT THIS?

 

[frank_AL]

Re: Need a vba code to copy paste data from one sheet to other sheet in the same workbook

That's my mistake. You told me there were formulas in those cells and the way I copied copied the formula not the value.

Corrected code is below. I've also utilized your Worksheet names you provided in this version.

Option Explicit


Sub CopyData()


Dim cfws As Worksheet
Dim ctws As Worksheet
Dim nextrow As Long


Set cfws = Worksheets("Master")
Set ctws = Worksheets("RAWDATA")


With ctws
    nextrow = ctws.Cells(ctws.Rows.Count, "B").End(xlUp).Row + 1
End With


' Copy data
ctws.Cells(nextrow, "B") = cfws.Range("I11").Value
ctws.Cells(nextrow, "C") = cfws.Range("I12").Value
ctws.Cells(nextrow, "D") = cfws.Range("K12").Value
ctws.Cells(nextrow, "E") = cfws.Range("K21").Value
ctws.Cells(nextrow, "F") = cfws.Range("K24").Value
ctws.Cells(nextrow, "G") = cfws.Range("K22").Value
Range(ctws.Cells(nextrow, "C"), ctws.Cells(nextrow, "G")).NumberFormat = "[hh]:mm"


End Sub

 

[VanshLucifer]

Re: Need a vba code to copy paste data from one sheet to other sheet in the same workbook

Splendid Frank thanks for your help Cheers Mate!!

 

[VanshLucifer]

Re: Need a vba code to copy paste data from one sheet to other sheet in the same workbook

Hi Frank hope you fine i have a question as am new to VBA am good with JAVA am planning to create a automatic roster VBA file which will assign shifts to the advisors with some particular conditions like no advisor should work for more then 7 days shld have atleast one week off's on sat and sun and user shld have a same shift for almost two weeks and should have split offs if he has got a sat sunday of i know this is possible in java but i have no idea about VBA is this possible frank and if it will you be able to help me with this??

There are more conditions to the roster if this is possible can we go ahead with this??