Copy paste data from one sheet to another automatically

[Closed]
Report
-
Posts
6
Registration date
Friday August 12, 2016
Status
Member
Last seen
August 17, 2016
-
Hello,

First of all thanks for your wonderful site.

I have a large set of information on one sheet. Column b has the name of the sheets in the file.

I want the data of the row to be copied to the sheet whose name appears in column b automatically in the next available free row in sheet name selected in column b.

Can anyone help please?

Thank in advance.
Subash


6 replies

Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello Subash,

Could you please upload a sample of your work book to a free file sharing site such as DropBox, ge.tt or SpeedyShare and then post the link to your file back here.

Please use dummy data.

We'll be able to then understand your query better and help you resolve it.

Cheerio,
vcoolio.
Posts
6
Registration date
Friday August 12, 2016
Status
Member
Last seen
August 17, 2016

Is there any way I may send the file directly here as I do not have a dropbox or any such account.
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello Subash,

You can't directly but DropBox is a free file sharing site. You don't need an account. Just sign up is all you need to do. The same goes for similar free file sharing sites.

Cheerio,
vcoolio.
Posts
6
Registration date
Friday August 12, 2016
Status
Member
Last seen
August 17, 2016

Hi,

Below link will take you to the file.

https://drive.google.com/file/d/0B36DR4A7NkktOTBldzhWVy1fdjg/view?usp=sharing

Thanks and best regards,
Subash
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello Subash,

Based on what you have supplied and explained, I believe the following code, placed in a standard module, will do the task for you:-

Option Explicit

Sub TransferData()

Dim ar As Variant, i As Integer

ar = [{"A","B","C","D";"A","B","C","D"}]

Application.ScreenUpdating = False

For i = 1 To UBound(ar, 2)
    Sheets(ar(1, i)).UsedRange.ClearContents
        With Sheet1
            .AutoFilterMode = False
                With Range("A1", Range("A" & Rows.Count).End(xlUp))
                    .AutoFilter 1, ar(2, i)
                    .Offset(0).EntireRow.Copy
                    Sheets(ar(1, i)).Range("A" & Rows.Count).End(xlUp).PasteSpecial xlPasteValues
                    ActiveSheet.AutoFilterMode = False
                    Sheets(ar(1, i)).Columns.AutoFit
                End With
        End With
  Next i
    
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data transfer completed!", vbExclamation

End Sub


I have assumed that you have already created the destination sheets and that sheet1 is the source (or input) sheet.

The code filters Column A for the sheet names and then transfers all relevant rows of data to each individual sheet.

If you have more sheets, you can increase the size of the array (line 7 in the code above). The left side of the array (the first dimension) refers to the sheet names and the right side of the array (the second dimension) refers to the criteria in Column A.

Following is the link to my test work book. Click on the "RUN" button to see it work.

https://www.dropbox.com/s/vbwufs6zronrcb5/Subash%28Master%20sht%20to%20multi%20shts%29.xlsm?dl=0

I hope that this helps.

Cheerio,
vcoolio.
Posts
6
Registration date
Friday August 12, 2016
Status
Member
Last seen
August 17, 2016

Thanks for taking out so much time Vcoolio.

I downloaded the file and clicked on the "Run", nothing happened.

Secondly, I do not know how to use VBA and that is why I was looking for a formula based answer to my query. Alternatively if you could advise how to use the VBA that you have sent. Sorry, am a bit of a dumbo.....

thanks and best regards,
Subash
Posts
33
Registration date
Sunday June 19, 2016
Status
Contributor
Last seen
November 20, 2016
1
Assuming your data looks something like this...
Header1... Sheet name... Header3 
aa... sheet7... 10 sheet7... 1
bb... sheet8... 20 sheet8... 1
cc... sheet7... 30 sheet7... 2
aa... sheet8... 40 sheet8... 2
bb... sheet7... 50 sheet7... 3
cc... sheet8... 60 sheet8... 3

D2=B2&" "&COUNTIF($B$2:B2,B2)
copied down

on sheet7...
Header1 ...Sheet name ...Header3
aa... sheet7... 10
cc...sheet7... 30
bb... sheet7... 50

A2=IFERROR(INDEX(Sheet6!A:A,MATCH("sheet7 "&ROWS($A$1:A1),Sheet6!$D:$D,0)),"")
copied down and across
Posts
6
Registration date
Friday August 12, 2016
Status
Member
Last seen
August 17, 2016

Dear Fdibbin,

Good morning.

Please find below the link to the file that I sent earlier, kindly download it and assist, as from what you have sent above it is not clear please.

https://drive.google.com/file/d/0B36DR4A7NkktOTBldzhWVy1fdjg/view?usp=sharing

Thanks so much for all your time and help.

Best regards,
Subash
Posts
6
Registration date
Friday August 12, 2016
Status
Member
Last seen
August 17, 2016

Hi,
Still waiting please.
Best regards,
Subash
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello Subash,

Don't be so hard on yourself! You'll catch on soon enough.

I would say that nothing happened after you opened the DropBox link because of security settings in the download.

When you first open the file, you will see a gold coloured bar near the top of the page which says Protected View and a button that says Enable Editing. Click on this button to clear the protection. Another gold bar will appear this time saying Security Warning and a button in that bar which says Enable Content. Click on this button and then the security settings will be cleared. You will now be able to see how my test work book works.

I see that our fellow volunteer fdibbins has given you a formula option which I think you initially preferred. Now you are spoiled for choice!!

Try both options and then let us know your thoughts.

Cheerio,
vcoolio.
Posts
6
Registration date
Friday August 12, 2016
Status
Member
Last seen
August 17, 2016

Dear Vcoolio,

I did all the Protected veiw etc till enable content, and still the only thing I got was data transfer complete but nothing happened in the relevant sheets.

Thanks and best regards,
Subash
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello Subash,

Did you check the other sheets? If you do, you will see that the data has been transferred.

The message comes up once the data is transferred but your view will remain on the main sheet. Check the other sheets.

Cheerio,
vcoolio.