Copy data of one sheet to other sheets [Solved/Closed]

ganesh - Sep 10, 2015 at 09:35 PM - Latest reply: vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen
- Nov 5, 2016 at 07:48 AM
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


See more 

25 replies

vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Sep 11, 2015 at 06:07 AM
0
Thank you
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:-

http://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.
Hello vcoolio,

you guessing my problem is absolutely correct.but i can't solve my problem..i want to send my excel sheet to you..please once send me your email...please do my excel sheet.

Thanks

Regards,

ganesh
ganesh > ganesh - Sep 11, 2015 at 11:35 PM
Hello vcoolio,

please give me you mail id. i want to send my excel sheet & information regarding what i expect the results.

Reply me as soon as possible.

Regards,

ganesh
ganesh > ganesh - Sep 12, 2015 at 12:38 AM
Hello vcoolio,

please see attached links:

https://www.dropbox.com/s/nlnnoy7mko0nsxa/AMSB%20CREW%20DATABASE.xlsx?dl=0



first save these two files into one folder.

Don't delete first 3 rows in excel sheets.

if i click file name at the end of the row. the entire row will be copied to the respective sheets.even i can insert hyperlinks to the cell,the rows also copied with hyperlinks.

Regards,

ganesh
ganesh > ganesh - Sep 12, 2015 at 05:45 AM
Hello vcoolio,

please see the attached link. if i click one more time on "Transfer data"
Button ,the same data will moved to respected sheetsone more time ..

So please rectify the problem

https://www.dropbox.com/s/7rwdcbsna7wge8b/Ganesh.xlsm?dl=0

Regards,

ganesh
Hello vcoolio,

please consider one more thing that suppose i am not giving any name at the end of row(AE),The row should appear in the main database(i mean it doesn't show a debug) .and the filenames should not appear at the end of the rows of sheets except main database.
when we transfers the relevant row of data to each individual sheet,the relevant sheets maintain the serial number from 1,2,...etc

This is my final question. please tell me .

https://www.dropbox.com/s/7rwdcbsna7wge8b/Ganesh.xlsm?dl=0

Regards,

ganesh
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Sep 12, 2015 at 04:36 AM
0
Thank you
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 String
lRow = Range("A" & Rows.Count).End(xlUp).Row

For 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 cell

Application.ScreenUpdating = True
Application.CutCopyMode = False

End 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 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Sep 12, 2015 at 06:48 AM
0
Thank you
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 String
lRow = Range("A" & Rows.Count).End(xlUp).Row

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
cell.EntireRow.Copy Sheets(MySheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Next cell

Application.ScreenUpdating = True
Application.CutCopyMode = False

End 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 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Sep 13, 2015 at 04:19 AM
0
Thank you
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.
Hello Vcoolio ,

my problem was resolved..i don't know how to thank you.

i am very grateful to you..

if any doubt arises in excel , can i ask you.?

Regards

ganesh
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Sep 14, 2015 at 03:43 AM
0
Thank you
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.
Hello Vcoolio ,

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..
&
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).

i hope you understand my idea.

please give me solution.

ganesh
Hello Vcoolio ,

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..
&
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).

i hope you understand my idea.

please see the link.

https://www.dropbox.com/s/uhnpsokqrgpo04b/Gantayya.xlsm?dl=0

please give me solution.

ganesh
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Sep 14, 2015 at 08:04 AM
0
Thank you
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.office.com/en-in/article/Create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b

It will guide you on how to construct a Validation list.

I hope that this helps.

Cheerio,
vcoolio.
Hello Vcoolio,

with your support,i am successfully completed my excel sheet.

i have one more problem in the excel sheet.

i fill the excel sheet(ganesh.xlsm) contains hyperlinks to the cells & save the sheet. i am sending the excel sheet (i mean these hyperlink certificates and sheet can be converted to the zip file) to the another system. in the other system , the file name shows" ganesh.xlsm [Read-Only]-microsoft Excel(Product Activation Failed)" & i try to open the hyperlink in the same sheet, it gives a message "can't open the specified file."

How can i edit the sheet. .

please give me an advice..

Regards,

