copy paste data from one sheet to another automaticallyClosed

Question

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


Configuration: Windows / Chrome 52.0.2743.116

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

fdibbins · Answer

Assuming your data looks something like this...
Header1... Sheet name... Header3 aa... sheet7... 10 sheet7... 1bb... sheet8... 20 sheet8... 1cc... sheet7... 30 sheet7... 2aa... sheet8... 40 sheet8... 2bb... sheet7... 50 sheet7... 3cc... sheet8... 60 sheet8... 3
D2=B2&" "&COUNTIF($B$2:B2,B2)
copied down

on sheet7...
Header1 ...Sheet name ...Header3aa... sheet7... 10cc...sheet7... 30bb... sheet7... 50
A2=IFERROR(INDEX(Sheet6!A:A,MATCH("sheet7 "&ROWS($A$1:A1),Sheet6!$D:$D,0)),"")
copied down and across

vcoolio · Answer

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.

vcoolio · Answer

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.

Copy paste data from one sheet to another automatically

6 responses

Similar discussions

Subscribe To Our Newsletter!