Copy data from one Excel sheet to another: automatically

Thanks for clarifying that Ganesh.

Create a copy of your workbook and test the following VBA macro in it:
 

Option Explicit
Sub Test()

        Dim ar As Variant, i As Long
        Dim wsD As Worksheet, ws As Worksheet
        Set ws = Sheets("MAIN DATABASE")
        ar = Array("M-23", "Nm-23", "SM-23")

Application.ScreenUpdating = False

          For i = 0 To UBound(ar)
                Set wsD = Sheets(CStr(ar(i))) '---->Destination worksheets.
                wsD.UsedRange.Offset(2).Clear '---->Clears destination worksheets of data.
           
                With ws.[A1].CurrentRegion
                        .AutoFilter 1, ar(i)
                        .Offset(1, 1).Resize(.Rows.Count - 1, 18).Copy wsD.Range("A" & Rows.Count).End(3)(2)
                        .AutoFilter
                End With
                        wsD.Columns.AutoFit
                        wsD.Rows.AutoFit
           Next i

Application.ScreenUpdating = True

End Sub

I've placed a line of code in the macro which clears all data in the destination worksheets prior to any data transfer (line 13).

I hope that this helps.

Cheerio,

vcoolio

Hello Ganesh,

Google won't allow me to access your file at the link you provided. Please use a free file transfer site such as WeTransfer then post the link to your file back here.

Cheerio,

vcoolio.

Dear Sir,

As suggested, Please find below link to access my file.

link : https://wetransfer.com/downloads/a76c1b839e3dd55eebb4e1628b67228220230720084454/63a116

Regards

Ganesh

Hello Gannesh,

Thanks for that.

Could you please confirm that you want to transfer data from the source sheet, MAIN DATABASE, to the destination sheets, M-23, NM-23 and SM-23, based on the destination sheet names found in Column A (Service Type) of the MAIN DATABASE sheet. Each service type has multiple entries in the source sheet so each row needs to be transferred to the relevant destination sheet.

Do you need each entire row transferred to the relevant destination sheet or do you only need certain columns of data transferred across?

I noticed that you have a mass of formulae in the destination sheets, all drawing data from the source sheet which are actually doing what you have asked in your opening post. Do you wish to no longer use formulae to do this task? I'm somewhat confused by your request.

Cheerio,

vcoolio.

Dear Sir,

Could you please confirm that you want to transfer data from the source sheet, MAIN DATABASE, to the destination sheets, M-23, NM-23 and SM-23, based on the destination sheet names found in Column A (Service Type) of the MAIN DATABASE sheet. Each service type has multiple entries in the source sheet so each row needs to be transferred to the relevant destination sheet. - "Exactly right"

Do you need each entire row transferred to the relevant destination sheet or do you only need certain columns of data transferred across? : "I need entire row transferred to the relevant destination sheet. Except service type "

Basically I tried several formulas to transfer the data but I can't.  so please ignore all formulas .. i dont want to use the formulas no longer.

Regards,

ganesh

Dear Sir,

Please look into sample sheet link : https://wetransfer.com/downloads/5e509620f177ca4f3bfef12bcadc5aa920230720110309/9eb381

Regards,

ganesh

Dear Sir,

I want one more help regarding dependent drop down list in excel.

I have a column A of repeated job numbers like M001/23,M002/23..etc. and column B of route card numbers like M001/23-1,M001/23-2,M001/23-3,M002/23-1,M002/23-2..etc.

I want a drop list that can be sorted  unique numbers out of repeated numbers. (that means only one number will be come for selection in dropdown list of job card no). and also based on the job no dropdown list, only route card numbers will come for selection in dropdown list.. Attached sample excel  file for your reference.

Link : https://wetransfer.com/downloads/dfd6189c4b63b645d97d68e6711e254820230726113231/eebfc2

Regards,

Ganesh

Hello Ganesh,

I don't quite follow what you are trying to do but this is my understanding of your request:

- You have a data validation drop down list with unique values in cell G2. These values are job numbers from Column A of your data set.

- From cell G5, you wish to list the Route Card numbers associated with the selection made from the drop down in cell G2.

In cells G9 and G11, you have duplicated the above but I'm assuming that you are just showing the result of a different selection from the drop down list in G2. Is this correct? Cells G9/G11 can be removed in this case for testing in the sample, correct?

Please also note that should you have any more requirements with this workbook, please start a new thread as we are getting further and further away from the original query and this thread will become too long and confusing. Thank you!

Cheerio,

vcoolio.