Macro pulling data from 2 worksheets
 

I need your help. I have been using a macro to pull data from only one
worksheet, C_Data. My business has expanded and now I have data in 2
worksheets, C_Data and C_Data2, that need to be pulled by the macro.
The original code for the single worksheet data 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.
' 3. PrintAllProducts [Ctrl+Shift+A]
' Runs each product & prints a copy.
' 4. 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
'
' 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 PrintAllProducts()
' 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"
' Skips all but data sheets, so Cookies
' get read once.
objSheet.Select
Cells(3, 1).Select
' Starting point for NextCookie.
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.

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.
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"
' 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
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
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


--
billrl34
------------------------------------------------------------------------
billrl34's Profile: http://www.excelforum.com/member.php...o&userid=29533
View this thread: http://www.excelforum.com/showthread...hreadid=492822

Macro pulling data from 2 worksheets
 

juding by the code you have probably tried these....simplest way would be
just select the second sheet when required.if its a case of going all the way
through the one and then starting the other you could try

a "for each" loop which would loop through each sheet in the workbook.

set mysheet = worksheets

for each mysheet in workbook(1)
....
your code
....

next mysheet



or you could call the sheets by their number this would call the first 5
sheets one after the other

for a =1 to 5
sheets(A).SELECT
....
your code
.....
NEXT a

"billrl34" wrote:


I need your help. I have been using a macro to pull data from only one
worksheet, C_Data. My business has expanded and now I have data in 2
worksheets, C_Data and C_Data2, that need to be pulled by the macro.
The original code for the single worksheet data 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.
' 3. PrintAllProducts [Ctrl+Shift+A]
' Runs each product & prints a copy.
' 4. 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
'
' 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 PrintAllProducts()
' 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"
' Skips all but data sheets, so Cookies
' get read once.
objSheet.Select
Cells(3, 1).Select
' Starting point for NextCookie.
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.

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.
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"
' 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
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
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


--
billrl34
------------------------------------------------------------------------
billrl34's Profile: http://www.excelforum.com/member.php...o&userid=29533
View this thread: http://www.excelforum.com/showthread...hreadid=492822

Macro pulling data from 2 worksheets
 

I really appreciate the help. Here would I place your suggested code in
my macro?


--
billrl34
------------------------------------------------------------------------
billrl34's Profile: http://www.excelforum.com/member.php...o&userid=29533
View this thread: http://www.excelforum.com/showthread...hreadid=492822