VBA Code To SUM Range On Specified Worksheets

Aug 2, 2006

I want to try and do is as follows:

- go through all the worksheets in the workbook
- if the sheets begin with A, C or P
- and if monthly index = 0
- sum the range of cells - B33:L51
- output the value into cell L52
- etc

So far this is what I got

For i = 1 To Sheets.Count
If Left(Sheets(i).Name, 1) = "A" Or Left(Sheets(i).Name, 1) = "C" Or Left(Sheets(i).Name, 1) = "P" Then

If (MonIndex = 0) Then
SUM(B33:L51)
ElseIf (Index = 2) Then
SUM(B34:L51)
ElseIf (Index = 3) Then
SUM(B35:L51)
End If

My questions really are - how do u write the code for it to generically know to sum the range B33-L51 for like all sheets required. - and is it possible to output that value to a specific cell in each sheet?

ADVERTISEMENT

Copy Cells / Range From Worksheets Positioned Between Two Worksheets

Jul 7, 2014

Let's say I have a workbook with 7 worksheets named, for example, "Instruction", "Begin", "Worksheet 1", "Worksheet 2", "Worksheet 3", "End", and "Data". (in that order)

What I want to do is run a macro to go to whatever worksheet that is in between "Begin" and "End" and copy, for example, cells $C$1:$D$10; then paste as formula into worksheet "Data" starting from cell C1 and then down a list (i.e., copied cells from "Worksheet 1" get pasted as formula into "Data" cells C1:D10; then copied cells from "Worksheet 2" get pasted as formula into "Data" cells C11:D20, and so on and so forth).

But if I were to add more worksheets (e.g., "Recipe" and "ToDo") positioned in between "Begin" and "End" and run the macro again, it'll either 1) re-copy all the formulas from the included worksheets back into "Data" including the formulas from the newly added/placed worksheets or 2) it'll add the formulas from the newly added/placed worksheets and paste into "Data" at the end of the list.

Can create the macro to run based on the position of worksheet, and not based on the name of worksheet, since ultimately there will probably be over 10 worksheets between "Begin" and "End".

View 4 Replies View Related

VBA Run Code Between Worksheets?

Dec 14, 2012

run a code that passes the word alarm from worksheet 3 to worksheet 2 as 100 instead of as the word alarm..... but i need it that it can copy the number 100 on any cell range between cell 2 to cell 10 on worksheet 2 depending on the other data i copy

View 1 Replies View Related

Loop Code Through Certain Worksheets?

Nov 15, 2013

I am trying to loop the following code for a total of 15 worksheets without copying and pasting that same code 14 more times for each worksheet. Right now it is only executing the code on the "CAN" tab. Is there a way to make it loop where indicated below?

The 15 worksheets are:
CAN
USA
ASG
Gallia

[Code]......

View 3 Replies View Related

Code For If Statement Between 2 Worksheets.

Sep 27, 2009

On Sheet1 I have a macro link. On the Setup sheet, in cell C14, I have the name I want to display in the link on Sheet1. The wording is changeable in the Setup sheet. This all works fine.

Now, I want to modify it. I want an if statement at the beginning of the macro that says. If Setup, cell c14 is blank, I simply want the macro to stop and do nothing. If Setup sheet, cell c14 is not blank, I want the macro to continue as normal.

View 2 Replies View Related

VBA Code For Worksheets Within Workbooks

Jan 31, 2012

For example, I have 50 clients. In 1 workbook, I have a sheet for each client. When I'm finished with a particular client, I need their one sheet to place in their file and be done with it.

The current process is to open that 1 global workbook, copy and paste the sheet I need into a new workbook and go from there.

I was wondering if there is a simpler way to achieve this with VBA coding?

Ideally, I would like to click an object button and then be prompted to select a worksheet from a list that contains all current non-hidden worksheets.

Once I select a worksheet, I would then be prompted to save as and select a file path. That would save that specific client worksheet in its own file that I selected.

View 7 Replies View Related

Consolidation Of Worksheets Using VBA Code

Sep 19, 2012

I have many sheets in an excel workbook. Each sheet contains some data. I want data of all sheets into one sheet in the same workbook. The data of sheet 3 should appear below the data of sheet 2 & so on. I leave the first sheet of the workbook blank so that data of remaining sheets can be consolidated in the first sheet. Therefore I wrote the following code:

Public Sub consolsheets()
Dim a As Integer
Dim rng, rng2 As Range
a = a + 1
For a = 2 To Worksheets.Count
Worksheets(a).Activate
Set rng = Worksheets(a).UsedRange
rng.Copy

[code].....

Data of row 3 of sheet 2 (ccccc) is overwritten by data of row 1 sheet 3 (11111). Similarly, data of row 3 of sheet 3 (33333) is overwritten by data of row 1 of sheet 4 (@@@@@).

This problem is not faced if the sheet 1(blank sheet) has some data.

View 2 Replies View Related

Run Code On Worksheets With Certain Tab Names

Aug 11, 2007

My Thread title should have read covert code TO run on all worksheets with same word in Name. I have had a look at some other pieces of code in this forum but they are quite the same as I want to do....I have tried a few things but each time I get an error ...so for sure I am not modifying the code quite right, I ahve been missing something for what I now want it to do. So I wonder if someone could Please review these two pieces of code...they of course are both for running on just one worksheet in a wookbook.

I would like them both be able to run, still as tow separete pieces of code as they are, on all and only worksheets that have in their worksheet name a key word in this case the word "Region" and that those worksheets all in a single workbook but the workbook does have other worksheets in it I dont want the code to run one on - but those worksheets do not have in their worksheet name the word "Region".

Sub test()
Dim r As Range, txt As String, ws1 As Worksheet, i As Long
Set ws1 = Sheets(1)
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For i = 16 To 21
Again:
For Each r In ws1.Range(ws1.Cells(6, i), ws1.Cells(Rows.Count, i).End(xlUp))
If r.Value <> "" Then
If Not .exists(r.Value) Then
.Add r.Value, Nothing
Else
txt = txt & "," & r.Address(0, 0)
If Len(txt) > 245 Then
ws1.Range(Mid$(txt, 2)).EntireRow.Delete
txt = "": .RemoveAll: Goto Again
End If .............

View 9 Replies View Related

Code Running For Multiple Worksheets

Jun 27, 2014

I have the following code, I have it running to just work on Sheet 2 but I would like it to be able to run on mulitple sheets, not all but a range like from Sheet2:Sheet30.

[Code]........

View 12 Replies View Related

VBA Code On Transferring Data To Different Worksheets?

Jun 29, 2014

What I am wanting to do is to transfer Names(Row B7:B160) to a different work sheet IF the Row next to it(C7:C160) is filled out with a time. I will be setting up a command button that once say 10 names have times next to it, this data then will transfer to another work sheet.

I have done below coding, however it will only transfer data in B7 and C7, if I try to add B7:B160 it comes back as Missmatch. Below is what I have done for the Command button.

Private Sub CommandButton1_Click()
Dim AdviserName As String, Time As Integer
Worksheets("Sheet1").Select

[Code]....

View 6 Replies View Related

VBA Code To Run Macro In All Worksheets Individually

Feb 28, 2014

I have data on sheet "master", which with the macro (Ctrl+Q), splits in unique values in tabs and another macro sub Send_files() (Ctrl+e) triggers a selection in each split-sheet in email body to recipient.

Problem arises when I want to run a macro which collectively send files after running in each sheet. A simple code would be:

Sheet2.Activate
Application.Run "Test2.xlsm!Send_Files"

Sheet3.activate
Application.Run "Test2.xlsm!Send_Files"
etc.

But I don't know how much list of sheets can go on. So I want Sub Send_Files to run on all sheets in workbook after splitting data from master.

Refer attached wb Test2.

View 5 Replies View Related

Runtime Error With Code To Name Worksheets

Dec 27, 2012

I am trying to run code to update Excel Worksheet from the value in a cell.

The code is -

Sub upDateSheetNames()

'Standard Module code, like: Module1.
Dim ws As Object

For Each ws In Worksheets
[Code] ......

Although it does actually work i.e. populates the worksheets with the correct values when I run the code a dialogue box appears indicating - "Run-Time Error '1004' Application Defined or Object Defined Error"

When i select the 'Debug' option the "ws.Name = ws.Range("Q1").Value" line is highlighted.

View 9 Replies View Related

Adjusting VB Code To Combine Only Certain Worksheets

Mar 26, 2014

I saw the below code in another thread that does almost what I need it to do. The only thing is that I need to only select certain worksheets, not all. Is there an adjustment I can make to this code or is there something I can do differently?

Each worksheet has a table on it as well, is there a code I could use to just combine certain tables?

Sub debit1()
'Combine all worksheets to the Summary sheet
'Created by Trevor G 30 June 2011
Dim ws As Worksheet

[Code]......

View 7 Replies View Related

Creating Worksheets And Copying Code

Sep 2, 2008

My macro does some calculations for a worksheet. I need the macro to loop through all the worksheets regardless of how many worksheets there are. My first sheet is a summary page with the names of the subsequent sheets that the macro needs to do calculation on. I need the macro to recognize the worksheet names in the summary page and run for each worksheet name. For instance the next time I run the macro I may have fewer sheets of more sheet names in the column. It sounds almost like an Indirect function problem.

View 9 Replies View Related

Code To Sort All The Worksheets In A Workbook

Oct 8, 2008

code to sort all the worksheets in a workbook...

View 13 Replies View Related

Modify Code To Create Worksheets

Dec 12, 2008

I have this code that will create 3 worksheets with the name "New Name" and the # ( 1/2/3 ) ..

How would I change the code if I want 3 worksheets with different name?
Like Sheet1 then Sheet 2 then Sheet3 or any other name?

'To add worksheets and change name with one code
For i = 1 To 3 'Creates 3 worksheets
Set ws = Worksheets.Add
ws.Name = "New Name" & i
Next i

View 9 Replies View Related

Running Code On Specific Worksheets

Dec 4, 2009

Let's say I have 6 Worksheets in my Workbook with the following names:

Sheeta1
Sheeta2
Sheeta3
Sheetb1
Sheetb2
Sheetb3

And I have three Subs that I want to run, one Sub for two specific Worksheets.

How could I code my Subs so that the first Sub runs on Sheeta1 and Sheetb1, the second Sub runs on Sheeta2 and Sheetb2, and the last Sub runs on Sheeta3 and Sheetb3?

As you can see, the 1, 2 and 3 in the Worksheet names are what links the Subs to the Worksheet.

View 9 Replies View Related

VBA CODE On Pasting On Last Row Of Data For Various Worksheets

Jun 9, 2006

I have got a workbook with 20 or more worksheets. Every quarter I need to paste/append new data on these worksheets (which has already got existing data from previous quarters). All worksheets have different rows of data, for eg One worksheet may have 10rows, the other may have 50. Hence when I am writing my code, I cannot specify the destination cell as for eg A11, or A51.

How do I write up a VBA code that looks for the last row of data on these various worksheets and then pastes the new information on the next row(new row).

View 5 Replies View Related

Apply Code To All Worksheets In Workbook

Nov 20, 2007

I recorded a macro in an Excel Workbook which contains 65 worksheets (--this is something received on a quarterly basis for which I have no control). The macro is successful within the workbook created however, a new quarter's data may or maynot have some of the referenced worksheets. 64.9 Waiv - 1 may exists and the others (2, 3, 4, etc) not. The macro fails and prompts for Debug if a worksheet does not exist.

Sheets("64.9 Waiv - 1").Select
Sheets("64.9 Waiv - 2").Select
Sheets("64.9 Waiv - 3").Select
Sheets("64.9 Waiv - 4").Select
Sheets("64.9 Waiv - 5").Select
Sheets("64.9 Waiv - 6").Select
Sheets("64.9 Waiv - 7").Select
Sheets("64.9 Waiv - 8").Select
Sheets("64.9 Waiv - 9").Select
Sheets("64.9 Waiv - 10").Select

Is it possible to alter the macro to look for and only invoke the code if a worksheet past 1 were to exist?

This is what I am requesting the macro do:

Sheets("64.9 Waiv - 1").Select
Range("B9").Select
ActiveWindow.FreezePanes = True
ActiveWindow.SmallScroll ToRight:=5
Range("J8").Select ...............

View 9 Replies View Related

Function To A Range Of Cells Over A Range Of Worksheets

Mar 31, 2009

I have a workbook with several worksheets in the same format. I would like to have a function to output the sheets that have rows in which collumn A = X AND collumn B = Y.

I've been working on this one for a couple days now and I'm not making much progress.

View 9 Replies View Related

Find And Replace Vba Lines Of Code In All Worksheets?

Sep 11, 2012

I have spreadsheet application which have a ton of sheets and macro into it, and i work on devlop and program it , and add new feature and codes

i wana Module_Macro to find and replace lines of vba code and replace it with lines of codes .

i know ,i should use vbide and vbcompnent , but i dont know from where to start ?

View 9 Replies View Related

VBA Code For Printing Worksheets That Are Hidden From A Selection

Jan 19, 2013

I have a workbook with various pages that are all hidden except the main page, on the main page it allows users to select items froms drop down boxes that returns a figure to cell B7 on the selection page.

What i would like to do is press a command button and the hidden worksheet that relates to that figure in cell B7 opens which allows the users to print it then after printing or closing the workbook is hidden again.

View 4 Replies View Related

VBA Code For Summing Up Cells On Multiple Worksheets

Jan 13, 2014

I have made a basic holiday planner, where each employee has one worksheet which shows 52 x 5 day weeks.

When an employee asks for holiday, I put a 1 in the corresponding day (cell). What I need to happen is that if 4 first aiders all ask for holiday on the the same date a warning User form loads up.

The names of the 4 worksheets are;

"Jason L 2014", "Sharon L 2014", "Alan B 2014" and "Chris M 2014".

The ranges for the 5 day weeks are

"B7:F7", "L7:P7", "V7:Z7"

"B10:F10", "L10:P10", "V10:Z10"

"B13:F13", "L13:P13", "V10:Z13"

"B16:F16", "L16:P16", "V16:Z16"

[Code] ........

The code should not run the User form until the sum of the same cells across the 4 worksheets = 4.

By this I mean if 3 of the first aiders book B7 off, the code searches the 4 worksheets and if the sum is 3 then nothing happens. However if the 4th and final first aider tries to book the same day off, when the code runs it totals 4 and the User form runs.

View 1 Replies View Related

VBA Code- To Pull All The Names Of The Worksheets In A Workbook

Apr 5, 2007

I just started playing with creating my own macros. I've written a macro to pull all the names of the worksheets in a workbook, now what I want to do is write a piece of code that will pull data from a cell in those worksheets.

So for instance I have all my worksheet names in cell A1 to A10, I want to pull the data from cell E1 from every worksheet in A1 to A10. Can someone help me with a sample code to work with. I'm still reading Power Progamming haven't gotten so far yet.

View 9 Replies View Related

VBA Code To Unmerge Cells On Specific Worksheets

Jan 27, 2010

I have a workbook with multiple worksheets. On some of those worksheets a have merged cells, so in order to paste new data I need to unmerge them first. Below is the code that I wrote for it. However it doesn’t work and returns “Run-time error '1004': Application-defined or object-defined error”. I looked at this post List Maximum Value From Each Worksheet and tried adding With – End With, but it failed too. As far as I understood the problem is that the Worksheets/Sheets object does not support UnMerge property, but I may be wrong. If I add ws.Activate line (test2) everything works fine.

However I was wondering if there is a way to do it WITHOUT activating the worksheets.

Sub test1()
Dim i As Integer, ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Name
Case "5 - Top Network Facilities", _
"5b - Top Arb Facilities"
For i = 0 To 9
ws.Range(Cells(2 + i * 5, 1), _
Cells(6 + i * 5, 1)).UnMerge
Next i
Case Else
End Select
Next ws
End Sub

View 4 Replies View Related

Using Command Buttons To Change Vba Code On Worksheets

May 24, 2006

I am trying to create a program to automate the gauging figures on oil barges, currently everything is done by hand and takes approximately 20 minutes to complete. I have the charts entered for the tanks already and have the code set so that when you click on a tank "gauge" it will enter the "volume" which corresponds to that gauge on a totals sheet. Here is where my problem is coming in

The barges are gauged at four points
"before loading"
"after loading"
"before discharge"
"after discharge"

I want to set a worksheet as the default page with four command buttons that let the user select which operation he wants to perform. before load figures. after load figures and depending on which operation they select have it enter the volumes in the appropriate cells on the totals sheet. So if someone selects "loadopen" command button I want the following code to run on my worksheets............................

View 2 Replies View Related

Code To Find Duplicates In Worksheets And Loop

Apr 27, 2007

I am trying to write a macro that will find duplicate listings in two worksheets by row. I don't really want to do a user form unless I have to.

View 9 Replies View Related

Code To Copy And Save Worksheets As Separate Files

Sep 17, 2013

I am looking for the VBA code to copy worksheets (with formatting) and save to a specific folder. The steps I am need to follow are:

Create a copy of the first worksheet
Save it to a specified folder and name it with the worksheet name
Repeat with all worksheets until the end of the workbook

View 3 Replies View Related

Macro- Filters Data And Copies To Worksheets Specified In Code

Jan 30, 2009

the following code filters data and copies to worksheets specified in code, can this code be shortened to make the macro run faster. Also in my second criteria how can I put "Contains "PT2" in the criteria as nothing happens when i run the macro, is *PT2?


With shtRec
.AutoFilterMode = False
.Range("A7").AutoFilter Field:=8, Criteria1:="LEHMAN", Operator:=xlAnd
With .AutoFilter.Range
On Error Resume Next
Set rng = .Resize(.Rows.Count - 1).Offset(1).SpecialCells(12)
On Error GoTo 0
End With
If rng Is Nothing Then
MsgBox "No data to copy"
Else
rng.Copy shtLehman.Range("A6")
End If
.AutoFilterMode = False
End With

View 9 Replies View Related

VBA - Code- Send Worksheets Named In Col B To Email Addresses In Col A

Jan 4, 2010

I have a code that i got from here which will send the Workbooks named in col A to email addresses in col B, as below..

but i need it to send the named Worksheets from the currently active Workbook.

The name of the Worksheet will be contained in col A.

Sub SendWkbs()
Application.ScreenUpdating = False
Dim wks As Worksheet
Dim iRowA As Integer, iRowB As Integer
Set wks = ActiveSheet
iRowA = 2
On Error GoTo EH
Do Until IsEmpty(wks.Cells(iRowA, 1))
iRowB = 2

View 9 Replies View Related

Macro Code Executes Very Slowly: Filling Some Cells On Different Worksheets

Nov 9, 2006

I have the following code attached to a userform. It works perfectly (for what I need) although I know it may not be perfectly written. However it does seem to take for ever to execute considering it's just filling some cells on different worksheets. Have I missed something out or got it tied up doing something that takes a long time. If it's just a fact of life that it takes this time then I can live with it.

Private Sub UserForm_Activate()
Dim wsCB As Worksheet, wsLL As Worksheet, wsBond As Worksheet
Dim rngFound As Range
Set wsCB = Sheets("Current Bonds")
Set wsLL = Sheets("Landlords")
Set wsBond = Sheets("Bond")
TenancyStartTxt = Format(TenancyStartTxt, "dd-mmm-yy")
RnwlDteTx = Format(RnwlDteTx, "dd-mmm-yy")
Application. ScreenUpdating = False
10 MyBond = Application.InputBox("Bond number for renewal?", "Bond Number")
strFindMe = MyBond
With wsCB
Set rngData = .Range("c13:c490").......................

View 5 Replies View Related

Deleting Multiple Worksheets Which Ends With The Word Data Through Vba Code

Jun 14, 2007

I have many worksheets which ends with the word " data". Is there a way to delete all the data sheets at one go through VBA code.

View 3 Replies View Related

Select Worksheets Dialog Box - How To Change Code From Print To Send To Email

Sep 29, 2012

I am making custom time sheets to suit our agriculture business - these excel sheets get sent out to the different farm managers who send back in staff times.

This code below is brilliant and works perfect for our needs. However I need to be able to emailPDF the sheets not print.How / where do I change code so the selected sheets go to Save & Send via email as a PDF instead of going straight to the default printer .( hard copy )

VB:
Option Explicit
Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet

[Code] .....

View 5 Replies View Related

VBA Code To Get Formula Into Each Worksheet Then Loop To Extract All Worksheets Into Master Workbook

Aug 26, 2012

I wanted to know if there is any way possible to get vba to insert a formula in a cell to each worksheet in each workbook in a folder and then using loop to extract all the info from each worksheet of each workbook in same folder into a master workbook?

View 1 Replies View Related

Autofill In Macro Range Is Constant How Can I Code To Be A Variable Range?

Feb 19, 2010

I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.

The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?

View 6 Replies View Related

Subscript Out Of Range :: Both Worksheets

Apr 27, 2009

I have this code below that keeps giving me a subscript out of range error and I don't know why I got it to run once but can't figure out the problem now...

View 4 Replies View Related

VBA - Select Same Range On All Worksheets

Jul 18, 2009

how to select the same range of cells on all worksheets via VBA?

I tried google several times but could not find anything that helps. I need to select a range of cells on all worksheets and then clear their content. However, google only tells me to select all sheets manually, which is not good since new ones are added every now and then.

Is there maybe a possiblity to select a range on all un-selected sheets?

View 10 Replies View Related

Global Range Name For All Worksheets

Jan 22, 2007

I have a holiday chart which has a summary at the end which counts holidays and sickness etc. It is in a workbook with a worksheet for each month, how can I define a range name that i can use with all worksheets, at the moment I am having to define new range names for all of the sheets and this is taking some time?

When I am in the new worksheet I can click on the range names in the top left but when i select one it takes me to the worksheet where the range is.

View 9 Replies View Related

Copy Range From Different Worksheets

Jul 27, 2007

I have a workbook with 58 worksheets in it... each worksheets is name T01, T02....T58, I need to copy a range from different worksheets...

I don't need all the worksheets in Master.xls I only need some of the worksheets...

for example .....

View 9 Replies View Related

Clear Range From All Worksheets

Jul 19, 2007

I have a macro to clear all contents for a sheet in a workbook. The problem is that every file has over 20 sheets of data. Is there a way I can code a for statement to clear all sheets in this one workbook? Is it possible to make a for statement for multiple workbooks with several sheets?


Range("A11:Z11").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.Interior.ColorIndex = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.ClearComments

View 9 Replies View Related

Zoom Worksheets To Fit Used Range

Aug 28, 2007

to apply a zoom to Fit Selection on all worksheets upon file opening? I've read many posts on this and understand how to apply this via worksheet activation, but haven't discovered how to make this to happen to all worksheets when the file is opened.

Zooming the selection needs to happen in the workbook module due to additional sheets being added at random and because it will facilitate the flow of the meeting so that we don't have to wait for the user to select and zoom.

Private Sub Workbook_Open()

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets

LCol = Cells. Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
Range(Cells(1, 1), Cells(1, LCol)).EntireColumn.Select

' Need code help here to apply to all worksheets
ActiveWindow.Zoom = True ' this works for the worksheet activation event only

Next ws

End Sub

View 5 Replies View Related

ADVERTISEMENT