VBA - Selecting Sheets In An Array
Aug 29, 2007
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?
View 9 Replies
ADVERTISEMENT
Jun 15, 2012
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.
View 3 Replies
View Related
Apr 3, 2009
I've successfully copied the array of equations using the VBA that Pjoaquin enlightened me with from my last thread. The outcome was Sheet2!A2:O2 being successfully populated with the equations from my first sheet... but here comes the problem: I'm looking to autofill A2:O2 down to the last record in Column P. But the number of records in this table is varable.
View 2 Replies
View Related
Mar 25, 2012
I want to find the row position in an array.
1 200 CAR 001
2 230 CAR 002
3 400 TRUCK 001
4 200 BUS 001
5 250 CAR 003
6 250 BUS 002
7 200 BUS 003
8 730 CAR 004
in 4th column it should say that this is "n"th position of 3rd column value.
ex.
in 4th row 3rd column 1st "BUS" appears. hence column 4 is 1
in 6th row 3rd column 2nd "BUS" appears. hence column 4 is 2
in 7th row 3rd column 3rd "BUS" appears. hence column 4 is 3
tell me how to automate this
View 1 Replies
View Related
Mar 21, 2008
I recorded the following macro to select all the worksheets in the Activeworkbook by clicking on the first worksheet and then hitting Shift Tab and selecting the final worksheet (thus '[Group] selecting' all worksheets in the active workbook).
Sub Macro1()
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
Range("A1:B2").Select
End Sub
How do you generalise the Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select if the number and names of worksheets varies from workbook to workbook?
View 9 Replies
View Related
Jul 21, 2014
I want to fill an array from values in range A1:H10. I want to fill the array with all rows in range and only columns B,C and E. I have the code below so far using index function.
Is there a more direct way to select all rows from desired range to avoid the need to create an array of rows from 1 to LastRow and then use Application.Transpose(RowsArr) (in red) inside Index()?
View 3 Replies
View Related
Apr 30, 2008
I have a workbook that I am continually adding and removing pages from. The pages are used together in groups of 3 and as a result are named 1.1,1.2,1.3; 2.1,2.2,2.3 and so on. I have a macro that works on sheet 1.3 and now need it to repeat on sheets 2.3, 3.3 etc depending on how many sheets I have open and active in the workbook. Is there anyway that I can create a macro that will work on all sheets that have titles ending in 3 and then end if there are no more? Thus it would work if my workbook goes to either 2.3 or to 7.3 and so on.
View 14 Replies
View Related
Sep 2, 2009
I have the following code which works fine when all of the sheets are not hidden:
View 3 Replies
View Related
Apr 22, 2008
If I opened a new workbook so i had sheet1 sheet2 sheet3... Starting at sheet1, how could i move on to sheet2 using VBA without actually naming it....
Is there a selectsheet.next or something?
View 9 Replies
View Related
Jun 25, 2008
I would like to use the below macro with other sheets in my workbook, how do i change the line "With Activesheet" to incorporate this? Lets say my other sheets are titled as: main, report, sheet3, sheet 4?
Sub Blanks()
Dim lastrow As Long, i As Long
Application.ScreenUpdating = False
Application.Calculation = xlManual
With ActiveSheet
lastrow = .Cells(Rows.Count, 3).End(xlUp).Row
For i = lastrow To 1 Step -1
If .Range("A" & i).Value = "" Then .Rows(i).Delete
Next i
End With
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub
View 9 Replies
View Related
Feb 10, 2014
I'm after a bit of programming which selects the same cell on the next sheet that I have already selected.
So, If I'm on Sheet1 and I have G5 selected, when I change Sheets to Sheet3 for example, I want it to select G5 on Sheet3.
View 9 Replies
View Related
Aug 25, 2008
Use a drop down menu (Combo box for example) to look at all the sheet names (these will be names of people, sheets added all the time) be able to select a name which when selected takes me to that sheet. So the amount of sheets will increase so everything will need to be dynamic.
I dont want to see all the list behind the drop down so that why i was thinking of a combo box.
View 8 Replies
View Related
Nov 12, 2009
I have created a macro that goes from my main sheet to the data sheet and changes all the pivot tables to match. One thing I hate about it is that it is not very fluid. It jumps from the first page to the second (due to the sheets("data") function. Is there a way to make it change the pivot tables on the second sheet without actually have to have the macro select the second sheet?
View 4 Replies
View Related
Dec 26, 2013
I have spreadsheet with 13 sheets the first of which is a title page, what I wish to do is hide the other twelve sheets and somehow be able to select them from a drop down box (or similar what ever works with a macro)on the front page, the selected sheets would then appear unhidden.
I am OK with formulas etc but macros are beyond my ability even trying to follow examples & tutorials online has me lost.
The sheets all have specific but different names and none need to be selected more than once (although if possible it would be good). I understand where to put the macro in but as to how it is written ......
View 7 Replies
View Related
Sep 13, 2013
I have x number of sheets in a workbook, and I am looking to copy sheets a, b and c into a new workbook that I will create.
The a, b and c are in no order, or consecutive (although they could be). I want to be able to count the number of sheets in the workbook, and traverse through that to find sheets that I need, and select them to copy over to a new workbook.
View 1 Replies
View Related
Jun 30, 2006
I have 30 and above sheets in a work book and like that I am having 5 such
books. The sheets are named as 201, 202, 203 ....etc as per the contents in
that particular sheets. (201, 202 .....are the P.O nos.). all the work
sheets are of having similar format of datas.
Now what I need is if I want to look the details of one single sheet (say
324) I have go all the sheets one by one and it is hard to find out.
If any body give me a solution so that if I type a particular no. (forms
part of the name of the sheet) that sheet should appear for me.
View 12 Replies
View Related
Feb 19, 2014
I have some reports that I run that go out to analyst daily and I use this script to merge all the documents together. They are the same everytime. However it includes a series of blank rows because the vba I use to create them I believe causes this. Is there a command to remove the VBA when merging them together. Here is the selection copy piece.
HTML Code:
'Import a sheet from found files
Do While Len(fName) > 0
If fName ThisWorkbook.Name Then
[Code].....
View 3 Replies
View Related
Aug 31, 2006
I have a print userform where I let the user print a summary package of sheets. I am trying to allow the user to see a print preview of certain sheets. The first one is always the "Bid Summary" sheet (sheet 2). The rest are a dynamic number of sheets with numeric names starting at 1. Now I need a easy way for me to allow the user to see a print preview of the Bid Summary, and then all the sheets right afterwards. I cant use sheets("1","2",...).select because it wont select a ever changing number of sheets. Really it is a problem of selecting a varied number of sheets in VB.
View 3 Replies
View Related
Jun 2, 2008
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 Related
Apr 26, 2006
How do I modify this macro so that the worksheet array will select all the worksheets except sheet 1?? My workbooks will have varying numbers of worksheets ...
View 3 Replies
View Related
Jan 20, 2010
I'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) '
View 9 Replies
View Related
Jul 23, 2014
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
View 2 Replies
View Related
Sep 12, 2012
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.
View 5 Replies
View Related
May 20, 2008
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?
View 9 Replies
View Related
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
Jul 30, 2014
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.
View 2 Replies
View Related
Nov 11, 2011
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]...
View 2 Replies
View Related
Dec 24, 2013
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.
View 3 Replies
View Related
Apr 25, 2007
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
View 3 Replies
View Related
Mar 10, 2014
Using VBA, I need to Select A1:C14.
The problem is that A1:C14 contains blank cells, and there is also an adjacent column D that I do not want to copy.
So, UsedRegion and CurrentRegion aren't doing it for me. (It selects Column D too.)
Obviously, this is an example...the real data set is an export and varies in size.
View 1 Replies
View Related
