Transfer data from one sheet to another [Solved]

Ask a question Anita80Olivier 2Posts Thursday February 23, 2017Registration date February 24, 2017 Last seen - Last answered on Feb 24, 2017 at 01:50 AM by vcoolio

I require some assistance please. I looked at a few answers on this forum, but I am stuck as my VB keeps on giving errors. I wish to automatically transfer data from one sheet to a specific sheet, but the criteria on the master sheet is that a installation team will be selected from a drop down list. If the specific team has been selected it must transfer the whole line to their individual excel sheet. For example my installation team is in Column G and then there is columns after. The error I am receiving is on the bold line:

Dim i, LastRow

LastRow = Sheets("Diary").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Sheets("Diary").Cells(i, "G").Value = "Team David" Then
Sheets("Diary").Cells(i, "G").EntireRow.Copy Destination:=Sheets("TeamDavid").Range("A" & Rows.Count).End(xlUp).offest(1)
End If
Next i

I know the code works as I tested it on another excel sheet, but on this specific one it gives problems. Is it because I have drop down lists as a manner of selection, instead of typing?

I would appreciate any help, as I am a complete novice in VB and don't know where to look for errors.

Thanks for any assistance I can get.


plus moins
Hello Anita,

Is the error that you are receiving a "subscript out of range" error?

As far s I can tell, all your data is going to the one sheet, "TeamDavid" and is not being transferred to any other sheet.

In the meantime, have a look at this thread which I believe is a similar scenario to yours:-

The code in post #3 would be a better option but you would need to change line #6 to:-

If Intersect(Target, Columns(7)) Is Nothing Then Exit Sub

Let us know your thoughts.

It would also be a good idea to upload a sample of your work book to a free file sharing site such as DropBox, or Sendspace and then post the link to your file back here. That way we can have a good look at what you are up against. Please use dummy data in the sample.

Anita80Olivier 2Posts Thursday February 23, 2017Registration date February 24, 2017 Last seen - Feb 24, 2017 at 01:08 AM
Thank you for the feedback, I actually got it right yesterday afternoon, I changed the teams name to capitals without any spaces and it worked.

I will bear in mind of the file upload next time.

Thanks a mill!!
vcoolio 1095Posts Thursday July 24, 2014Registration date ModeratorStatus November 20, 2017 Last seen - Feb 24, 2017 at 01:50 AM
That's great Anita! I'm glad that you worked it out for yourself.

Come back any time should you have any future queries.

Good luck!

Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!