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.
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 ......
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.
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
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
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.
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?
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.
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.
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.
I am currently working on a simple macro to help with formatting a spreadsheet. The number of tabs on the spreadsheet will change to reflect the number of companies I'm running reports for. But the same process is to be applied to each tab. The issue I'm facing is I don't know the appropriate code to select all tabs without specifically referencing them.
The code that I would like to apply to all tabs is as follows: ActiveSheet.PageSetup.PrintArea = "$A$1:$J$85" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = ""
This should be so simple (I would have thought), but so far I'm struggling.
Using Range / Offset or something similar, I want my macro to select a certain cell (which is dependent on the data in another cell) on my worksheet so that the user can then input data into the chosen cell.
Something along the lines of:
Range("A1").Offset(23 + t4, 1).Select
[where cell t4 contains a number which dictates how many lines down I want it to jump]
The purpose of the excel sheet is hour administration. In the first page you fill in which group it is about, what week it is about and the amount of hours to change.
There are several other sheets named after the group that are filled with some kind of agenda.
An example: I want to make a change to the amount of hours group X will be there in week 3. I fill out the 3 columns (Group, Week, Hours) and hit a button. It should now check those 3 columns for data and modify the hours for the correct group. It is not done yet but I'm running into a problem already, the function I use to find the row with the correct weeknumber in the Group sheet returns wrong numbers and I don't know why.
VB: Private Sub CommandButton2_Click()
Dim Week As Integer Dim WeekRow As Long Dim Groep As String Dim Uur As Integer
[Code] ....
What I am expecting to happen is this: It checks A1, B1 and C1, selects the correct sheet, executes the function FindRowByValue (the week number is in column B) and fills Week Row with the result, then returns to the original sheet, gives a debug message and repeats this 5 times.
The debug message shows that the Week Row is not returning the row with the week number I filled in but instead returns either the week number itself (so if I wanted to find week 5 it returns a 5, while week 5 is in row 28), or a different number that is incorrect.
I have a comparison macro that compares one sheet with the next, it is called sub comp1 (). There is a condition whereby columns must be removed before sub comp1 can run. To remove these columns I run the macro called sub colcut (). What I need is the following action:
If cell BJ4 of current worksheet = "RE COMMENT" then run sub colcut () followed by sub comp1 () otherwise only run sub comp1 (). FYI The macro is to be run manually via a button rather than triggered by the value of the cell.
I run a macro on a spreadheet in which the rows increase daily. In the macro I simply recorded an end.down to select a column of data which ends up being fixed row numbers in the code so the next time I run the macro it only selects the range I selected last time.
I'm trying to make a macro'd button that when clicked will select the cells A1:A?? where ?? is equal to the value in cell B1
B1 is a variable number that changes to be the proper amount of rows that I require selected.
It's always A1 down to A9 minimum and A1 down to A400 max.
Once selected I need the macro to copy the selected cells to the clipboard for another application to be able to paste that info.
So far I've been able to get the copy command to work and the range command to work but can't seem to figure out how to put the variable number from B1 into the range command.
I've made a macro and spend some time making it work across different sizes of data. The last issue I'm having is after the macro sorted out some data, that it will then delete, it has to select the first row and shift select down before deleting it. However, depending on how the data is sorted, the first row could be 9, could be 10 and so on. See below for my code.
Code: Sub Macro1() Windows("statistik.xls").Activate Range("A8").Select
[Code]....
If the first row in my data set is not one of the 3 criteria ("Personligt ejede virksomheder", "Privat", "Reklamebeskyttet"), then the first row # will be 10 instead of 9. If the first two rows are not one of the 3 criteria, then it would be row 11 and so forth. How do I make my macro take this into consideration?
With wsSheet.Range("A:A") ReDim MyArray(1 To .Rows.Count, 1 To .Columns.Count) MyArray = wsSheet.Range("A:A") For i= 1 To .Rows.Count For j= 1 To .Columns.Count wsSheet.Cells(i, j+ .Offset(0, 3).Column) = MyArray(i, j) Next: Next
I want to select alla values in column A, but when I specify the range as "A:A" the code results in an infinite loop! How can I come around this?
I created two radio buttons on a spreadsheet where a user can select one button for Yes and the other for No. When I right click these buttons, I notice the names (shown on the left hand side of the formula bar) are:
I'd like to create a macro that automatically selects the Yes or No button depending on a cell value in another tab. If the cell value is 1, the macro should select Yes. If the value is 0 the macro should select No.
I tried running the macro below:
Sub testSelectYesOrNo() If Not IsEmpty(Sheets("Sheet2").Range("A1").Value) Then Select Case Sheets("Sheet2").Range("A1").Value Case Is = 1 Sheets("Sheet1").OptionButton4 = True Case Is = 0 Sheets("Sheet1").OptionButton3 = True End Select End If End Sub
But when this runs I get a "Run-time error 438 Object doesn't support this property or method"
When I click debug, the text "Sheets("Sheet1").OptionButton4 = True" was highlighted.
I have created the below macro to show the user (when he / she clicks on a button) how many days are left till the deadline day (taken from MS Project).
Sub DaysLeft() Dim rng As Range Dim count As Integer Set rng = Sheets("Programme Reporting").[E2:E200] count = Application.Evaluate("NETWORKDAYS(Today(),E3)") Range("L3:L200").Select ActiveCell.Value = count End Sub
The range I want to output the days to is L3:L200 that seems to be okay. I have an issue with trying and putting a range in the NETWORKDAYS area, if I try and put in E3:E200 (the range of dates) it fails, as does $E$3:$E$200. At the moment the above code does work but it only puts in the days for one of the entries (E3).
I am trying to select rows in excel using a macro and then hide the rows, but i want to be able to add rows within this selection and still be able to hide the complete selection of rows.
The macro i am currently using is shown below: This hides rows 131 - 205, but if i was to add in another row in between so the range i now want to hide is 131-206 can i get the macro to update to this new range?
I'm using a macro that picks up on changes in a active cell.. This is the code
HTML Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then If Target.Value <> "" Then
ThisWorkbook.Sheets("Log").Cells(Target.Row, 2).Formula = "=Today()" End If End If End Sub
There's a problem with this code, when select from a range and hit delete, i get a error mismatch. I want to have the macro ignore when i select active cell range of greater then 1.
1. I need to paste data that is copied from an internet window into sheet1 without selecting the cell so that I can continue working on a data in sheet5. The macro prevents me from editing sheet5 in an orderly fashion because when it goes to paste the data into sheet1 it selects the cell in sheet1 interupting what I am trying to manually do with the data in sheet5. How to paste data into a cell withou selecting the cell so that my program stops interupting what I am doing in a different sheet?
2. My macro is meant for real-time defect monitoring in a production facility. The macro opens an IE window that contains the defect occurences, copies the data, pastes it in sheet 1 then sheets2 and 3 analyze the data, closes the IE window, and then waits for a time period before repeating the process. This macro is so slow though that it is hard to work on this file while the macro is running. How to run the macro in the background and speed it up so that I can continue working on Sheet5 of the file while the macro repeats itself over and over again? Here is my code.
The variable website is determined by earlier parts of the code that direct the macro to a specific network website. Sub Half_Hour_Data
1 'Open Internet Window and Navigate to Website Set myIE = CreateObject("InternetExplorer.Application") myIE.Navigate Website myIE.Visible = True Application.Wait Now + TimeSerial(0, 0, 10) needed to wait until the page loads
[code]....
Is there a way to speed up the macro by removing the timeserials and just telling it to continue when the task is complete. Then run the whole thing in the background so I can work on another sheet while this program runs?
