can I reference worksheets by number rather than name in a macro?
I'm trying to make a macro that will go through and rename all worksheets according to a date they calculate. I want them to be able to re-name themselves again if the date is changed. This makes it not possible to use "2-12-07" as a callout because this name could change. So, what I'd like to do is select worksheet(4) regardless of the current name.
I'm not sure whether this should be a general question or it should go in the programming section... but here here goes anyways
I need to create a spreadsheet where the contents of Col A & B in Sheet1 are copied into both Col A & B in Sheet2 & Sheet3. Sheet1 Col A&B will be continuously updated (rows will be added) and as these columns are updated, Col A & B in Sheet2 & Sheet3 should be updated automatically and keep the format that is set in Col A & B in Sheet1.
Now, Col C&D in Sheet2 & 3 will be updated by a different user. As the user updates the cells in Col C&D in Sheet2 & 3, Col C&D in Sheet1 should automatically be updated with the data in the cells of Col C&D in Sheet2 (and keep the format) and Col E&F in Sheet1 should automatically be updated with the data in the cells of Col C&D in Sheet3 (and keep the format).
Instead of just counting all worksheets I want to count the number of worksheets between 2 control worksheets (Start and End). Reason for this is that I have a Workbook that grows weekly and each new worksheet is inserted after "Start". I have a macro that lists the Worksheet names but it grabs all and I only want those between Start and End.
View 9 Replies View RelatedI have 20+ tabs that need to be copied monthly. That also means 40+ name changes (when you copy you get "copy of...". I am trying to get this down so I am hoping for the better. Anyways what the macro needs to do is copy the first x amount (lets say 3 for this demo) tabs (true full copy, formulas and all)
(YYYYMM Name) -- This is the setup, it is not current month, I do not mind inputting the YYYYMM if need be)
201010 Tab 1
201010 Tab 2
201010 Tab 3
To
201011 Tab 1
201011 Tab 2
201011 Tab 3
Then a popup at asks current reporting date (again this is not current date) for these new tabs only (all these tabs have a date field that needs to be updated) So I assume this will have to store an array of the tabs created. Date WILL go in the same row/column in all tabs.
Then copy paste values all prior months tabs to preserve the history in the old tabs
201010 Tab 1
201010 Tab 2
201010 Tab 3
Now 20+ tabs doing this for you can see how im about to lose my mind. I am stressing them to downsize this mess, so the X number would need to be a value i could mess with in code.
If it wants to just read for the tabs that start with the asked for or current YYYYMM that's fine with me. That way I don't have to input a number.
The colours are just to mark the ranges
As every month is different the number of sheets adding up to the Red sheets(week total)
will change and the same with the Blue sheet(month end total)
Is there a code I can run for this summing up to be done?
Yellow is where data is entered
Red is where the Yellows range sheets need to add up before it
The Blue is where all the Red range sheets need to add up
I have no idea about VB code and writing macros so I was hoping to get some help on this issue.
Is it possible to unhide a specific number of worksheets based on an input value, i.e. if someone inputs a value of '3' in the specific cell, then 3 worksheets with a certain name is unhidden?
I have a macro set up to copy and paste data from worksheets into worksheet 1. But the number of worksheets often changes. Is there a way to write this to include all worksheets even if the number changes?
Windows("0285 WORKING FILE 0406.XLS").Activate
Sheets(3).Select
Application.Goto Reference:="R500C33"
Range("A9:AG500").Select
Range("AG500").Activate
Selection.Copy
Sheets(1).Select
Application.Goto Reference:="R501C1"
ActiveSheet.Paste
Sheets(2).Select
Application.Goto Reference:="R500C33"
Range("A9:AG500").Select
Range("AG500").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets(1).Select
Application.Goto Reference:="R1001C1"
ActiveSheet.Paste
Sometimes I will receive this file and there will be additional tabs. I just never know how many.
I copy down 500 rows knowing there will never be more data than that. And for each worksheet I paste to sheet 1 I add 500 rows to not paste over other data.
I would like the computer to read it as "select last indexed worksheet, copy paste to index 1, repeat while selecting the left adjacent worksheet until you reach index 1.
For each new worksheet selected add 500 rows to the last pasted amount."
How can I count the number of selected worksheets in VBA? I've been looking in the Excel object model, but with no avail. Perhaps I'm overlooking something simple.
View 2 Replies View RelatedI've been trying to bend my mind on how to accomplish this and haven't found a clear way to do this. I have to combine reports from a varying amount of one worksheet workbooks that are all formatted the same and have the same data format in the same cells. I was originally thinking about creating some quick code like
'for when I need to sum
activecell.formula = Workbook1.range(“b2”) + workbook2.range(“b2”) + workbook3.range(“b2”)
'and for when i need to create averages
activecell.formula = "=average(Workbook1.range(“b2”), workbook2.range(“b2”), workbook3.range(“b2”)
However, the amount of workbooks will change depending on which regional account I'm working on, so I have no 'set amounts' of sheets. it can be anywhere from 3 to 300 (figuratively speaking). Is there a way for me to create something that will be flexible enough to expand the amount of workbooks I need to consolidate into 1 final report that totals them together? or am I going about this totally wrong?
I have a function that counts my worksheets and I reference that to a cell so another macro that creates a new worksheet will be named +1 numerically. My problem is I need it to anchor on "MISLC" and count to the end of the workbook. Or start on the last sheet and count back to "MISLC"
VB:
Function Sheets()
Sheets = Worksheets("MISLC").Index - 1
End Function
I have a workbook has many worksheets, I would like to be able to delete worksheets if, for example, the number of rows in the worksheet is less than 100 rows.
View 3 Replies View RelatedI am trying to find a way to combine two worksheets with identical columns and an unknown number of rows. Both sheets use columns A through K.
I want to add a new sheet called "Combined orders", then copy data and headers from Sheet1, paste it to Combined orders, copy data only from Sheet2, and paste it on the row after the last row of data from Sheet1.
Sounds easy but I am easily confused by the unknown number of rows in each data set. Can someone please help?
Also, are there a few lines of code that I can copy and paste into new macros that will make the unknown rows problem easier for me in the future? (i.e., instead of just showing me the code, can you also please explain the concept behind it so I can learn for future reference?)
I found the code below on the Microsoft website and it works except it didn't treat the worksheet tabs as numbers so the sort is 1, 10, 100, 101 etc.
How can I get it to treat the worksheet values like numbers and sort accordingly?.
Code:
Sub Sort_Active_Book()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which direction they wish to
' sort the worksheets.
'
[Code]....
' If the answer is No, then sort in descending order.
'
ElseIf iAnswer = vbNo Then
If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
End If
Next j
Next i
End Sub
the maximum number of tabs/worksheets 1 workbook hold? I am using Excel 2003.
View 9 Replies View RelatedI have multiple worksheets (too many, actually!) and each worksheet is a record. I need to enter a Record Number into B5 of each sheet. The Record Number doesn't start at 1. I'd like to enter a number into the first sheet and the rest of the sheets to increase by 1. However, I only need to do this to some sheets.
View 9 Replies View RelatedI would like to write a macro that links cells in one workbook (working) to cells in another workbook (summary).
I don't want this to be a straight swap thought, I'd like it to contain the following formula:
=IF(Working!G8="a","a","")
The cells in the working workbook are listed vertically, while the cells in the summary workbook are listed horizontally.
ie. link G8-G18 in working to B3-K3 in summary
link H8-H18 in working to B4-K4 in summary
...etc
I am attempting to copy some numbers from one spreadsheet to another including the formats. The format I am using is a user-defined one which doesn't normally appear in the list of personalised formats.
My code seems to work fine within the same spreadsheet but fails when I do it using 2 spreadsheets.
i have 5040 rows in a sheet. in columns A,B,C,D,E,F, i have some input. and based on that i have some formula on column G.
lets say, min value is at row 4094 by looking at G column... i want to call entire row to the, lets say K L M N O P ....i need a formula for that....
random numbers are included in the formula. So ever time i make any changes on the sheet, min value changes even thought i dont play with the formula and input... so i want my new min value shown at row 1 of K L M N O P, every time i do that
how can i do that?
Need a excel macro which can compare 2 xls files and highlight cells which are different in the 2 files its like a slightly advanced one >a prompt which asks file 1 after selecting file a prompt whichasks for file 2 ..then on running it should highlight cells in file two which are different from file one Both file have same layout ie if file one has n columns in n worksheets file 2 will also have same . So the comparison should be done for each worksheet in both file and cells should be highlighted ..
View 1 Replies View Relatedsimplifying a formula which gathers data from about 50 worksheets from within the same work book.
The data to be gathered is in the same cell on each worksheet and is simply a number but i want the SUM of theses numbers carried forward to another worksheet. Each worksheet is named by date i.e. sheet 1 is named "16 June 2014" and sheet 2 is named "23 June 2014" and so on until "30 March 2015" (Each sheet represents one full week Monday - Sunday).
I am creating a set of lists, each on a different sheet. I want to have the first sheet as a sort of "Title Page" with basic information from the others. Is it possible to create a link of some types so that I could click on "Magazines" for example and go to the sheet called Magazines, or "Books" and go to the sheet called books. The latter would be very handy as this workbook has around 30 sheets in it, which is a pain to look through to find the right one.
View 2 Replies View RelatedI have created an XLA using a blank workbook which has a macro (called CompareMan) I have written within it.
I have written a utility (called INSTALLMAN) to install this add in.
When I run the install it creates and activates the add in OK.
The install also creates a toolbar with a button to trigger the macro.
Problem is, when I click the newly created button, no matter which workbook I am currently in I get a message ...
"The macro INSTALLMAN.xls!CompareMan cannot be found".
So my question is, how do I get my button to trigger the macro in my add in?
I have to do the same procedure for a number of profit centers. The only differences are the names of these profit centers. Therefore I want to use the same code, with changing references. How can I use this array so that it calls/uses 1 profit center at the time? If I use the following code they all are used at once (P1100P1200P1300P1500P1600P1900)
View 2 Replies View RelatedIs there a way to call a sub with out actually saying its name? In other words can I refer to a sub by a value or a cell that holds its name? for example, in my CNC programs I often use something like this;
View 14 Replies View RelatedOkay, I know you can reference worksheet functions with code like:
application.search()
But how do you use a UDF function within VBA???
I tried, thisworkbook.UDF() (UDF being the name of any UDF) but it didn't work.
I have a add in for excel (self made) and I recently hit a wall when trying to add functionality to it.
I have a form "UserForm1" created within the .Xlam file of the add in.
I want this form to be useable for any excel file that might be open, so putting code in the excel file that needs to use the form is undesirable.
The functions carried out by my add in are activated trough ribbon buttons.
my goal is to have a button on the ribbon that loads the form.
As the UserForm1.show code doesn't work, I will need a alternative.
I did find the code for making the sheet show if there would be a button in the workbook, however I want all code to be in the add in.
I am using the following code to find the text "Employment" in any row in colum H. If found it copies it to another worksheet.
Sub CopyRowsSht8()Sheet8.SelectSet Rng = Sheet8.Range(Sheet8.Range("H1"), Sheet8.Range("H" & Rows.Count).End(xlUp))For Each cell In RngIf cell.Value = "Employment" Thennum = cell.RowSheet8.Range("A" & num & ":" & "L" & num).Copy Destination:=Worksheets("Employment").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)End IfNextCall CopyRowsSht9End Sub
1. This sub calls CopyRowsSht8 and then this calls CopyRowsSht9 and so on. I have 15 sheets so how would I code this into some kind of loop to include sheets 8 to 22 instead of calling sub then sub then sub etc.
2. It takes a while to do just two sheets so 15 would be very slow - can this be made faster. The maximun number of rows containing data is only about 100 on each sheet -could that make a difference?
Im trying to call another subroutine within the same workbook but it doesnt work,
---------------------
Sub Macro1()
Dim mac1 As Variant
Dim mysub As Variant
Select Case Cells(1, 3)
Case mac1
Call mysub
End Select
End Sub
---------------------
The below thing has been driving me up a wall for 2 days now. I know the fix has to do with calling the variables, but I can't figure out the exact code.
Sub Parsing_Logic()
Dim IBG_URL As String
Dim A As String
A = Mid(IBG_URL, Application.WorksheetFunction.Find("/200", IBGURL), 8)
Dim B As String
B = Left(IBG_URL, Application.WorksheetFunction.Find("/200", IBGURL))
Dim C As String
C = Right(IBG_URL, Len(IBG_URL) - (Len(A) + Len(B)))
Dim D As String
D = Left(C, Application.WorksheetFunction.Find(".200", C))
End Sub
Function IBGLink(IBG_URL As String, Formatted_Date As String)
If Application.WorksheetFunction.IsErr(A) Then
IBGLink = (D & Formatted_Date)
Else: IBGLink = (B & Formatted_Date & D & Formatted_Date)
End If
I am trying to call another macro, but instead of using the name of the macro, I have it in a variable. I am getting an error that says "Compile Error: Expected sub, Function, or Property." Can you call a macro that is in a variable? I basically have a bunch of macros created. I also have a list of the name of those macros on a worksheet in Excel. I want excel to start at the top of the list in excel and run the macro, then move down and run the next macro, and so on. Below is my code.
Sub Commercial_2005()
Dim Macroname As String
Workbooks("bleeg.xls").Activate
Worksheets("CommercialList").Select
Cells.Range("a1").Select
While ActiveCell.Value <> ""
Macroname = ActiveCell.Value
Workbooks("copy of recast_Report_v2.xls").Activate
Call Macroname
Workbooks("bleeg.xls").Activate
Worksheets("CommercialList").Select
ActiveCell.Offset(1, 0).Select
Wend
End Sub