How to loop based on value given in 2 cells on different sheet

[Akash kothari]

I have attached and sample excel file.
what I am trying to do is to copy data from 'Actual Vs Plan' Tab to 'Burn Macro' Tab If the below condition is Met.

In 'Burn' tab if Division is 01-1000 (cell C3 )then copy the data from 'Actual Vs Plan' tab between the range mentioned below as start range and End range (Call B5 & C5 Respectively). (copy only Level, resource name and Emp ID) PLUS copy the Hours charged by each employee on specific dates ((Mentioned in cells H6 to N6) Actual Hour column for each date will skip same column each week (Means after Evert 4th Column).

Similarly for Next division 01-9000 (Cell E3)

Note: there may be some rows hidden in between I want to skip those.


I am not really good at creating Macro but still managed to create a small part of code in the attached file, but not able to get exact output what I want as I am not able to deal with below line

startrng = Sheets("BurnMacro").Range("B5").Value
endrng = Sheets("BurnMacro").Range("C5").Value

For counter = 16 To 18


I Really appreciate any help on this as it will reduce my daily unnecessary work at office.

Thank you
Akash

 

[Akash kothari]

attached is Macro enabled excel file

 

[Akash kothari]

How can i define below start range as given in attached excel sheet2 to do a Loop ?? Please help



Dim startrng As String
Dim endrng As String
startrng = Sheets("BurnMacro").Range("B5").Value
endrng = Sheets("BurnMacro").Range("C5").Value

For counter = 16 To 20


If Sheets("Actual vs Plan").Range("Q" & counter).Value = Sheets("BurnMacro").Range("C3").Value Then