Macro to update cells based on a named range

**bomex1018** · 10-10-2012, 07:55 PM

I currently have a print macro that looks for blanks in column A hides them and prints(shown below). Now i want to add to this macro to have cell B4 update for everything in my named range (named range is List).

If my named range consisted of 5 items (A,B,C,D,E) I want to macro to put "A" in B4 then print (I have formulas looking at cell B4). Then put "B" in cell B4, and print. And so on.

Please Login or Register  to view this content.

Thanks,
Jeff

**rylo** · 10-10-2012, 08:58 PM

Hi

I'm assuming that you want the printout loop to happen after the row hiding so add something like

Please Login or Register  to view this content.

HTH

rylo

**bomex1018** · 10-10-2012, 09:12 PM

Hi Rylo,

I want it to loop after all the rows are unhidden. My marco hides anything in column A that is blank..prints then unhides all rows. The new piece i am looking for will update b4 with the numbers from my named range (my named range is in sheet 1 and b4 the cell that i want to update is in sheet 2 - b4 is also a data validation list) and re run my print macro. I want it to loop all the way through my named range.

**rylo** · 10-10-2012, 09:29 PM

Hi

Ok, so add that code block after you have unhidden the rows.

rylo

**bomex1018** · 10-10-2012, 09:46 PM

I am now getting a run time error with the code below. It is kicking out at Range("B4").Value = ce.Value

Sub Alloy()
Application.Calculation = xlManual
Application.ScreenUpdating = False
Range("A:A").EntireRow.Hidden = False
Dim currentRow As Object
For i = 1 To 15 'Range("A:A").Rows.Count
If (Range("A:A").Rows(i).Text = "") Then
Range("A:A").Rows(i).EntireRow.Hidden = True

End If
Next i
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Range("A:A").EntireRow.Hidden = False
For Each ce In Range("List")
Range("B4").Value = ce.Value
ActiveSheet.Calculate
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Next ce
Application.ScreenUpdating = True

Application.Calculation = xlAutomatic
End Sub

**rylo** · 10-10-2012, 09:50 PM

Hi

What happens if you run

Please Login or Register  to view this content.

Does it give you the individual entries in the named range "List" or error. If it errors, then where is the named range?

rylo

**bomex1018** · 10-10-2012, 09:53 PM

The other code works if i put my name range in sheet 2 ..but i need my name range in sheet 1..and i need the macro to run in sheet 2

**rylo** · 10-10-2012, 10:07 PM

Hi

Don't know if that is a 2010 thing. It works in 2007. Maybe try

Please Login or Register  to view this content.

and see if that works when in sheet2.

rylo

**bomex1018** · 10-10-2012, 10:20 PM

Im actually using 2003 at home right now. I think your right it should recognize my name range from sheet1 in 2010. Ill let you know tomorrow (have 2010 at work)..thanks for your help i really appreciate it.