Sheet One Items Transferred to Sheet Two. [Closed]

Report
-
vcoolio
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
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.

3 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
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
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 ***@***
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
211
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.
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
211
Hello Venkat,

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

I'll leave it with you.

Regards,
vcoolio.
vcoolio
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
211 > srigopal
Hello Srigopal,

How many are you using as of now and how many for the very near future?

Cheerio,
vcoolio.
right now using 30 in future max i need is 100
vcoolio
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
211 > srigopal
Can you supply the sheet names or numbers that you want to use?
sheet names :

2563
2598
9865
5634
6985
5658
Srigopal
rakesh
amit
suresh
saleem
anthony
vcoolio
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
211 > srigopal
Hello Srigopal,

I've updated the sample work book for you to have a look at.

https://www.dropbox.com/s/6gls4kgedyt4heo/Srigopal%282%29.xlsb?dl=0

As your work book grows, we'll see how it performs then.

Cheerio,
vcoolio.