Copy data from one Excel sheet to another: automatically

Solved
gannesh0604 Posts 25 Registration date Thursday July 20, 2023 Status Member Last seen June 1, 2024 - Jul 20, 2023 at 02:37 AM
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - Jul 27, 2023 at 06:07 AM

Dear all,

Anybody can help me please.

I have MAIN DATABASE ( sheet 1) of column names : Serial No, Service type, Job No, Date, Customer name, description, scope, qty...etc.

I made another few more sheets with name of Service type(Ex:M-23,NM-23,SM-23). The column names is same as MAIN DATABASE sheet except service type. I want to transfer data from Main database to other sheets i.e,.to M-23,NM-23,SM-23 sheets automatically  by the condition of M-23.there are repeated service type names in main database. all should come to the respective sheets one by one by serial order.

looking for a formula or DBA code to transfer the data automatically when I putting the data into the main database.

attached google drive link for sample sheet: https://docs.google.com/spreadsheets/d/1XGUZEyuNr75gePS1Wyk7LWUX3k2OukFE/edit?usp=drivesdk&ouid=111420590505833532953&rtpof=true&sd=true

8 responses

vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Updated on Jul 20, 2023 at 08:13 AM

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

1
gannesh0604 Posts 25 Registration date Thursday July 20, 2023 Status Member Last seen June 1, 2024
Jul 21, 2023 at 01:54 AM

Dear Sir,

Thank you very much once again.  The above code working fine.

Regards,

Ganesh

0
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Jul 21, 2023 at 03:53 AM

You're welcome Ganesh. I'm glad to have been able to help again.

Cheerio,

vcoolio.

0
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Jul 20, 2023 at 03:45 AM

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.

0
gannesh0604 Posts 25 Registration date Thursday July 20, 2023 Status Member Last seen June 1, 2024
Jul 20, 2023 at 04:52 AM

Dear Sir,

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

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

Regards

Ganesh

0
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Jul 20, 2023 at 06:27 AM

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.

0

Didn't find the answer you are looking for?

Ask a question
gannesh0604 Posts 25 Registration date Thursday July 20, 2023 Status Member Last seen June 1, 2024
Jul 20, 2023 at 06:53 AM

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

0
gannesh0604 Posts 25 Registration date Thursday July 20, 2023 Status Member Last seen June 1, 2024
Jul 20, 2023 at 07:04 AM

Dear Sir,

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

Regards,

ganesh

0
gannesh0604 Posts 25 Registration date Thursday July 20, 2023 Status Member Last seen June 1, 2024
Jul 26, 2023 at 07:33 AM

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

0
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Jul 27, 2023 at 06:07 AM

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.

0