Copy paste data from one sheet to another automatically

Closed
ssdgeek - Aug 10, 2016 at 09:09 AM
ssdgeek Posts 6 Registration date Friday August 12, 2016 Status Member Last seen August 17, 2016 - Aug 17, 2016 at 11:08 AM
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

vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Aug 11, 2016 at 10:00 AM
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.
0
ssdgeek Posts 6 Registration date Friday August 12, 2016 Status Member Last seen August 17, 2016
Aug 12, 2016 at 07:54 AM
Is there any way I may send the file directly here as I do not have a dropbox or any such account.
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Aug 12, 2016 at 08:52 AM
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.
0
ssdgeek Posts 6 Registration date Friday August 12, 2016 Status Member Last seen August 17, 2016
Aug 12, 2016 at 11:34 AM
Hi,

Below link will take you to the file.

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

Thanks and best regards,
Subash
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Aug 13, 2016 at 07:52 AM
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.
0
ssdgeek Posts 6 Registration date Friday August 12, 2016 Status Member Last seen August 17, 2016
Aug 14, 2016 at 08:06 AM
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
0
fdibbins Posts 33 Registration date Sunday June 19, 2016 Status Contributor Last seen November 20, 2016 1
Aug 14, 2016 at 08:18 PM
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
0
ssdgeek Posts 6 Registration date Friday August 12, 2016 Status Member Last seen August 17, 2016
Aug 15, 2016 at 06:12 AM
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
0
ssdgeek Posts 6 Registration date Friday August 12, 2016 Status Member Last seen August 17, 2016
Aug 17, 2016 at 11:08 AM
Hi,
Still waiting please.
Best regards,
Subash
0

Didn't find the answer you are looking for?

Ask a question
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Aug 14, 2016 at 10:26 PM
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.
0
ssdgeek Posts 6 Registration date Friday August 12, 2016 Status Member Last seen August 17, 2016
Aug 15, 2016 at 06:12 AM
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
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Aug 15, 2016 at 06:29 AM
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.
0