Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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

Report
Posts
2
Registration date
Thursday February 23, 2017
Status
Member
Last seen
February 24, 2017
 -
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 -
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!

Related:

1 reply

Answer 1 / 1
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220
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:-

https://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
Posts
2
Registration date
Thursday February 23, 2017
Status
Member
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
Posts
1278
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 16, 2021
 220 >
Posts
2
Registration date
Thursday February 23, 2017
Status
Member
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!