• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 908
  • Last Modified:

Copy data from sheet1 to sheet2

Hi Experts,

Request Experts help to create a macro which is able to  transfer the data automatically from sheet 1 to to sheet 2 when we click command button “Transfer Data”. The data are copied according to the type (header). I have created sets of sample data in sheet2 for Experts to get a better view. I hope Expert could help me to create this.

Timecode-test1.xls
0
Theva
Asked:
Theva
  • 26
  • 22
  • 3
  • +1
2 Solutions
 
tilsantCommented:
hi Theva,
your this file and the file attached in your previous post (which is now deleted) are both same.... right?
and your data needs to be transferred at the end of your previous data on sheet 2......right?
will the no. of "titles" remain same or variable??


regards,
tils.
0
 
ThevaAuthor Commented:
Hi,

Yes, its same. I thought the Q has confusing  Experts, therefore I revised it. Sorry for the inconvenience caused.  
0
 
tilsantCommented:
chk this one out.
im not so sure if this is what is required by you.


tils.
Copy-of-Timecode-test1-1.xls
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Curt LindstromCommented:
Hi Theva,

Try this one.
A slightly different approach with less code. I haven't added any formatting.

Regards,
Curt
Timecode-test2.xls
0
 
ThevaAuthor Commented:
Hi,

Almost there, we need to copy the whole timecode (TC) ##:##:##:##:##. Please allow us to transfer more data. I have attaches the sample data for you to get better view.
Copy-of-Timecode-test1-1.xls
0
 
ThevaAuthor Commented:
Hi epaclm,

Look great but, please include all digits (00:00:00:00) and also add line at the total duration.
0
 
tilsantCommented:
hope this'll do.....
Copy-of-Timecode-test1-2.xls
0
 
tilsantCommented:
thers is still some problem with the code where filter is applied

    Range("G1").Select
    Selection.AutoFilter Field:=7, Criteria1:="="

is there any way with which u can remove this "Field:=7" and make it only criteria based??.... it'll work properly then..
0
 
ThevaAuthor Commented:
Hi tilsant,

We need 4 sets of digits to complete the timecode (TC) which is hh:mm:ss:ff. Without ":ff" its kind of impairment when we imposed editing a movie or film. I hope we can add "ff"   when transferring this data.
0
 
tilsantCommented:
u mean to say we have to add B3 n C3......B4 + C4.........etc....??
0
 
tilsantCommented:
btw........ excel does not allow Time format beyond hh:mm:ss!!
how are u planning to go about then??
0
 
porearCommented:
Hello, tilsant.  I thought the same thing.  It obviously can be made into a string and the :ff characters concatenated, but any future time based calcuations would have to split to convert back to time and do more complex manipulations to deal with the added digits.
0
 
tilsantCommented:
yea true porear.....concatenation will make it into "text" which wont be of any use while calculations....
........i guess we'll have to wait for theva's view on this!
0
 
ThevaAuthor Commented:
Hi,

The time calculating (TC) has been made in sheet-1, we not going to do anymore calculation in sheet-2. What we'd done in sheet-1 is consider finalized value and its OK if we save it the value in text format as long as its indicate the TC as ##:##:##:##.
0
 
tilsantCommented:
hey theva,
Me not being a VB Programmer would suggest an addition of sheet3 which would be linked to sheet1, the only difference being that the values in sheet3 on cell B3 would be your concatenated value in the form "hh:mm:ss:ff".
And, the macro will take values from sheet3 instead of sheet1 and transfer it on Sheet2.
But since u dont want anymore calculation involved, i doubt if i could be able to help any further.


Best Luck,
tils.
0
 
ThevaAuthor Commented:
Hi tilsant,

Any chance for you to send me the sample file. I would like to see it. Can we hide the sheet-3 works at background.
0
 
tilsantCommented:
i can do that part but there exists an issue with my Data Transfer Macro.......that i mentioned it before:

{
there is still some problem with the code where filter is applied

    Range("G1").Select
    Selection.AutoFilter Field:=7, Criteria1:="="

is there any way with which u can remove this "Field:=7" and make it only criteria based??.... it'll work properly then..
}


if u can deal with this one then the rest can be assured.


tils.
0
 
ThevaAuthor Commented:
Hi,

OK, please remove this field-7.
0
 
tilsantCommented:
chk out this one (sheet3)...........this one doesnt need a macro...... its formula based.
i have put the formula for around 100 rows and conditional formatting too..
Copy-of-Timecode-test1-2.xls
0
 
tilsantCommented:
and ya there is no problem with the "field-7" thingy............its not bothering at all........jus a misconception of mine ;)
0
 
ThevaAuthor Commented:
Hi,

Looks cool! as you mentioned >" the macro will take values from sheet3 instead of sheet1 and transfer it on Sheet2." Can we do this in sheet 2 and hide sheet-3 from users. Hope this is possible.
0
 
ThevaAuthor Commented:
Hi,