ganesh
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Sep 15, 2015 at 11:40 PM
0
Thank you
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.
Hello vcoolio,

i am giving hyperlinks to the cells in ganesh.xlsm file. The hyperlinks documents are stored in one folder. for suppose i am transfer the documents folder into another folder, 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.

i unprotected the excel sheet(ganesh.xlsm).

i hope you understand my idea.

Regards,

ganesh
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Sep 17, 2015 at 06:18 AM
0
Thank you
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.office.com/en-au/article/Create-select-edit-or-delete-a-hyperlink-472fce40-c5f0-427e-90d4-12c0366bc06f

You should be able to take it from here.

Good luck!

Cheerio,
vcoolio.
Hello vcoolio,

i want a software that satisfy to my conditions. if you know it,please advice me.

i want a cloud storage that can store my office files & Documents.
suppose we are 10 employees. one employee can login to cloud with his email id & password & upload folders or files to the cloud. second employee can login to same cloud his email id & password& upload the files and folders.but one employee can't open the another employee files and folders,but another employee wants to open the folders it asks a password.

my condition is that ,For suppose if any employee can download the file.it can't be editable.
&
only particular owner can be download the file and editable.

we can also want to buy the storage..

i know the software like "Drop box" but it is not satisfy my condition. There is one problem in drop box that share holder can edit the file ,automatically the original files can also changed.. This is major Drawback.

i know one more software "i cloud" but i don't know how it works.

i hope you understand my idea..

Regards,

ganesh
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Sep 24, 2015 at 06:05 AM
0
Thank you
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.
Hello vcoolio,

This is ganesh.

i need one more help from you.

Can you just solve my problem by automatic transfer of data from one to all other sheets in a single file.

see link below:-

https://drive.google.com/open?id=0ByQPnLUCgh0mbFZzU09Icmt0NGM

i am facing one problem that the same row will be copied into two times in a single sheet.

i hope you understand my idea.

Thank you,

Your's,
Ganesh
Hello vcoolio,

See below link:-

https://www.dropbox.com/s/2dnllep5ifmbi9j/sample.xlsm?dl=0

Regards,
Ganesh
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Oct 24, 2016 at 10:15 PM
0
Thank you
Hello Ganesh,

Change the following row in the code:-

sht.UsedRange.Offset(4).ClearContents


to

sht.UsedRange.Offset(3).ClearContents


Cheerio,
vcoolio.
Hello vcoolio,

Good afternoon. i am happy to talk with you again

I need one help from you.

https://www.dropbox.com/s/ibi3cdp22y7oe4c/sample%202.xlsm?dl=0

please click above link & adjust the code for automatic transfer of data from main database to other sheets including Rate/kg and amount.

Also i want to maintain a every shift(AM,PM) data in a separate excel sheet or main database. Can you please give me an idea & solution

Thanks

I am awaiting for your valuable reply.

Your's
Ganesh
vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen > Raja - Nov 5, 2016 at 07:48 AM
Hello Ganesh,

Replace the code already in the work book with the following code:-

Sub TransferData()

Application.ScreenUpdating = False

    Dim lRow As Long
    Dim MySheet As String
    Dim cell As Range
    Dim ws As Worksheet
    
lRow = Range("B" & Rows.Count).End(xlUp).Row

On Error Resume Next
For Each ws In Worksheets
        If ws.Name <> "MAIN DATABASE" And ws.Name <> "Buffalo" And ws.Name <> "Cow" Then
        ws.UsedRange.Offset(1).ClearContents
        End If
Next ws

For Each cell In Range("B6:B" & lRow)
    MySheet = cell.Value
    Range(Cells(cell.Row, "C"), Cells(cell.Row, "O")).Copy Sheets(MySheet).Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
    Sheets(MySheet).Columns.AutoFit
Next cell

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


You will need to create the remaining sheets (1003---->1008) exactly the same as 1001 and 1002 before running this code.

Also i want to maintain a every shift(AM,PM) data in a separate excel sheet or main database


I'm not sure what you mean by this. Please start a new thread for any further queries as this one is becoming too long and your latest queries are not relevant to your original post.

Thank you Ganesh.

Cheerio,
vcoolio.