Copy data from master to other sheets

**mdshotgun** · 03-15-2011, 08:42 AM

Hello all,

On first sheet Quiz results sheet i will be inserting results and in column A i will pick from dropdown a choice of quiz tables 1-4.
see attached for examples.
I was hoping to copy the results from quiz results sheet to appropriate Tables sheet from the choice in column A.

I hope this is clear enough to understand.Thanks for any help.

**pike** · 03-16-2011, 02:47 AM

Hi mdshotgun
if vba is ok then

Option Explicit
Sub ptest()
    Dim xcell As Range
    With Sheets("Quiz results")
        For Each xcell In .Range("A3", .Cells(Rows.Count, 1).End(xlUp))
            xcell.Resize(1, 6).Copy Destination:=Sheets(xcell.Text).Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
        Next
    End With
End Sub

**mdshotgun** · 03-16-2011, 04:42 AM

Thank you Pike for your reply,

Yes it works nicely.
But i have 1 last small issue.
every week we will be adding the next set of quiz fixtures.
Ex:I add next weeks fixture to row 15 and when i add in results and run the program,
it seems to be copying all results again,not just the new ones.
If this can't be fixed easy,i can just clear all the previous weeks results first,but
it was something i hope i did'nt have to do.

But thanks very much for your help.

**pike** · 03-16-2011, 04:50 AM

hi mdshotgun
One way wouid be to count all the rows with values in all the Quiz table sheets then start from that row number in the result table.
But why not just delete the last record as you have re-distribted the result to the sheet.

**mdshotgun** · 03-16-2011, 04:57 AM

Hi Pike,

I was keeping the results on quiz results sheet,so when i was posting up next weeks fixtures
that i could remember what teams already played who.
And i know they are on quiz tables sheets
But it was easier to leave them on quiz results sheet so i would'nt have to keep track of each other sheet.

I will try your suggestion first.Thanks Pike.

**pike** · 03-16-2011, 05:23 AM

mdshotgun
another way would be to count the rows in the result sheet on opening or activate then before close or deactivate the sheet it runs the code from the last row to new rows