Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort data on multiple worksheets | Excel Discussion (Misc queries) | |||
Macro to break data into worksheets | Excel Discussion (Misc queries) | |||
Unique and duplicate data between 2 Excel worksheets | Excel Worksheet Functions | |||
Inserting Filtered RC cell information into other worksheets | Excel Discussion (Misc queries) | |||
Macro data selection line delete | Excel Discussion (Misc queries) |