How to auto-fill until the last row of data based on user-specified input on a separate worksheet

[Alex89]

Hi everyone,

I'm trying to autofill column A in "Master Publisher Content" based on a user-specified value in "Enter Info" in cell H3. The catch is that I only want to fill column A based on the last row of data in "Master Publisher Content." I'm using column C to find the last row of data, but it doesn't seem to be working the way I would like. Any suggestions would be really appreciated!! Thank you so much in advance


Sub CopyMarketName()
'
' CopyMarketName Macro
Dim wksData As Worksheet
Dim lngLastRow As Long


Set wksData = ThisWorkbook.Worksheets("Master Publisher Content")




With wksData
lngLastRow = .Range("C" & .Rows.Count).End(xlUp).Row
Set rngData = .Range("C2:C" & lngLastRow)
End With




ThisWorkbook.Worksheets("Enter Info").Range("H2").Select.Copy
Sheets("Master Publisher Content").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range(lngLastRow), Type:=xlFillDefault
Range(lngLastRow).Select
Sheets("Enter Info").Select




End Sub

 

[JLGWhiz]

You don't really need to use auto fill for that.

Sub CopyMarketName() '
' CopyMarketName Macro
Dim wksData As Worksheet
Dim lngLastRow As Long
Set wksData = ThisWorkbook.Worksheets("Master Publisher Content")
    With wksData
        lngLastRow = .Range("C" & .Rows.Count).End(xlUp).Row
        Set RngData = .Range("C2:C" & lngLastRow)
    End With
ThisWorkbook.Worksheets("Enter Info").Range("H2").Copy
wksData.Range("A2:A" & lngLastRow).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

BTW, when posting code, it is easier to read and work with if you use code tags to hold its format. To apply the code tags, simply select the text of the code, then click the pound symbol (#) in the tool bar of the thread reply window.

 

[Alex89]

Thank you so much for your help! This worked exactly the way I wanted it to.

 

[JLGWhiz]

You're welcome,
regards, JLG