Copy data of one sheet to other sheetsSolved/Closed

Question

Hello, 
I want a VBA code or excel formula that automatic copy of data from one sheet to another sheets(either sheet 2 or sheet 3...etc)..suppose In the sheet 1 ,there are   30 columns & 100 rows. In the first row ending  of sheet 1, i am giving the another sheet name(either sheet 2, sheet 3,....etc),the entire row will be copied to the respective sheets.

please give suitable suggestions.

Thanks

ganesh


Configuration: Windows / Chrome 45.0.2454.85

vcoolio · Answer

Hello Ganesh,

I have a fair idea as to what you would like to do but it would be most helpful if you gave us more information and a sample of your work book showing your inputs and expected results.

In the meantime, have a look at the following thread by another Poster from a few days ago and see if it is similar to your own situation:-

https://ccm.net/forum/affich-838105-auto-populate-rows-from-master-sheet-to-other-sheets-excel-2013

The ID in the above thread is in Column A but of course it can be placed wherever suits you best. You will see from the test work book in the above thread that the VBA code searches Column A for each unique ID and then transfers the relevant row of data to each individual sheet.

Let us know what you think.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Ganesh,

Firstly, please bear in mind that this is a public forum and any Poster who starts a thread asking for help needs to keep all correspondence on the forum.
Other people may come here looking for resolutions similar to your own hence it is a simple case of help one, help all. 
Therefore, to be fair to all we cannot correspond by e-mail or PM to resolve your issue and any Volunteer on this forum will refuse to do so. Please make yourself familiar with the CCM Charter (click on "Charter" at the bottom of this page).

Secondly, the second link you supplied with the Certificate of Proficiency was not required, so I have deleted it. This type of personal information should not be displayed on any forum.

Thirdly, your request:

I believe that the following code should do as you would like:-


Sub TransferData()Application.ScreenUpdating = False    Dim lRow As Long    Dim MySheet As StringlRow = Range("A" & Rows.Count).End(xlUp).RowFor Each cell In Range("AE5:AE" & lRow)    MySheet = cell.Value    cell.EntireRow.Copy Sheets(MySheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)Next cellApplication.ScreenUpdating = TrueApplication.CutCopyMode = FalseEnd Sub

The code searches Column AE for the criteria, in this case the Vessel Names, and transfers the rows of data associated with a particular name directly to the individual sheet. Following is a link to your work book which I have updated with the code:-

 https://www.dropbox.com/s/gvnbwae0kd8eoew/Ganesh.xlsm?dl=0

I have also removed the numbers from Column A in each sheet (other than the Main Data Base sheet) as these numbers are transferred from the Main Data Base sheet with the rest of the row on execution of the code.

In the sample work book, enter some fictitious data in a few rows and click on the Transfer Data button. You will see that the data is transferred to each individual sheet based on what name you enter in Column AE.

Create a copy of your work book to test the code with your actual data before implementing the code into your actual work book.

I hope that this helps.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Ganesh,

The work sheets are password protected, so I used a work-around to test the additional lines of code that I have added so change the code to the following:-


Sub TransferData()Application.ScreenUpdating = False    Dim lRow As Long    Dim MySheet As StringlRow = Range("A" & Rows.Count).End(xlUp).RowFor Each sht In Worksheets        If sht.Name <> "MAIN DATABASE" Then        sht.UsedRange.Offset(4).ClearContents        End IfNext shtFor Each cell In Range("AE5:AE" & lRow)    MySheet = cell.Value    cell.EntireRow.Copy Sheets(MySheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)Next cellApplication.ScreenUpdating = TrueApplication.CutCopyMode = FalseEnd Sub

You should be able to see the slight change. The few extra lines clear all the individual sheets (except for the headings) which will prevent duplication each time you execute the code. The main data will remain in the Main Data Base sheet.

You will need to re-assign the code to the button (right click on the button, select "assign macro" and from the Assign Macro window that appears, select Transfer Data and then click OK).

Cheerio,
vcoolio.

vcoolio · Answer

Hello Ganesh,

Following is a code that should do all that you need:-

Sub TransferData()

Application.ScreenUpdating = False

Dim lRow As Long
    Dim MySheet As String
lRow = Range("A" & Rows.Count).End(xlUp).Row

On Error Resume Next
For Each sht In Worksheets
        If sht.Name <> "MAIN DATABASE" Then
        sht.UsedRange.Offset(4).ClearContents
        End If
Next sht

For Each cell In Range("AE5:AE" & lRow)
    MySheet = cell.Value
    Range(Cells(cell.Row, "B"), Cells(cell.Row, "AD")).Copy Sheets(MySheet).Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
Next cell

Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub

It does not transfer the vessel name from Column AE and leaves the serial numbers as is.

I hope that this helps.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Ganesh,

You're welcome. I'm glad that I could help.

If you have any further queries, please don't hesitate to come back and start a new thread.

Good luck.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Ganesh,

I have one more problem on the previous excel sheet...suppose i delete the values of day on,proposed sign off date,day off(the columns contains Formula),the formula also deleted. my condition is that formula will not be deleted.. 

Do not delete or clear the contents of a cell that contains a formula, just clear or overwrite the cell(s) that feed the cell with the formula in it.

In the formula sheet ,for suppose i am inserting one more cell in the vessel column,it is automatically updated to main database sheet of vessel column(AE) drop down list.(like please see Rank, Nationalitname). 


Carefully read the following Microsoft article:-

https://support.microsoft.com/en-us/office/create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b?ui=en-us&rs=en-us&ad=us

It will guide you on how to construct a Validation list.

I hope that this helps.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Ganesh,

I would say that it all boils down to the fact that your file is password protected. You will need to unprotect it to make it editable again.

Excel spreadsheets and workbooks can be protected to prevent changes. Depending on the level of protection, they may be partly or completely Read Only. You can select "Unprotect" under the Review tab, but if the person who enabled protection set a password, you will need that password to remove the protection. 

Also, if you prefer:-

On your spreadsheet, click on file up in the top left of your page then select the Info tab. Here you should see three headings: Permissions, Prepare for Sharing and Versions. Under the Permissions heading, you will see what is protected. Change these to "unprotect". You may be prompted to use your password. This should fix the issue for you.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Ganesh,
 when i want to open the hyperlinks,it show a message"can not open the Specific file.

i want to transfer my documents folder into another folder but when i open the hyperlinks it doesn't show a warning message.

It would appear that you need to re-establish the hyperlinks. Carefully read the following Microsoft article about hyperlinks and carefully follow each step. Make sure that your work book is unprotected first:-


https://support.microsoft.com/en-us/office/work-with-links-in-excel-7fc80d8d-68f9-482f-ab01-584c44d72b3e?redirectsourcepath=%252fen-us%252farticle%252fcreate-select-edit-or-delete-a-hyperlink-472fce40-c5f0-427e-90d4-12c0366bc06f&ui=en-us&rs=en-us&ad=us

You should be able to take it from here.

Good luck!

Cheerio,
vcoolio.

vcoolio · Answer

Hello Ganesh,

I'm not qualified to advise you on which cloud based storage provider is best for you but Microsoft OneDrive works for me.

Have a look at the following article:-

http://www.thetop10bestonlinebackup.com/cloud-storage

which may help to answer most of your questions.

Just remember, that the more you want from these providers, the more you will have to pay. You will only get the basics for free.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Ganesh,

Change the following row in the code:-

sht.UsedRange.Offset(4).ClearContents

to

sht.UsedRange.Offset(3).ClearContents

Cheerio,
vcoolio.

Copy data of one sheet to other sheets

10 responses

Similar discussions

Subscribe To Our Newsletter!