Solved: pulling data from multiple columns in one worksheet to others

[Beatrix]

Hi Everyone ,

I need to pull data from multiple columns in one worksheet to others in same workbook. Is there a vba code you could suggest me to use to do this?

I have a spreadsheet with 15 tabs and I need to allocate daily rns numbers (higlighted in yellow) from daily allocation by agent tab to 14 individual agent tabs. All agent tabs are in same format. The thing is rns data is monthly basis agent by agent. If the figure is 86 for feb for agent1 then I should copy that figure from 1st feb to 29th in related tab. I should do this copy-paste thing for 12 months for 14 agents.

sample spreadsheet is attached.

thanks very much in advance.

Yeliz

[mdmackillop]

[VBA]
Sub Test()
'Set Agent sheets
For i = 2 To Sheets.Count
Sheets(i).Range("D2367").FormulaR1C1 = "=MONTH(RC[-2])"
Next
'Set DABA Sheet
With Sheets(1)
.Range("S4:S14").FormulaArray = "=ROW()-2"
.Range("S15") = 1
.Range("S20:S31").Value = Range("S4:S15").Value

For k = 1 To 18 Step 17
For j = 3 To 15 Step 2
Set c = .Cells(k, j)
For r = 3 To 14
Set dly = c.Offset(r).Offset(, 1)
For Each cel In Sheets(c.Value).Range("D2367")
If cel = .Cells(dly.Row, "S") Then
cel.Offset(, -1).Value = dly.Value
End If
Next
Next r
Next j
Next k
.Columns(19).ClearContents
End With
For i = 2 To Sheets.Count
Sheets(i).Range("D2367").ClearContents
Next
End Sub

[/VBA]

[Beatrix]

Hi there..

Thanks very much. I appreciate for your help. It works perfect! If you don't mind I have a question? If I move to january to top of the list on daily allocation table then which line do I need to change in code?

Cheers
Yeliz

[mdmackillop]

You need to change these two lines to correectly insert the month numbers
[VBA]
.Range("S4:S14").FormulaArray = "=ROW()-2"
.Range("S15") = 1

[/VBA]

[Beatrix]

Many thanks, it worked..

[Beatrix]

Hmm.. I noticed that second table- range S20:S31 is missing january data in agent lists. I copied updated attachment to make it clear as I have no idea which part I need to change in code. I spent some time to work it out but failed Would you mind to have a look at the attachment please?

[mdmackillop]

The Room Number row is not in the layout of the lower data, so the Offset values need to reduce by 1. I've split this into 2 loops.
BTW, I suggest when you step through the code, add your own comments so you are clear what is happening, and why!

[vba]Sub Test()
'Set Agent sheets
For i = 2 To Sheets.Count
Sheets(i).Range("D2367").FormulaR1C1 = "=MONTH(RC[-2])"
Next
'Set DABA Sheet
With Sheets(1)
.Range("S4:S15").FormulaArray = "=ROW()-3"
.Range("S20:S31").Value = Range("S4:S15").Value
'Top Row
For j = 3 To 15 Step 2
Set c = .Cells(1, j)
For r = 3 To 14
Set dly = c.Offset(r).Offset(, 1)
For Each cel In Sheets(c.Value).Range("D2367")
If cel = .Cells(dly.Row, "S") Then
cel.Offset(, -1).Value = dly.Value
End If
Next
Next r
Next j
'Bottom row
For j = 3 To 15 Step 2
Set c = .Cells(18, j)
For r = 2 To 13
Set dly = c.Offset(r).Offset(, 1)
For Each cel In Sheets(c.Value).Range("D2367")
If cel = .Cells(dly.Row, "S") Then
cel.Offset(, -1).Value = dly.Value
End If
Next
Next r
Next j
.Columns(19).ClearContents
End With
For i = 2 To Sheets.Count
Sheets(i).Range("D2367").ClearContents
Next
End Sub
[/vba]

[Beatrix]

Thanks very much for explaining what was causing the problem. I had 2 days eXCEL VBA course last week so it makes better sense now. I need to practise a lot though..

I'll take your advice about comments when I step through the code.

Thanks again
Yeliz