Running Simple Macro On Multiple Sheets

Oct 22, 2013

I need to run a very simple macro (filter by date, copy, and paste) on every worksheet in a workbook, and then place that data into a summary page at the "end" of the workbook. Most other posts had to do with some kind of formatting that would work in succession on the sheets linearly from left to right (for lack of a better way to put it). My problem, however, is that the data would need to be pasted into the summary worksheet, and then somehow the macro would come back to the next sheet (after the one it just did) and do it all over again, so on and so forth. The worksheets are arranged alphabetically, but other than that there is no sort of easily identifiable progression between them (i.e. Sheet1, Sheet 2, etc.).

View 9 Replies


ADVERTISEMENT

Running Macro On Multiple Sheets

Jun 30, 2006

I would like to run the code below on selected sheets in my workbook:

Range("B9:AW38, AZ9:BE38, b3").ClearContents

I tried using this but it doesn't work:

Sub clear()
Sheets( Array("sheet A", "sheet B", "sheet C")).Select
Range("B9:AW38, AZ9:BE38, b3").ClearContents
End Sub

View 2 Replies View Related

Very Simple Macro Not Running Consistently?

Jul 30, 2012

I have two short macros, shown below. The first changes the font color of the cells in named range BackgroundFont1 to the background color of the cells. The second macro turns the font color in these cells back to black.

I call these subs in other macros. The first one works all the time. The second one works only occasionally - more often than not it just hangs up Excel entirely and shuts down the program. It happens even if I just run the BlackFont macro on its own.

The error message I get at times is "Method color of Object Font failed".

Macro 1

Code:
Sub BackgroundFont1()
For Each cell In Range("BackgroundFont1")
cell.Font.Color = cell.Interior.Color
Next cell
End Sub

Macro 2

Code:
Sub BlackFont()
For Each cell In Range("BackgroundFont1")
cell.Font.Color = vbBlack
Next cell
End Sub

These are the cells in the ranged named BackgroundFont1.

Code:
=Demo!$JY$581:$KI$590,Demo!$JY$599:$KI$602,
Demo!$JY$612:$KI$615,Demo!$JY$624:$KI$625,
Demo!$JY$643:$KI$644,Demo!$JY$656:$KI$658,
Demo!$JY$667:$KI$669,Demo!$JY$677:$KI$679,

[Code] ......

View 6 Replies View Related

Running Macro Based On Sheet Name (Multiple Sheets)

Dec 20, 2012

I have multiple sheets in one file. I need a code (that will be ran at random) to look at all the sheet names and the ones that end in the word "Archive" to move to another file.

View 5 Replies View Related

Running Multiple Modules Across Multiple Workbooks With Multiple Sheets

Feb 19, 2012

I've got data being scraped from a site, putting 1 new workbook in a folder each day

each workbook has 40 sheets in it.

i need to run 5 modules in sequence on a sheet then loop to the next sheet and run the same 5 modules.

ive writen all the modules, and can loop them through the sheets in sequence but i cant work out how to loop them through the each workbook in the folder..

is there an easy way to do this or can it not be done because it would need access to the folder that holds all the wordbooks which lives outside of excel on the desktop ?

View 5 Replies View Related

Running Same VBA Over Multiple Sheets

Jan 12, 2008

I have some code which I want to run on all apart from one sheet called 'List'. At the moment I am selecting each sheet before running code for the specified sheet. This doesn't allow for any sheet name changes etc. or additional sheets. Is there a quicker way of doing this rather than having a sub to manually select each sheet?

Here is an example of my code for a button on the sheet called 'List'. The only thing Sheet1, Sheet2 does in this code is select that sheet.

View 14 Replies View Related

Sheets Are Flickering Even No Macro Running

Oct 27, 2008

Some of my sheets are flickering even when there is no macro running. It blinks and flashes from time to time. It does not affect the funktion in the sheet except that its annoying as hell. Some times I have to scroll down and back up to "clear" the screen from the flicker/flash effects.

I can open an old file fresh and it flickers and flashes from start. It doesnt matter if I start it with or with out macro activated.

View 9 Replies View Related

Running Simple VBA Code Causing Screen Errors

Nov 30, 2009