>>>and ya there is no problem with the "field-7" thingy............its not bothering at all........jus a misconception of mine ;)

It does actually, any value of "ff" showed as "00".
0
 
ThevaAuthor Commented:
Hi,

Shall we use this instead:

,":",TEXT(sheet1!C32,"@")))
0
 
tilsantCommented:
im unable to Hide the Sheet3..........for some reason if i hide the sheet3 then the macro runs into an error........
as of now jus try ignoring the sheet3 and run the macro if this works out then we might think of having something to replace the sheet3.
and its late night here........so'll talk 2moro...
bbye



tils.
Copy-of-Timecode-test1-2.xls
0
 
ThevaAuthor Commented:
Hi,

OK, thanks for your help, I need to let my eyes rest too, mine is 2:55am zzzzzzzzzzzzzzzzz.
0
 
tilsantCommented:
hi theva........wassa!!
hows the last sheet tht i'd sent across??
0
 
tilsantCommented:
jus worked out this one in the morning now........and it works ;)
i would suggest u to put some more data and try experimenting do find out errors, if any.


tils.
Copy-of-Timecode-test1-2.xls
0
 
ThevaAuthor Commented:
Hi Good Morning,

Thanks for the file. Have checked the data, any chance for us to refine/rectify these:

1). kept the whole data in sheet-2 and update the new data at new row. We'll treat sheet 1 as working template and archived all TC information in sheet-2. Sheet-3 obviously act as "converter"  
2) Noticed ff in sheet-2 always with 00 event though we've typed frames(:01-:24) in sheet-1.
0
 
tilsantCommented:
Step1 that u want can be done i guess (i havent tried it yet though).
And for Step2 u can change the formula in Sheet3.........for e.g. in sheet3..... cell:C2=IF(sheet1!B3=0,"",CONCATENATE(TEXT(sheet1!B3,"hh:mm:ss"),":",TEXT(sheet1!C3,"ss")))

instead of this u can use:
C2=IF(sheet1!B3=0,"",CONCATENATE(TEXT(sheet1!B3,"hh:mm:ss"),":",TEXT(sheet1!$K$1,"ss")))


Sheet1!K1 is the correct value to be used......right??
0
 
ThevaAuthor Commented:
Hi tilsant,

How to open sheet-3?
0
 
tilsantCommented:
go to VB Mode
select the Sheet3 in Project Explorer
go to properties window and change the "visible" option to "xlSheetVisible"


untitled.JPG
0
 
ThevaAuthor Commented:
Hi,

Thanks.
0
 
tilsantCommented:
hi.......
is the thing done.......?? wht more modifications are u planning to do in that??
0
 
ThevaAuthor Commented:
Hi,

Tried the formula but doesn't work. No other modification needed except the  
"1). "
0
 
tilsantCommented:
"sheet1!K1" is the value to be used for "ff" right??
0
 
ThevaAuthor Commented:
Hi,

Yes you're right.
0
 
tilsantCommented:
check out this one....
Copy-of-Timecode-test1-3.xls
0
 
ThevaAuthor Commented:
Hi,

Can we retain the old TC information in sheet-2. Let say I clear all content in sheet-1 and starts with new TC line up. When we transfer this data the new data shouldn't override the previous data. The new data will use new row instead. Sorry if my previous explanation create confusion.
0
 
tilsantCommented:
Hi Theva,
do u need the old data in sheet2 to move down or the new data to come below the old data in sheet2??
0
 
tilsantCommented:
and is the "ff" issue solved now??
0
 
ThevaAuthor Commented:
Hi,

Not really, in this case I prefer your old formula. I've tested with this formula "TEXT(sheet1!C32,"@")))" but the problem is its not showing 00. And if we have 01 it showed as 0. But this formula works well if the frame is starts from :10.
0
 
tilsantCommented:
use "0#" instead of "@" .........this works for both single digit and double digit numbers!!
for 1 it gives 01 and for 24 it gives 24.......the latest sheet attached by me has implementation of "0#"


tils.
0
 
ThevaAuthor Commented:
Hi,

Please check this file. I'm using previous formula with "#0". Any idea how to show "0" as "00".
Copy-of-Timecode-test1-3.xls
0
 
tilsantCommented:
use "00" instead of "0#"  ..........it works!!
0
 
ThevaAuthor Commented:
Hi,

Cool! Thanks a lot. Hope you create sheet-2 in such way to kept previous data and allow new entry without overriding old data.
0
 
Curt LindstromCommented:
Hi Theva,

Try this one. Notice that sheet 2 is empty. Press button check sheet 2. Go back and press button again and check sheet2. You will find that sheet 2 will keep all transferred data at the desired format. I have not added the lines.

The code is stored in module 1 and can also be seen in the code snippet.

