Copy data from one Excel sheet to another: automatically
Solved/Closedvcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jul 27, 2023 at 06:07 AM
- Copy data from one Excel sheet to another: automatically
- Transfer data from one excel worksheet to another automatically - Guide
- Mark sheet in excel - Guide
- How to open excel sheet in notepad++ - Guide
- How to screenshot excel sheet - Guide
- Google sheet right to left - Guide
8 responses
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
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.
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
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.
Didn't find the answer you are looking for?
Ask a questionJul 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
Jul 20, 2023 at 07:04 AM
Dear Sir,
Please look into sample sheet link : https://wetransfer.com/downloads/5e509620f177ca4f3bfef12bcadc5aa920230720110309/9eb381
Regards,
ganesh
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
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.
Jul 21, 2023 at 01:54 AM
Dear Sir,
Thank you very much once again. The above code working fine.
Regards,
Ganesh
Jul 21, 2023 at 03:53 AM
You're welcome Ganesh. I'm glad to have been able to help again.
Cheerio,
vcoolio.