I have a rather large workbook (30 sheets, 10MB) that has one worksheet with many INDIRECT functions in it (pulling data from the same file, different tabs). I am working to put simple code into the workbook to protect and/or unprotect all worksheets. I have gotten code to work to both protect and unprotect all the sheets, but when I run the unprotect code (see below), and then I go to edit the workbook, data from the sheet with many "indirect" functions temporarily "overwrites" the data on the active sheet (this is fixed when I scroll my mouse over the effected cells). I am developing this workbook for other users, so I'd like to fix this before sending it off to them.

This problem does not happen until after I run the following .....

View 14 Replies View Related

Running One Macro For Multiple Workbooks?

Apr 7, 2014

I have approximately 30 workbooks for which i open and run a macro to change the look and feel for client perusal.

I normally open each of the 30 workbooks and run the macro for each one.

Is there a way I can run the macro to apply to all workbooks at once?

View 3 Replies View Related

Running Macro In Multiple Worksheets?

Dec 20, 2013

I am seeking code that will go to Sheet 4 (named "Extract"), copy a1:a5 and return to the active cell the macro was launched from and copy the values in.

The challenge I am having is how to get the code to return back to the cell it was launched from since I want the flexibility to use this macro in multiple tabs/sheets to copy the info into ANY selected cell within ANY sheet. As it is now, I have to go change the sheet name each time for the then active sheet.

I've attempted to insert a generic worksheet reference, however I end up with a debug issue.

Sub Macro10()
'
' Macro10 Macro

[Code].....

View 2 Replies View Related

Running Macro Over Multiple XLSX Files

Oct 2, 2013

how to run my Macro over multiple (about 60) .xlsx files. I have been looking around, but haven't quite got anything working yet.

I have all the files in the same folder with similar names (i.e. Subject_01.xlsx, Subject_02.xlsx, etc.).

What I'd like is:

*open Subject_01
*run script (some calculations, copy/pasting, my script for this is working fine)
*save file under a different name (i.e. Subject_01_processed.xlsx)
*close file
*again for Subject_02, Subject_03, etc.

Additionally, I have a second question: after I have the processed .xlsx files, I'd like to copy some cells from each .xlsx file (e.g. A1-H1) to a new file. There, I'd like the data from Subject_01 to for example be in cells A1-H1, from Subject_02 in cells A2-H2, etc.

View 5 Replies View Related

Running Multiple Macros With A Single Macro

Oct 5, 2007

I have 3 seperate macros (macro1, macro2, macro3) that have to be run in order. Is it possible to create a macro to run them in order and what would the code look like? I will have it assigned to a button on the worksheet.

View 9 Replies View Related

Running Macro Across Multiple Files In Same Folder

Apr 7, 2009

I would like my macro to go to a certain folder- in this case N:Aexeo ClientsJabre2008Excel Diet Run, open each workbook therein ( to this end I have found the first part of the code below on another thread) and perform the ExcelDietMacro (also below). I am missing something though as nothing is happening, would anyone know from a quick glance what is wrong? Should I have these as 2 seperate Sub End Subs or combined into one? I know that the Excel Diet is correct thanks to the original designer and Rory on the forum for getting it adjusted to my needs.

Also each workbook within the folder will have the same password to open, is it possible to insert some code in the macro to do this automatically?

Sub LoopFiles()
Dim MyFileName, MyPath As String
Dim MyBook As Workbook
MyPath = "N:Aexeo ClientsJabre2008Excel Diet Run"
MyFileName = Dir(MyPath & "*.xls")
Do Until MyFileName = ""
Workbooks.Open MyPath & MyFileName
Set MyBook = ActiveWorkbook
Application.Run "ExcelDietMacro"
MyBook.Save
MyBook.Close
MyFileName = Dir
Loop
End Sub

Sub ExcelDietMacro()
'
' ExcelDietMacro Macro.........................

View 9 Replies View Related

Running Multiple Pivot Tables With Same Macro

Aug 31, 2006

I am trying to find a way to set up a macro that will allow me to pull in data - create the Pivot table - delete the table - then pull in fresh data (of a different row length - same number of columns) and create another Pivot table. I have tried to manipulate recorded code to no avail. Here is what I am starting with:

Const lngLastPossRow As Long = 65536
Range(Cells(1, 1), Cells(Cells(lngLastPossRow, 1).End(xlUp).Row, 24)).Name = "Data"

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Data"). _
CreatePivotTable TableDestination:="", TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array(" ", _
"Channel", "Sales/Exchange", "Mode of transp.", "Sold-to Party", "Material", "Data")

While it creates the first pivot table fine, after deleting it and starting again it wants to create the next sequence (PivotTable3) which crashes the macro. I must close the file and reopen to run it again.

View 4 Replies View Related

Running A Macro To Copy Multiple Cells From A Row By Selecting A Cell

Feb 21, 2009

I want to be able to select a cell, for example A10, this would then run a macro that would copy and paste several cells of information along that row and paste it on another worksheet. However, if i then choose a15, a3, a79 in turn (using the control key), it would run the macro using those rows in turn.

i'm looking forward to hearing if and particuliar how this could be achieved.

View 13 Replies View Related

Simple IF Formula And Linking 2 Sheets

Feb 9, 2009

So I have a list of names, and I need to keep track of whose paperwork I have. There's a column that says Yes/No.

I would like to link everything to another sheet in the file. So for every name that says "No," I would like that name to appear in the next sheet. And when someone updates the first page to put in a "yes," I want it automatically taken off the second sheet.

So.

I have Sheet 1:....

View 14 Replies View Related

Simple Array Formula Between Two Different Sheets

Sep 25, 2013

I'm working on simple array formula between two different sheets.

I'm working on Sheet 1 and sheet 2

Right now I want the result on sheet 2 C9

Sheet 2 C6 has MTR
Sheet 1
c6 MTR
c7 MAR
c8 MTR

E6 2
E7 4
E8 6

Now on Sheet 2 C7 has 10

Now the result Sheet C9 has to get 8

=ARRAYFORMULA(SUM(IF(C6="+Sheet1!$C$6:$C$8",Sheet1!$E$6:$E$8,Sheet2!C7)))

I want this formula to work on both excel and Google Drive but now this formula doesn't work on any one.

View 4 Replies View Related

Excel 2010 :: Macro To Save Multiple Sheets To Multiple PDF With Cell Value As Filename

May 6, 2014

I would like a macro to be able to save 26 tabs within the one document to individual PDFs.Preferably I would like to be able to specify each time exactly which tabs get printed, because often I don't need to print all 26, just the first 10 or so.I would like each PDF to automatically be named with the value in cell E10 of each tab.E10 already has a formula to create its final value. It references cells from other tabs within the same document. Hopefully the fact that this cell has a formula in it won't affect my ability to use the resulting value as a 'save as' reference?I would like it if the PDFs save to the same location as the Excel sheet from which they're generated is located. The location of the excel sheet will change every three months, so I'd prefer not to specify a location with a specific filepath, as it will have changed by the time I run the macro again.

I am using Excel 2010.

View 5 Replies View Related

Run VB Macro On Multiple Sheets

Apr 17, 2009

Hi i have this macro below which works perfectly to clear the contents of column A. I will have multiple worksheets all named from 1,2,3,4,5 upto 50.

What i would like to do is modify this script below so it will run on the worksheets labelled above.

View 7 Replies View Related

Run Same Macro On Multiple Sheets?

Sep 8, 2012

I have a macro and I want to run that macro on multiple sheets. I don't know how to select multiple sheets. I have selected for example three worksheets.

I have used the following code but the code runs only on sheet 10. what do I need to do to make it work on all the sheets ?

Code:
Sub multiplemacro()
Dim wsh As Worksheet
Sheets(Array("Sheet10", "Sheet11", "Sheet12")).Select

[Code].....

View 3 Replies View Related

Macro To Get Min / Max/ Avg Values From Multiple Sheets

Jun 27, 2014

I have multiple sheets and a summary sheet in the beginning. and i need to populate the min value / max value and the avg value for every sheet into the summary sheet.

Example i have a column of numbers in column G and i need the min , max and round(avg) for all sheets in the first summary sheet.

View 2 Replies View Related

Macro For Data In Multiple Sheets?

