Help VBA code on transferring data to different work sheets

[Wino82]

Hi,

I am new to VBA coding. What I am wanting to do is to transfer Names(Row B7:B160) to a different work sheet IF the Row next to it(C7:C160) is filled out with a time. I will be setting up a command button that once say 10 names have times next to it, this data then will transfer to another work sheet.

I have done below coding, however it will only transfer data in B7 and C7, if I try to add B7:B160 it comes back as Missmatch. Below is what I have done for the Command button. Please can anyone help me with this. Thank you in advance Winston

Private Sub CommandButton1_Click()
Dim AdviserName As String, Time As Integer
Worksheets("Sheet1").Select
AdviserName = Range("B7")
Time = Range("C7")
Worksheets("Sheet2").Select
Worksheets("Sheet2").Range("F5").Select
If Worksheets("Sheet2").Range("F5").Offset(1, 0) <> "" Then
Worksheets("Sheet2").Range("F5").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = AdviserName
ActiveCell.Offset(0, 2).Select
ActiveCell.Value = Time
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("C6").Select


End Sub

[Hawkeye16]

Attaching a workbook with a before and after of what you are trying to accomplish would help a lot with this. Right now you are only setting variables equal to B7, to use a range they would need to be dimensioned as ranges not string and integer.

Make sure to use code tags when posting code

Maybe try something like (note that unless the two ranges for copy from and copy to are the same size this may error)

Please Login or Register  to view this content.

[Wino82]

Copy of Dummy ID docx EXAMPLE.xlsxThank you for your response, I have attached an example of what I want to do, however there will be a lot more names added to this. As you can see when you open it up, I want to be able to add times by different people then click UPDATE button, this will then transfer the data to work sheet 2(Dummy ID's) and populate the relevant fields.

At this moment in time it is only setup for C7 so works only if you add something into that cell. What I need is:

a) Time to transfer over
b) to be able to add up to 10 times by peoples names so it fills out the Dummy ID section.

I am sorry if I'm not making any sense on this. Thanks again for your help

[Hawkeye16]

Yes, I am still having trouble understanding the outcome you are expecting, perhaps you could make Sheet3 into the "final outcome" tab that you would expect to see after running the macro?

[Wino82]

I'm sorry the confusion. What I want a macro to do is to, transfer names and the shift times on to Sheet 2, instead of manually writing in every persons name and times, I want to just write the times next to the names then click 'UPDATE' it would transfer the name and shift times to sheet 2. So I then can copy and paste that information out to the advisers, thus saving time.

Does this make any more sense? Thanks

[Hawkeye16]

What role do the Dummy ID and Extensions play? Do you randomly assign a name to a Dummy ID or is that there to signify previous data?

[Wino82]

It's random, I will assign advisers to go onto these dummy ID's when it's needed, however just long winded having to type out every persons name against each ID, thought it would be simpler just to assign a time, then it auto fills it out ready to send. Thanks