Hi, I wonder whether someone may be able to help me please with a problem I've been struggling with for days.
I'm using the code below to extract specified data (via VB) from a 'Source' sheet "All Data" toa 'Destination' sheet "Monthly Direct".
Sub TMMonthlyExtract()
Dim AFTE As Single
Dim BlnProjExists As Boolean
Dim CMonth As Date
Dim DI As Worksheet
Dim i As Long
Dim IND As Worksheet
Dim j As Long
Dim JRole As String
Dim LastRow As Long
Dim m As Long
Dim OVH As Worksheet
Dim PCode As String
Dim PDate As Date
Dim PLOB As String
Dim Project As String
Dim RLOB As String
Dim Task As String
Const StartRow As Long = 8
Set DI = Sheets("Monthly Direct")
CMonth = Worksheets("All Data").Range("B3").Value
With Sheets("All Data").Range("F4")
For i = 1 To .CurrentRegion.Rows.Count - 1
Task = .Offset(i, -1)
RLOB = .Offset(i, -3)
PLOB = .Offset(i, -4)
Project = .Offset(i, 0)
PCode = .Offset(i, 1)
JRole = .Offset(i, 3)
PDate = .Offset(i, 4)
AFTE = .Offset(i, 8)
If InStr(.Offset(i, 0), "TM - DIR") > 0 And InStr(.Offset(i, 3), "Heads of Consultancy") + InStr(.Offset(i, 3), "Deputy Director") = 0 And RLOB <> "P&P" And RLOB <> "S&A" And PDate = CMonth And AFTE > 0 Then
RLOB = .Offset(i, -3)
Project = .Offset(i, -1)
JRole = .Offset(i, 3)
AFTE = .Offset(i, 8)
With DI.Range("B6")
If .CurrentRegion.Rows.Count = 1 Then
.Offset(1, 0) = Project
.Offset(1, 1) = RLOB
.Offset(1, 2) = AFTE
j = 1
Else
BlnProjExists = False
For j = 1 To .CurrentRegion.Rows.Count - 1
If .Offset(j, 0) = Project And .Offset(j, 1) = RLOB Then
BlnProjExists = True
Exit For
End If
Next j
If BlnProjExists = False Then
.Offset(j, 0) = Project
.Offset(j, 1) = RLOB
.Offset(j, 2) = AFTE
Else
temp = .Offset(j, 2).Value
.Offset(j, 2) = .Offset(j, 2).Value + AFTE
End If
End If
End With
End If
Next i
End With
Columns("B:D").AutoFit
End Sub
Display More
In it's current format, the header row is created in row 6, with the first row of extracted data paste into 1 row below.
The problem I've got is that I would like to change this, so the first row where the extracted data is paste, is 2 rows below the header row.
I must I had quite a bit of help along the way to get this far, and my lack of understanding is really hindering me.
I just wondered whether someone may be able to look at this please and offer some guidance on how I may go about achieving this. If possible, I just wondered whether this could also include some short notes, so that I can learn from this
To make things a little easier, I've attached a file which has the 'Source' "All Data" sheet which the data is extracted from, and a "Monthly Direct Expected Outcome" sheet which is what I would like to create.
If you click the button on the "Macros" sheet, the sheet in it's current form will be created.
Many thanks and kind regards
