Macro Not Working- Help!!!!

[bnvetter]

Hello,

I am trying to write a macro that will pull a selection from a drop down, update the information, and print. However, it seems that its not updating the information. It also looks like it zooms by a name here and there.

Any help is appreciated! The code is below.

Sub PrintSupplierScorecards()
Dim ws As Worksheet
Dim i As Long

Set ws = Sheets("Suppliers for macro")

For i = 1 To ws.Cells(Rows.Count, "A").End(xlUp).Row
With Sheets("Key Supplier Scorecards")
.Range("C9:J9").Value = ws.Cells(i, "A").Value
.PrintPreview ' Change to PrintOut after testing
End With
Next i

End Sub

 

[JackDanIce]

Welcome to the board. Edit in blue, try:

Sub PrintSupplierScorecards()

    Dim ws As Worksheet
    Dim i As Long
    
    Set ws = Sheets("Suppliers for macro")

    For i = 1 To ws.Cells(rows.count, "A").End(xlUp).row
        With Sheets("Key Supplier Scorecards")
            .Range("C9:J9").value = ws.Cells(i, "A").value
            .Calculate
            .PrintPreview ' Change to PrintOut after testing
        End With
    Next i

End Sub

To confirm your macro is supposed to set range C9:J9 (are they merged as a single cell?) to the same value as ws.cells(i, "A")? ie. 8 cells inclusive between C9 and J9 will ALL contain the same value as ws.Cells(i, "A")?

 

[bnvetter]

Welcome to the board. Edit in blue, try:

Sub PrintSupplierScorecards()

    Dim ws As Worksheet
    Dim i As Long
    
    Set ws = Sheets("Suppliers for macro")

    For i = 1 To ws.Cells(rows.count, "A").End(xlUp).row
        With Sheets("Key Supplier Scorecards")
            .Range("C9:J9").value = ws.Cells(i, "A").value
            .Calculate
            .PrintPreview ' Change to PrintOut after testing
        End With
    Next i

End Sub

To confirm your macro is supposed to set range C9:J9 (are they merged as a single cell?) to the same value as ws.cells(i, "A")? ie. 8 cells inclusive between C9 and J9 will ALL contain the same value as ws.Cells(i, "A")?

Thank you for your reply. The Drop down has merged cells from C9 to J9. Those names are in the separate worksheet I created which starts on A. I tried to use the change you provided and they still aren't calculating. The sheet which has the drop down pulls data from a separate sheet within the workbook. Do I need to incorporate that sheet into my macro? Im not the strongest at macro writing. I started trying to record one and it wouldn't work. So I googled it and was directed to threads for this site.

 

[JackDanIce]

Without knowing the exact links and what workbooks you're using, can only give suggestions based on the information you *do* provide.

I don't think you need to incorporate the other sheet that contains the values for the drop-down, but merge cells are notoriously difficult to work with when using VBA and I've yet to see a single comment advocating their use with VBA.

Can you try un-merging those cells so the drop down is just in C9 and then try this code:

Sub PrintSupplierScorecards()

    Dim ws As Worksheet
    Dim i As Long
    
    Set ws = Sheets("Suppliers for macro")

    For i = 1 To ws.Cells(rows.count, "A").End(xlUp).row
        With Sheets("Key Supplier Scorecards")
            .Range("C9").value = ws.Cells(i, "A").value
            .Calculate
            .PrintPreview ' Change to PrintOut after testing
        End With
    Next i

End Sub