GrahamVincent
New Member
- Joined
- Mar 3, 2009
- Messages
- 5
Hi all
I currently have some code that copies and pastes rows, a variable number of times dependant on data in Supervisor Name field, to a new sheet.
What I am looking for is a way of adding a cell prior to the pasted data, populated with 'Call 1, Call 2 etc. which references the number of times the row has been copied.
thanks in advance for any help.
Current code achieves the following
Source Data (Sheet 'A')
[TABLE="width: 492"]
<tbody>[TR]
[TD="class: xl66, width: 207, bgcolor: #BFD2E2"]Employee
[/TD]
[TD="class: xl66, width: 135, bgcolor: #BFD2E2"]Termination Date
[/TD]
[TD="class: xl66, width: 167, bgcolor: #BFD2E2"]Supervisor Name
[/TD]
[TD="class: xl66, width: 148, bgcolor: #BFD2E2"]Teamlead Name
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Smith, Bob
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]Jones, Sue
[/TD]
[TD="class: xl67, bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Jones, Sue
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
If there is an entry in Supervisor Name field, this Row is copied 5 times
If there is no entry in Supervisor Name field, this Row is copied 1 time
Which gives this result;
Sheet 'B'
[TABLE="width: 492"]
<tbody>[TR]
[TD="class: xl66, width: 207, bgcolor: #BFD2E2"]Employee
[/TD]
[TD="class: xl66, width: 135, bgcolor: #BFD2E2"]Termination Date
[/TD]
[TD="class: xl66, width: 167, bgcolor: #BFD2E2"]Supervisor Name
[/TD]
[TD="class: xl66, width: 148, bgcolor: #BFD2E2"]Teamlead Name
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jones, Sue
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
</tbody>[/TABLE]
Code needed to provide;
[TABLE="width: 492"]
<tbody>[TR]
[TD="class: xl66, width: 207, bgcolor: #BFD2E2"]Call Number
[/TD]
[TD="class: xl66, width: 207, bgcolor: #BFD2E2"]Employee
[/TD]
[TD="class: xl66, width: 135, bgcolor: #BFD2E2"]Termination Date
[/TD]
[TD="class: xl66, width: 167, bgcolor: #BFD2E2"]Supervisor Name
[/TD]
[TD="class: xl66, width: 148, bgcolor: #BFD2E2"]Teamlead Name
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Call 1
[/TD]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Call 2
[/TD]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Call 3
[/TD]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Call 4
[/TD]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Call 5
[/TD]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Call 1
[/TD]
[TD="bgcolor: transparent"]Jones, Sue
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
</tbody>[/TABLE]
Current Code
I currently have some code that copies and pastes rows, a variable number of times dependant on data in Supervisor Name field, to a new sheet.
What I am looking for is a way of adding a cell prior to the pasted data, populated with 'Call 1, Call 2 etc. which references the number of times the row has been copied.
thanks in advance for any help.
Current code achieves the following
Source Data (Sheet 'A')
[TABLE="width: 492"]
<tbody>[TR]
[TD="class: xl66, width: 207, bgcolor: #BFD2E2"]Employee
[/TD]
[TD="class: xl66, width: 135, bgcolor: #BFD2E2"]Termination Date
[/TD]
[TD="class: xl66, width: 167, bgcolor: #BFD2E2"]Supervisor Name
[/TD]
[TD="class: xl66, width: 148, bgcolor: #BFD2E2"]Teamlead Name
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Smith, Bob
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]Jones, Sue
[/TD]
[TD="class: xl67, bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Jones, Sue
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
If there is an entry in Supervisor Name field, this Row is copied 5 times
If there is no entry in Supervisor Name field, this Row is copied 1 time
Which gives this result;
Sheet 'B'
[TABLE="width: 492"]
<tbody>[TR]
[TD="class: xl66, width: 207, bgcolor: #BFD2E2"]Employee
[/TD]
[TD="class: xl66, width: 135, bgcolor: #BFD2E2"]Termination Date
[/TD]
[TD="class: xl66, width: 167, bgcolor: #BFD2E2"]Supervisor Name
[/TD]
[TD="class: xl66, width: 148, bgcolor: #BFD2E2"]Teamlead Name
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jones, Sue
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
</tbody>[/TABLE]
Code needed to provide;
[TABLE="width: 492"]
<tbody>[TR]
[TD="class: xl66, width: 207, bgcolor: #BFD2E2"]Call Number
[/TD]
[TD="class: xl66, width: 207, bgcolor: #BFD2E2"]Employee
[/TD]
[TD="class: xl66, width: 135, bgcolor: #BFD2E2"]Termination Date
[/TD]
[TD="class: xl66, width: 167, bgcolor: #BFD2E2"]Supervisor Name
[/TD]
[TD="class: xl66, width: 148, bgcolor: #BFD2E2"]Teamlead Name
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Call 1
[/TD]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Call 2
[/TD]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Call 3
[/TD]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Call 4
[/TD]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Call 5
[/TD]
[TD="bgcolor: transparent"]Smith, Bob
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Jones,Sue
[/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Call 1
[/TD]
[TD="bgcolor: transparent"]Jones, Sue
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Brown, Henry
[/TD]
[/TR]
</tbody>[/TABLE]
Current Code
Code:
Sub CallCalculation()
' Macro to copy and paste a variable number of rows dependant on T/L or Supervisor to a new sheet.
Dim rngSinglecell As Range
Dim rngSupervisorCells As Range
Dim intCount As Integer
' This sets the range for the Supervisor column.
With Worksheets("A")
Set rngSupervisorCells = .Range("C2", .Range("C2:C1000")) '.End(xlDown))
End With
For Each rngSinglecell In rngSupervisorCells
' Checks if Supervisor cell contains a value
If IsEmpty(rngSinglecell.Value) = False Then
' Copy this row 5 times
For intCount = 1 To 5
' Copy the columns A,B,C into the next empty row in sheet(B)
Sheets("B").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(1, 3).Value = rngSinglecell.Offset(0, -2).Resize(1, 3).Value
Next
' Checks if Supervisor cell contains a value
ElseIf IsEmpty(rngSinglecell.Value) = True Then
' Copy this row once
For intCount = 1 To 1
' Copy the columns A,B,C,D into the next empty row in sheet(B)
Sheets("B").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(1, 4).Value = rngSinglecell.Offset(0, -2).Resize(1, 4).Value
Next
End If
Next