Sheet One Items Transferred to Sheet Two.Closed

Question

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      Amount2       1325    17/06/2014      1             Direct          10009       4433    04/08/2014      2             online          2000        1325    30/08/2014      3             Direct          3000        5555    11/09/2014      4             Direct          40001       1325    14/06/2014      5             Direct          50007       4433    30/07/2014      6             Cash            60008       5555    07/07/2014      7             online          700014      4433    09/10/2014      8             direct          80006       1325    25/07/2014      9             online          90005       7432    16/07/2014      10            Direct          1000010      4433    09/08/2014      11            Direct          1100013      1325    21/09/2014      12            Cash            120003       4433    27/06/2014      13            Direct          1300011      5555    11/08/2014      14            Ubi             140004       5555    27/06/2014      15            Direct          1500012      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.

venkat1926 · Answer

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

vcoolio · Answer

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.

vcoolio · Answer

Hello Venkat,

My apologies for intruding. We must have posted at the same time!

I'll leave it with you.

Regards,
vcoolio.

Sheet One Items Transferred to Sheet Two.

3 replies

Similar discussions

Subscribe To Our Newsletter!