Regards Curt
Sub Button17_Click()
    Dim i As Long, LastRow2 As Long
    Dim Ws1 As Worksheet, Ws2 As Worksheet
    Dim strReturn As String
    Dim objCell As Range
    
    Set Ws1 = Worksheets("Sheet1")
    Set Ws2 = Worksheets("Sheet2")
    LastRow2 = xlLastRow("Sheet2") + 1
    For i = 3 To xlLastRow
        If i = 3 Then
            Ws2.Cells(LastRow2, 6) = Ws1.Cells(i - 1, 1)
        End If
        Ws2.Cells(LastRow2, 1) = Ws1.Cells(i, 1)
        Ws2.Cells(LastRow2, 3) = Format(Ws1.Cells(i, 2), "hh:mm:ss") & ":00"
        Ws2.Cells(LastRow2, 4) = Format(Ws1.Cells(i, 4), "hh:mm:ss") & ":00"
        Ws2.Cells(LastRow2, 5) = Format(Ws1.Cells(i, 6), "hh:mm:ss") & ":00"
        If i = xlLastRow Then
            LastRow2 = LastRow2 + 1
            Ws2.Cells(LastRow2, 7) = Format(Ws1.Cells(i, 10), "hh:mm:ss")
        End If
        LastRow2 = LastRow2 + 1
    Next
End Sub
Function xlLastRow(Optional WorksheetName As String) As Long

'Check for optional worksheetname else use activesheet
    If WorksheetName = vbNullString Then
        WorksheetName = ActiveSheet.Name
    End If

    '    find the last populated row in a worksheet
    With Worksheets(WorksheetName)
        xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
                                xlWhole, xlByRows, xlPrevious).Row
    End With

End Function

Open in new window

Timecode-test3.xls
0
 
tilsantCommented:
Hi Theva,
do u need the old data in sheet2 to move down or the new data to come below the old data in sheet2??


tils.
0
 
Curt LindstromCommented:
Hi Theva,

This one will transfer the frames correctly as well.

Regards,
Curt
Sub Button17_Click()
    Dim i As Long, LastRow2 As Long
    Dim Ws1 As Worksheet, Ws2 As Worksheet
    
    Set Ws1 = Worksheets("Sheet1")
    Set Ws2 = Worksheets("Sheet2")
    LastRow2 = xlLastRow("Sheet2") + 1
    For i = 3 To xlLastRow
        If i = 3 Then
            Ws2.Cells(LastRow2, 6) = Ws1.Cells(i - 1, 1)
        End If
        Ws2.Cells(LastRow2, 1) = Ws1.Cells(i, 1)
        Ws2.Cells(LastRow2, 3) = Format(Ws1.Cells(i, 2), "hh:mm:ss") & ":" & Format(Ws1.Cells(i, 3), "00")
        Ws2.Cells(LastRow2, 4) = Format(Ws1.Cells(i, 4), "hh:mm:ss") & ":" & Format(Ws1.Cells(i, 5), "00")
        Ws2.Cells(LastRow2, 5) = Format(Ws1.Cells(i, 6), "hh:mm:ss") & ":" & Format(Ws1.Cells(i, 7), "00")
        If i = xlLastRow Then
            LastRow2 = LastRow2 + 1
            Ws2.Cells(LastRow2, 7) = Format(Ws1.Cells(i, 10), "hh:mm:ss")
        End If
        LastRow2 = LastRow2 + 1
    Next
End Sub
Function xlLastRow(Optional WorksheetName As String) As Long

'Check for optional worksheetname else use activesheet
    If WorksheetName = vbNullString Then
        WorksheetName = ActiveSheet.Name
    End If

    '    find the last populated row in a worksheet
    With Worksheets(WorksheetName)
        xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
                                xlWhole, xlByRows, xlPrevious).Row
    End With

End Function

Open in new window

Timecode-test4.xls
0
 
tilsantCommented:
hey theva......check out this one...... it places the data in sheet2 below the previous data!


tils.
Copy-of-Timecode-test1-4.xls
0
 
ThevaAuthor Commented:
Hi tilsant,

Thanks a lot for your help,really appreciated. I learned a lot from you in this project.
 
Hi Epaclm,
Thanks for the codes, It look even better if you highlight the total duration row.
0
 
tilsantCommented:
hi Theva....
jus thought it wud be important for u to know the following things:
1. Sheet3 is having formulas only till 1001th row.........so if u have more than 1000 rows of data to be transferred at a time then u'll have to drag down the formulas in sheet3.
2. And the macro will run only till Sheet2 has 10,000 rows filled after that it might not work properly.........u can increase its limit by changing the 9th line of the code which contains.......
    Range("H2:H10000").Select

instead of 10000 u can change the value to a lower or upper value as per your requirement. Lower the value >> Faster will be your Macro run time!
But if u plan to have your Sheet2 for more than 10,000 rows then u'll have to change the value accordingly.


Regards,
Tils.
0
 
ThevaAuthor Commented:
Hi tilsant,

Thanks for reminding me, I could overlook this.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 26
  • 22
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now