Menu
Ask a question Report

Transfer data from one sheet to another [Solved/Closed]

Posts
2
Registration date
Thursday February 23, 2017
Last seen
February 24, 2017
 - - Latest reply: vcoolio
Posts
1206
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 7, 2018
 - Feb 24, 2017 at 01:50 AM
Excel Ask a question Report
Hello,

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
Sheets("TeamDavid").Range("A2:I5000").ClearContents
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.

Cheers!

See more 

1 reply

Report Respond to vcoolio
Posts
1206
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 7, 2018
0
Thank you
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:-

http://ccm.net/forum/affich-964123-complicated-auto-populate-possible-or-not-and-if-so-how

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, ge.tt 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.

Cheerio,
vcoolio.
Report
Anita80Olivier
Posts
2
Registration date
Thursday February 23, 2017
Last seen
February 24, 2017
 -
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!!
Report
vcoolio
Posts
1206
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 7, 2018
 > Anita80Olivier
Posts
2
Registration date
Thursday February 23, 2017
Last seen
February 24, 2017
 -
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!

Cheerio,
vcoolio.;
Ask a question