I tried your code. I think that we are getting very close. When I run my
macro to the last column of data, the next(blank) cell is activated but the
macro errors. My entire mac code is as follows:
Option Explicit
' How do I return to memory the 'puter power that is used
' by my variables?
' Module Name: NextProduct
' CONTENTS OF THIS MODULE
' 1. NextCookie [Ctrl+Shift+C]
' Calls FunctNextCookie frm keyboard;
' instructs it to un/hide ingreds.
' 1f. FunctNextCookie - FUNCTION
' Runs next SKU thru cookie cost template;
' un/hides rows to display ingreds if so instructed.
' 2. NextCookie [Ctrl+Shift+D]
' Calls FunctNextCookie frm keyboard;
' instructs it to un/hide ingreds.
' 2f. FunctNextCookie - FUNCTION
' Runs next SKU thru cookie cost template;
' un/hides rows to display ingreds if so instructed.
' 3. NextMuffin [Ctrl+Shift+M]
' Calls FunctNextMuffin frm keyboard;
' instructs it to un/hide ingreds.
' 3f. FunctNextMuffin - FUNCTION
' Runs next SKU thru muffin cost template;
' un/hides rows to display ingreds if so instructed.
' 4. PrintAllProducts [Ctrl+Shift+A]
' Runs each product & prints a copy.
' 5. HideZeroUsage
' Hides any raw & pkg ingrediant items w/ 0 usage for
' currently displayed SKU.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub NextCookie()
' Macro recorded 1/3/2002, revised 12/5/2005
' by Bill Riley.
'
' Keyboard Shortcut: Ctrl+Shift+C
'
' Will select C_Data sheet, move cursor one cell to right,
' copy, and paste value on cell C5 of Cookie sheet.
'
' Invokes HideZeroUsage.
FunctNextCookie (True)
End Sub
Function FunctNextCookie(Optional blnHide As Boolean)
Sheets("C_Data").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
ActiveCell.Offset(0, 1).Range("A1").Select
'Selects next SKU.
Selection.Copy
Sheets("Cookie").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage
End Function
Sub NextCookie2()
' Macro recorded 1/3/2002, revised 12/5/2005
' by Bill Riley.
'
' Keyboard Shortcut: Ctrl+Shift+D
'
' Will select C_Data2 sheet, move cursor one cell to right,
' copy, and paste value on cell C5 of Cookie sheet.
'
' Invokes HideZeroUsage.
FunctNextCookie2 (True)
End Sub
Function FunctNextCookie2(Optional blnHide As Boolean)
Sheets("C_Data2").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
ActiveCell.Offset(0, 1).Range("A1").Select
' Selects next SKU.
Selection.Copy
Sheets("Cookie2").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage
End Function
Sub NextMuffin()
' Macro recorded 1/3/2002, revised 1/7/2002
' by Lee Salazar.
'
' Keyboard Shortcut: Ctrl+Shift+M
'
' Will select M_Data sheet, move cursor one cell to right,
' copy, and paste value on cell C5 of Muffin sheet.
'
' Invokes HideZeroUsage.
FunctNextMuffin (True)
End Sub
Function FunctNextMuffin(Optional blnHide As Boolean)
Sheets("M_Data").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
ActiveCell.Offset(0, 1).Range("A1").Select
' Selects next SKU.
Selection.Copy
Sheets("Muffin").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage
End Function
Sub PrintAllProducts()
' by Lee Salazar.
' Keyboard Shortcut: Ctrl+Shift+A
Dim bytGo As Byte
Dim objSheet As Object
Dim strDataSource As String
bytGo = MsgBox("Print cost model for every product?", vbYesNo, _
"Print All Products")
' User's opportunity to back out.
Select Case bytGo
Case vbYes
For Each objSheet In Worksheets
' Loops through all sheets.
If objSheet.Name = "C_Data" Or _
objSheet.Name = "C_Data2" Or _
objSheet.Name = "M_Data" Then
' Skips all but data sheets, so Cookies
' and Muffins each get read once.
objSheet.Select
Cells(3, 1).Select
' Starting point for NextCookie & NextMuffin.
Do
' Keeps reading to right until it runs out
' of SKUs, then moves onto next sheet.
If WhichPlant = "Cookie" Then
FunctNextCookie (True) ' Runs next cookie.
Else
FunctNextCookie2 (True) ' Runs next cookie.
Else
FunctNextCookie2 (True) ' Runs next muffin.
End If
' MsgBox Range("C5").Value
' Uncomment to test macro w/o wasting paper.
ActiveWindow.SelectedSheets.PrintOut Copies:=1
' Prints.
objSheet.Select
' Starting point for next iteration of
' NextCookie or NextMuffin.
Loop Until ActiveCell.Offset(0, 1).Range("A1").Value = ""
End If
Next objSheet
Case Else
' Do nothing.
End Select
End Sub
Sub HideZeroUsage()
' HOW TO BREAK THIS MACRO
' allowing blank spaces in the
' -- Item Desc ranges (raws)
' -- HLOOKUP(row) ranges for the Item Descs (pkg)
Select Case ActiveSheet.Name = "Cookie" Or ActiveSheet.Name = "Cookie2"
Or ActiveSheet.Name = "Muffin"
' prevents macro from running on any sheet but templates.
Case True
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
' Raws
Select Case ActiveSheet.Name
' positions cursor at start of raw materials
Case "Cookie"
Range("E21").Activate
Case "Cookie2"
Range("E21").Activate
Case "Muffin"
Range("E23").Activate
End Select
Do
Select Case True
Case ActiveCell = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -3).Value <> 0
' Packaging
Select Case ActiveSheet.Name
' positions cursor at start of raw materials
Case "Cookie"
Range("E133").Activate
Case "Cookie2"
Range("E133").Activate
Case "Muffin"
Range("E138").Activate
End Select
Do
Select Case True
Case ActiveCell.Value = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -4).Value <> 0
Case Else
End Select
' prevents macro from running on any sheet but templates.
End Sub
Public Function Reset()
End Function