Mar 21, 2014

I want to consolidate the various employees' salaries of all months in a sheet. I enter salaries in different sheets month-wise and in each sheet, department-wise. Some employees get commission in various departments. Now, I need to see the details of an employee by giving his name. I should get month-wise his salary, commission and department in which he get commission, across all the sheets.

View 1 Replies View Related

Macro For Printing Multiple Sheets

Mar 13, 2008

Print sheet 1, 3 and 7. Always print sheet 1, however only print sheet 3 and 7 if there in these sheets are values in the cells from row 8 and below.

(If that is to complicated it would be ok if the condition for printing sheet 3 and 7 is that there's a value in e.g. cell A8.)

I managed to create this script that allows me to print sheets 1, 3 and 7, however I can't seem to find out where to put the if-statement (I suppose that's how you do it?). Here's the script i created so far:

View 9 Replies View Related

Macro To Combine Multiple Sheets Into One

Jun 29, 2011

I have an excell spreadhseet that has more than 100 sheets and I would like to combine all these sheets into one master sheet (Sheet1 = MasterSheet) within this workbook. Each sheet has different number of rows used. I just want used ranges to be copied over to a master file appending the previous copied range.

Sub MergeSheets()
Dim strSheet As Object
Dim LR As Long, LC As Long
Sheets("Sheet1").Name = "MasterSheet"
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
For Each strSheet In Sheets
If strSheet.Index 1 Then

[code]....

View 5 Replies View Related

Macro With Information From Multiple Sheets?

Dec 30, 2012

I am writing a macro that organizes data, but also needs to be able to conditionally copy data from other sheets in a workbook.

I have a table with values...column A has the identifiers (strings of letters) and column N has comments related to the data in each row. So, for example, row 3 column A has the value "AAPL," and row 3 column N has the related value "sells electronic goods."

Now, I want to be able to copy column N based on the value of column A into other sheets using my macros. So, say I have another sheet in the workbook and row 6 column A has the value "AAPL," I want row 6 column N to have the related value from the previous sheet. Is it possible to do this?

View 2 Replies View Related

Using Macro For Multiple Sheets In Same Workbook?

Oct 18, 2013

I have a macro listed below that I would like to use on multiple sheets within the same workbook.

Sub PrintMacro1()
'
' PrintMacro1 Macro
' To change print format from landscape to protrait

[Code].....

View 4 Replies View Related

Macro - Hide Multiple Sheets

Mar 10, 2014

What would be the macro if i need to hide multiple select sheets? I'm working on a test with 12 sheets. Even number sheets contains the fields that they need to answer and the Odd number sheets contains the formula for score computing including the answers so it needs to be like this:

Sheet 2 - The test
Sheet 3 - Must be hidden
Sheet 4 - The test
Sheet 5- Must be hidden
Sheet 6- The test
Sheet 7 - Must be hidden

I already have the code to unhide all sheets, just need the macro to hide specific sheets like the ones above.

View 2 Replies View Related

Creating Multiple Sheets From A Macro

Dec 30, 2006

I am creating a vacation calendar for all of my associates. I have 763 employees so, i want to run a macro that will react a worksheet for each of them. I will have an employee list that will create the sheets and I will have a VLOOK Up to update and pull information when we have new hires come on board.

View 9 Replies View Related

Run A Macro In Multiple Sheets At The Same Time

Jun 13, 2007

I have to run the macro in over 75 sheets every month and they are divided in few workbooks. The workbooks have 2 summary sheets and then the sheets I need to run the macro. Can you help me to run the macro in all of these workbooks running it just once?

Sub TelcoTicketsCleaning()
'
' TelcoTicketsCleaning Macro
' Macro recorded 6/13/2007 by EQUANT
'
Dim lastrow As Long
lastrow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For r = lastrow To 46 Step -1 'Remove rows with DSL, ISDN and PSTN services..................

View 9 Replies View Related

Run Same VBA Macro Code On Multiple Sheets

Aug 10, 2009

I am trying to run the following code on multile sheets in a workbook. so far this code is working fine for one worksheet. Can someone help me modify this so it runs on multiple sheets. There can be more then 1 worksheet in a workbook depending upon data ...

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved