Looping or Autofill to Final Row in VBA

[Vlr516]

I have worked several days to get my macro to work but itwill not fill down the data to the FinalRow of a referenced worksheet.

Background: a person opens a workbook (wbPPM) and goes to a variable worksheet(shtReq) where the person kicks off a macro. The macro open another workbook(wbQC) with one preformatted worksheet (shtQC) and inserts a new worksheet(shtLoad) into the active workbook and then closes the other workbook (wbQC).The new worksheet (shtLoad) will refer to the data on the variable worksheet(shtReq) which begins on row 6 and the FinalRow is determined by column C,perform a formula to concatenate, and then drop the data into the new worksheetwhich also begins on row 6.

I can get it to do the first line, but not loop or autofill down. I've triedboth.

Sub [Name] ()
Dim wbPPM As Workbook
Dim wbQC As Workbook
Dim shtTitle As Worksheet
Dim shtQC As Worksheet
Dim shtLoad As Variant
Dim shtReq As Variant
Dim ReqNo As String
Dim ReqName As String
Dim FinalRow As Long

Set wbPPM = ThisWorkbook
Set shtTitle = wbPPM.Worksheets(1)
Set shtReq = ActiveSheet
Set wbQC = Workbooks.Open("[Location])
Set shtQC = wbQC.Worksheets("[Name]")

Desc = shtReq.Range("$D6").Value
ReqNo = shtReq.Range("$A6).Value
ReqName = shtReq.Range("$C6")
PPMNo = shtTitle.Range("$G$9").Value
shtQC.Copy Before:=wbPPM.Sheets("[Name]")
wbQC.Close
Set shtLoad = ActiveSheet

With shtReq
FinalRow = shtReq.Cells(Rows.Count, 2).End(xlUp).Row
End With

Range("C6").Formula = ReqNo & "--" & ReqName
ActiveCell.AutoFill Destination:=Range("C6:C" & FinalRow),Type:=xlFillDefault
Range("D6").Formula = Desc
ActiveCell.AutoFill Destination:=Range("D6:D" & FinalRow), Type:=xlFillDefault
Range("L6").Formula = PPMNo
ActiveCell.AutoFill Destination:=Range("L6:L" & FinalRow),Type:=xlFillDefault

End Sub<o></o>

 

[Vlr516]

Thank you for your help. After moving the FinalRow above the Set statements I was able to get 2 out of the 3 autofills to work properly. Yay!

For the last autofill:
Desc = shtReq.Range("D6").Value This is really a merged cells of D6:G on the original sheet.
On the new worksheet:
Range("D6").Formula = Desc This too is really a merged cells D6:G

Therefore the autofill isn't working because it says we can't do that to a merged cell:
Range ("D6").AutoFill Destination:=Range("D6:D" & FinalRow), Type:=xlFillDefault

Without changing the merged cells, is there a different code I can use on this one? The reason for the merged cells is to keep all the columns consistently the same size but allow someone a larger area for particular information.

 

[MARK858]

Afraid I avoid merged cells like the plague.