Dim nextrow As Integer
Dim i As Integer
Dim starttemp As Integer
Dim endrow As Integer
Sub resource1()
starttemp = 1
Dim projectsheet As Worksheet
Dim cel As Range
Dim temprows As Range
Dim startrow As Integer
Dim shtname As String
Dim rows As Integer
'Clear the contents of the temp sheet
Application.ScreenUpdating = False
For Each cel In Worksheets("Temp").Range("A1:T500")
cel.ClearContents
Next cel
Application.ScreenUpdating = True
Worksheets("Res Plan").Select
Range("A1").Select
'search each tab beyond tab 2
For Each projectsheet In Worksheets
If projectsheet.Index < 3 Then GoTo Next1
shtname = projectsheet.Name
MsgBox shtname
projectsheet.Range("aa:1").Value = shtname
'Find the Resource utilisation block on the projectsheet sheet
For Each cel In projectsheet.Range("A1:C200")
If cel.Value = "RESOURCE UTILISATION" Then GoTo Next2
Next cel
Next2:
startrow = cel.Row
endrow = startrow + 9
'Copy the range in blocks of 10
projectsheet.Range("B" & startrow & ":r" & endrow).Copy
Sheets("Temp").Range("A" & starttemp).PasteSpecial xlPasteValues
starttemp = starttemp + 9
Next1:
Next projectsheet
'clear formatting in temp sheet
For Each cel In Worksheets("Temp").Range("a1:r500")
cel.Interior.ColorIndex = xlNone
Next cel
'Remove empty rows
'For Each cel In Worksheets("Temp").Range("A1:a500")
'Set temprange = Worksheets("Temp").Range("a1:r40")
'rows = temprange.rows.Count
'For i = rows To 1 Step -1
'MsgBox i
'If WorksheetFunction.CountA(temprange.rows(i)) = 0 Then temprange.rows(i).Delete
'Next i
End Sub
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE