I'm trying to summarize some data across 12 sheets
I'm trying to look up values against dates and specific items, however they repeat across different sheets.
The attachment has more detail : Lookup x across multiple sheets bring back value.xlsx
I am currently using the array formula to find out the most recent date a business as referred a deal. I have been using the below array formula to figure this out...
{=MAX(IF((Opps!A:A=[@Company])*(Opps!B:B=[@[Full Name]]),Opps!G:G,0))}
However, we have now have two types of deals. Opps and Suspects. SO now i need to figure out the most recent date from Opps!G:G and Suspects!G:G... regardless of what the deal type is. Hope that makes sense.
Basically i need to have a formula along the same lines as above but MAX date from if conditions from Opps OR conditions from Suspects
I am trying to graph data from an Array of sheets. I keep getting many errors... I tried using Cases too but this failed, it graphed the first Sheet but nothing else.....
Before Array Works! (Uses Cases)
Code:
Sub GraphTest4()
Dim LastRowGraph1 As Long
Dim ws As Worksheet
For Each ws In Worksheets
Select Case ws.Name
[Code] .........
I have a list of cell entries that I want to find and replace with different text or a number.
My code below is 4 of them.
I am trying to do the replace over 3 different sheets at the same time but I am only changing the 1st sheet with my efforts.
Sub Find_And_Replace()
Sheets( Array("Resolution", "Response", "Open")).Select
Sheets("Resolution").Activate
Cells.Replace What:="1 Widespread*", Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="2 Critical*", Replacement:="2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="3 Non*", Replacement:="3", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(Array("Resolution", "Response", "Open")).Select
Cells.Replace What:="4 Require*", Replacement:="4", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
I am trying to use the SUM IF Array formula to sum a group of numbers that fall under a heading of reference numbers over several sheets of data. For example purposes lets say my spreadsheet looks something like the below.
A
B
C
D
E
1
2600000248391
2600000393805
[code]......
The first two digits of the heading numbers are the criteria I am trying to use to separate and sum the data. For example I need to sum the value of the data below headings that falls between 1400000000000 and 1499999999999.
For the example above I used the below formula for the current Sheet and it works fine.
{=SUM(IF(A1:E1>="1400000000000",IF(A1:E1="1400000000000",IF(Sheet1!A1:E1,Sheet2!A1:E1
I am trying to populate many arrays with the same code using something like this. For this test, assume the following data in A1.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Code:
Sub populate()
Dim firstArr(5), secondArr(5), thirdArr(5), fourthArr(5), fifthArr(5) As Integer
Dim r, c, num As Integer
[Code]....
The above code does not work of course and falls over. I am unsure whether I should try and concatenate with something like this eg "" & arrName(i) = Cells (r,c) or go down a different route.
how do i create a sheetsarray to include all sheets between First and Last? I plan to loop through each sheet in this array to copy data to a summary page, as per [url]
View 9 Replies View RelatedI'm trying to create an array of Sheets objects and use them. This gives me a type mismatch on the marked line below. I could just save the sheet names as strings in the array.
Dim SheetList() As Sheets, A As String
Set ORng = Sheets("Options").Range("ReachPagesHeader").Offset(1, 0)
PageCount = Range(ORng, ORng.Offset(1000, 0).End(xlUp)).Count
ReDim SheetList(1 To PageCount)
For X = 1 To PageCount
A = ORng.Offset(X - 1, 0).Text
Set SheetList(X) = Sheets(A) '
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc
This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode()
'
'Sheets("Summary"). Select
If Range("a7") = "complete" Then
'
Sheets(Array("1", "0")).Select
Sheets(ArrSh(1)).Activate
[Code] ......
I have been running a simulation for about 18 hours now and just received:
Run-time error '1004':
Method 'Add' of object ' Sheets' failed
I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:
ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)
The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...
Do array formulas work if the array is across sheets instead of across columns or rows? I'm getting a #Ref! error when I try to use an "across sheets" array.
I'm doing this:
=sum(if('Sheet 1:Sheet 2'!A1=1,'Sheet 1:Sheet 2'!A2,0))
With a CTRL + SHIFT + ENTER return.
I am trying to clean up a macro a little that used to look like the following:
Sub Refresh()
Sheets("Sheet2").Select
ActiveSheet.QueryTables(1).Refresh BackgroundQuery:=False
ActiveSheet.QueryTables(2).Refresh BackgroundQuery:=False
Sheets("Sheet3").Select
ActiveSheet.QueryTables(1).Refresh BackgroundQuery:=False
I want to set up an array and have the code run through a loop and have gotten stuck on the following:
Sub Refresh()
Dim i As Integer
Dim SheetName As Variant
It does not seem to want to go from the first sheet to the second. What am I doing wrong/what can I do better?
I know I can print sheets as such:
Sheets(Array(2,4,5,8,9)).printout
I want to populate an array list based on sheet names, but cannot figure out the last line for syntax:
Dim ws As Worksheet, arr() As String
ReDim arr(0 To Sheets.Count-1)
For Each ws In Worksheets
If InStr(1, ws.Name, "Crp-") Or InStr(1, ws.Name, "Reg-") Or InStr(1, ws.Name, "Grp-") Then
arr(counter) = ws.Index
counter = counter + 1
End If
Next ws
Sheets(arr()).printout
As I debug it the array is filled with the proper sheets, but I for some reason cannot figure out the syntax for this line? Does it have anything to do with me declaring the array as a string and using integers as the index?
I have a work sheet named "Main_List"...In column D starting with "D2" I would like to list worksheets that I would like to have printed via VBA.
The workbook has several hundred worksheets and I would like to list in column D only worksheets that I would like to print with VBA code.
I have a workbook with 26 sheets, labelled A to Z. Column A in all the sheets have names from rows A6:A35.
I need a macro or a code to extract all the names from each of the 26 sheets and paste it to a new sheet 'Names' under column A, such that names starting with 'B' paste under all the names 'A' and so forth till 'Z'.
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.
I wonder whether I'm using the code below to copy data from two 'Source' sheets to one 'Destination'.
[Code] .......
The code does copy and paste the correct inofrmation, but the problem I have is that the data from the latter sheet i.e. "IDEAS Actuals" overwrites the data from the "IDEAS Forecast" sheet.
How I would be able to change this so the data is copied underneath each other.
Sub Macro2()
Dim stgSheets As String, Cnt As Long
Dim arrSheets()
Cnt = Sheets.Count
ReDim arrSheets(Cnt - 1)
For i = 1 To Cnt
stgSheets = stgSheets & Sheets(i).Name
arrSheets(i - 1) = stgSheets
'sSheets = sSheets & "", ""
Next i
[Code]...
Code:
Sub TabColor()
Dim mySheets As Worksheets
Dim mySheet As Worksheet
Set mySheets = Worksheets(Array("sheet1", "sheet2"))
For Each mySheet In mySheets
With mySheet.Tab
.Color = 9
End With
Next
End sub
I'm getting a type mismatch at line 4. Can I not group the sheets like this? This is just a test program, my real one would have 10 or more tabs in the array.
I have a workbook with several sheets, some are permanent and then there are certain sheets whose quantity varies depending on how many sub-contractors we use on a given job. The workbook starts as a template and there are 10 sub-contractor sheets to start with, if we only have 5 subs then the last five sub sheets are deleted. The sheets start out with the tabs named "Sub 1" "Sub 2" etc. but after the book is set up the sheet tab names are changed to the sub name. I've included some partial code below, with the goal being to run code on the sub-contractor sheets no matter the tab name or how many there are.
So the workbook has 5 "non-sub" related sheets, I was thinking that if I could do a sheet count -5, and get that number into the array it would accomplish what I want to do. And if someone added a new sheet it would still be processed with the others.
Public Sub SubConEstNum()
On Error Resume Next
Dim S As Worksheet, names As Variant, i As Integer
names = Array(6, 7, 8, 9, 10, 11, 12, 13, 14, 15)
For i = LBound(names) To UBound(names)
Set S = Sheets(names(i))
S.Range("T3").Value = S.Range("S3").Value
S.Range("T1").Value = S.Range("S1").Value
S.Range("T2").Value = Sheets("Bill").Range("K1").Value
Next i
End Sub
I need to multiply an array of logical results ( returned as {1,0,0,0,1,0 et.}) with a text array (a reference column) and return the text in the reference column in case the value in the logical array is 1.
View 3 Replies View RelatedI'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 ?
I have created a macro that loops through and creates an array of the visible sheets. Now I would like for it to print each of those sheets out. I think my main mistake is in my declaration of the array type, since I have not worked with arrays much before.
how to make the following code operable. Currently when I run it I'm getting a "Run-time error (9): Subscript out of range" error.
I need a code to select sheets with pages less than 15 to print out. Also, a code to print out just the first 5 and last 5 pages if its more than 15 pages.
I've tried this so far but no success:
For X = 1 To Worksheets.Count
If Y Is Nothing Then
Set Y = Sheets(X)
Else
If ExecuteExcel4Macro("Get.Document(50)") < 10 Then
Set Y = Union(Y, Sheets(X))
End If
End If
Next X
Y.Select
I am currently using this code to place names in a combobox (active X). How can I use an array to perform each line to all 12 months without repeating the code over and over?Here is what I have:
Code:
Sub auto_Open()
Sheets("FEB").ComboBox1.List = Array("ALL", "ACT", "ROF", "MM")
Sheets("FEB").ComboBox2.List = Array("ACTvsROF_2", "ACTvsPLN_2", "ACTvsLY_2", "ROFvsMM_2", "ROFvsLM_2", [code].....
Here is my macro
MyFileName = Sheets("Macros").Range("B1").Value
MyFileNameTwo = Sheets("Macros").Range("B2").Value
Sheets("Blank").Select
Sheets.Add After:=Sheets("Blank")
[Code] .........
Issue with array that's bolded The way it is set up is to rename the 3rd sheet to MyFileName and rename the last sheet to MyFileNameTwo. The file names will remain constant. They will always be the 3rd and last sheets, but the number in between will vary. Is there anyway to select the 3rd sheet through the last sheet to delete these? When I use the array it wants sheet names but those are based on multiple variables in other workbooks.
Need a way to generate a list (i.e. array) of tabs available in the current spreadsheet?
View 3 Replies View RelatedHow can I make this macro easier (smaller)? The same action is performed on an array of sheets (not all sheets).
View 4 Replies View RelatedWorking with an existing macro from this forum. How do I create the array showing the number of sheets found? I know from a MsgBox that two sheets are found.
View 3 Replies View RelatedI have the below VBA code associated with a sheet called "Select View". When the workbook opens there is another code that displays only "Select View", all the other sheets are hidden. In "Select View" there is a data validation list. Based on the selection the user gets to see only certain sheets. For example, if the user selects "Region 1" they see only the sheets "Region 1", "Panama City" and "Pensacola".
As you can see some of the names in the "Target.Value" are the same as sheets in an array. The ones that do are lead schedules, that is information from other sheets in an array roll into it. That is values from "Pensacola" and "Panama City" flow into "Region 1", the lead sheet.
What I am trying to do is finish the code where based on the "Target.Value" selected it automatically takes the user to the lead sheet. For example, when the user selects "Region 1" in the data validation it takes the user directly to the sheet "Region 1". The user of course can also see the other sheets in the array, that is "Pensacola" and "Panama City" and can manually select the tab to view or go into one of the later
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet
If Not Application.Intersect(Range("B10"), Target) Is Nothing Then
For Each sh In Sheets(Array("Panama City", "Pensacola", "Region 1"))
sh.Visible = Target.Value = "Region 1"
Next sh
[Code]....