Sheet One Items Transferred to Sheet Two.

Closed
srigopal - Mar 3, 2015 at 09:19 PM
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - Mar 10, 2015 at 02:28 AM
I have few items in sheet one and want to transfer to sheet two. Please help me..

Sheet 1 consist of:-

S.no   Tanker     Date        Voucher        Payment via      Amount
2 1325 17/06/2014 1 Direct 1000
9 4433 04/08/2014 2 online 2000
1325 30/08/2014 3 Direct 3000
5555 11/09/2014 4 Direct 4000
1 1325 14/06/2014 5 Direct 5000
7 4433 30/07/2014 6 Cash 6000
8 5555 07/07/2014 7 online 7000
14 4433 09/10/2014 8 direct 8000
6 1325 25/07/2014 9 online 9000
5 7432 16/07/2014 10 Direct 10000
10 4433 09/08/2014 11 Direct 11000
13 1325 21/09/2014 12 Cash 12000
3 4433 27/06/2014 13 Direct 13000
11 5555 11/08/2014 14 Ubi 14000
4 5555 27/06/2014 15 Direct 15000
12 4433 15/09/2014 16 Ubi 16000


I want to make tanker wise sheet, which I update here. I want that to go into respective sheet. Sheet name is tanker number.

Please someone help me.
Related:

3 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 4, 2015 at 12:38 AM
get list of unique tankers using advanced filter

then autofilter data for each unique tanker number and add worksheet with name as tanker number and paste it there (only filtered data will e pasted)
loop for each unique tanker number

try to do it manually (opening "record macro") and then edit the macro
0
I dont know much. can you help me making the sheet as you said. i am not that good in filter and macro options. my id ***@***
0
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Mar 4, 2015 at 01:12 AM
Hello Srigopal,

The following code may do as you ask:-

Sub TransferData()
Application.ScreenUpdating = False
Dim lRow As Long
Dim ws As Worksheet, ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
Set ws = Worksheets("Input")
Set ws1 = Worksheets("1325")
Set ws2 = Worksheets("4433")
Set ws3 = Worksheets("5555")
Set ws4 = Worksheets("7432")

ws.Select
lRow = Range("A" & Rows.Count).End(xlUp).Row

For Each Cell In Range("A2:A" & lRow)
    If Cell = 1325 Then
    Cell.EntireRow.Copy ws1.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    
    ElseIf Cell = 4433 Then
    Cell.EntireRow.Copy ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    
    ElseIf Cell = 5555 Then
    Cell.EntireRow.Copy ws3.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    
    ElseIf Cell = 7432 Then
    Cell.EntireRow.Copy ws4.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
  
Next Cell

Sheets("Input").Range("A2:F" & Rows.Count).ClearContents
MsgBox "Data transfer completed!", vbExclamation
Application.ScreenUpdating = True

End Sub


It transfers all data to each tanker's relevant sheet.

You can have a look at my test work book here:-

https://www.dropbox.com/s/3ctqx2573q10fyz/Srigopal.xlsm?dl=0

to see how it works.

I have moved the "Tanker No." to Column A and the "S. No." to Column B. It just simplified things a little.
The code also clears all data from the "Input" sheet once the transfer of data is complete. This will prevent duplicates in the Tanker sheets but I also assumed that you would not want "used" data cluttering up your "Input" sheet.

I hope that this helps.

Regards,
vcoolio.
0
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Mar 4, 2015 at 01:15 AM
Hello Venkat,

My apologies for intruding. We must have posted at the same time!

I'll leave it with you.

Regards,
vcoolio.
0
thanks that worked a great, i want the same data to be in sheet one also...

thanks a ton for the help. this will also workout.. if the same can be there it will be awesome
0
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262 > srigopal
Mar 4, 2015 at 03:16 AM
Hello Srigopal,

Just delete line 30 from the code and all the data will remain in The "Input" sheet also. But, remember, this will create duplicates in all the Tanker sheets. Do you want the data duplicated?

Regards,
vcoolio.
0
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 4, 2015 at 05:37 AM
It is ok. no need for apologies.
0
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262 > venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021
Mar 4, 2015 at 05:41 AM
Cheers my friend.
0
thanks a ton both of you :) cheers
0