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>

 

[MARK858]

What happens with

Sub MyName()
    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("G9").Value
    shtQC.Copy Before:=wbPPM.Sheets("[Name]")
    wbQC.Close
    Set shtLoad = ActiveSheet

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


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

 

[MARK858]

Or actually

Sub MyName()
    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("G9").Value
    shtQC.Copy Before:=wbPPM.Sheets("[Name]")
    wbQC.Close
    Set shtLoad = ActiveSheet


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

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

 

[Vlr516]

The With statement isn't working properly. It is copying the data on shtReq but not pasting on shtLoad. Instead it is pasting on shtReq. Also I am getting an error on the autofill: Run-time error '1004': Autofill method of Range class failed.

 

[Vlr516]

Also should there be .Value on ReqName when designating the range? I wasn't sure if I needed .Value on any of them.

 

[MARK858]

The With statement isn't working properly. It is copying the data on shtReq but not pasting on shtLoad.Instead it is pasting on shtReq.

There is no copy within the With statement, it is in

shtQC.Copy Before:=wbPPM.Sheets("[Name]")

and I haven't amended anything on that line from what you posted.

Also I am getting an error on the autofill: Run-time error '1004': Autofill method of Range class failed.

Are you using the code in post number 3? You need to check that there are values in Range("C6,D6,L6") of shtLoad and what the value of FinalRow is (which is based on Column B of shtReq) as it needs to higher than 6.

Also should there be .Value on ReqName when designating the range? I wasn't sure if I needed .Value on any of them. .

Excels default is .Value so no but it is good practice to always explicitly define what you want the code to do.

 

[Vlr516]

There is no copy within the With statement, it is in

shtQC.Copy Before:=wbPPM.Sheets("[Name]")

and I haven't amended anything on that line from what you posted.

Are you using the code in post number 3? You need to check that there are values in Range("C6,D6,L6") of shtLoad and what the value of FinalRow is (which is based on Column B of shtReq) as it needs to higher than 6.

I tried both codes and received the same error. I should have clarified what I meant about copying--I meant the autofill which is copying data from shtREQ down the column in shtLoad.

 

[MARK858]

I tried both codes and received the same error

As stated before..

You need to check that there are values in Range("C6,D6,L6") of shtLoad and what the value of FinalRow is (which is based on Column B of shtReq) as it needs to higher than 6.

and what is the ActiveSheet when the line below is run?

 Set shtLoad = ActiveSheet

Because that is what the With statement in post number 3 uses.

 

[Vlr516]

When I have FinalRow above:
Desc = shtReq.Range("D6").Value
ReqNo = shtReq.Range("A6").Value
ReqName = shtReq.Range("C6")
PPMNo = shtTitle.Range("G9").Value

Then FinalRows equals 15. When it is listed after adding the new sheet it is 5. I understand that means it is looking at the current sheet instead of the original one which is an easy fix. However, no matter where I place FinalRow, I haven't ever been able to get the fill down or a loop to work.

As for values for C6, D6, and L6, I am getting only one row of values in every case.

 

[MARK858]

I am sorry then I can't help you as the autofill works fine for me and there is no loop.