Repeating A Macro In A Macro
Jun 4, 2009
I have a spreadheet with a list of numbers in column A. (6000 or so)
I am looking for a macro that will pick up the first number in A1 (is a 6 digit number)
transfer this number into cell A1 in another worksheet, after this part of the macro i will add my macro that will save & print the second worksheet.
However i can't make the macro repeat for Cell A2, Cell A3....... Cell A6000. All of these will be going into cell A1 in the second worksheet.
View 9 Replies
ADVERTISEMENT
Jul 7, 2008
I have the below macro that I need to have it repeat until the end of the data, I'm not brilliant with how macro's actually work,
Range("A2:Q5").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
View 9 Replies
View Related
Jan 2, 2007
I have a macro which is shown below.
Range("E1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A3").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Range("I1").Select
Application.CutCopyMode = False
Selection.Copy
What im trying to do is to be able to run the same macro but start on the last cell that I had selected and work on down the sheet until the end. What im also trying to do is to be able to post into next avail line on sheet 1 so it dosent overwrite.
View 9 Replies
View Related
Nov 23, 2007
I have a macro that is working fine. It calculates some values based on a SQL database and then copy the values and paste them into another spreadsheet.
However, I need to repeat the same procedure 1296 times.
Please see below the macro.
Sheets("QUERIES").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "2"
Range("C5").Select
Selection.QueryTable.REFRESH BackgroundQuery:=False
Range("D5").Select
Selection.QueryTable.REFRESH BackgroundQuery:=False
Range("C5:G5").Select
Selection.Copy
Sheets("AWB RANGE").Select
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I need to increment the line #3 (ActiveCell.FormulaR1C1 = "2") that should start with 2 and end with 1296.
Also, I need to increment the line #11 (Range("K2").Select) that should start with K2 until K1296.
The other parts of the macro will not change.
Would you be able please to give me a hand on this explaining how to create a counter to start on 2 and stop with 1296 ?
View 9 Replies
View Related
Feb 5, 2010
I have figured out how to write a macro to make charts for me automatically, but it only runs the macro on the exact same range every time.
I have about 100 different students to make charts for and would really like to find a way to do it more automatically
Is there a way to write a Macro so that I can run the same steps on a different range (same number of rows and columns)?
Edit: having learned from my first post, I am adding more information.
the first chart is from the data in the range from B5-H7
the next chart would be from the data in the range from B11-H13
The charts are not evenly spaced from each other. I will need to select the range (same size) each time.
I can record the Macro, I just can't apply it where ever I want to.
View 14 Replies
View Related
Feb 21, 2014
This is for a template for teachers to analyze student testing data. On sheet4, wrong answers in each column are noted by a lack of a + in the corresponding cell. I want to paste the names of the students who missed each question into sheet 5.
I've done it by repeating a filter macro, but I manually copied the following separately for the 75 columns in the template.
Problem #1 - there must be a more efficient code, something that automatically loops to the next column
Problem #2 - the template has 75 columns, but many tests have fewer questions. I'm trying to find a way to stop the loop whenever it hits a blank cell in Row 10 on sheet 4. I've done it with an if/then in the last section on the above code, but where I'm at now, i would have to add that to the code section for each column. Which isn't that big a deal, but I figure there must be a better way.
The relevant portions of the workbook are attached here.
repeating macro until hits blank cell sample.xlsm
View 4 Replies
View Related
Feb 26, 2008
I have a sheet with a number of repeating blocks copied down.
I would like a macro that when I select the first block, say range "A1:D30", the macro will cut the blocks below and put them side by side, so that I can compare them across.
i.e cut "A31:D60" and put it in "F1:I30" and so on ...
View 9 Replies
View Related
Apr 20, 2006
sorting data I use in a workbook for athletics. I've really chopped down my workbook for upload, In the worksheet "Leaderboard" I can call up stats for different lifts, and it finds (in this case) the top 5 lifts and the names for the kids that have those corresponding lifts.
My problem is that when two or more kids have the same lift, it will only call up the name of the first instance of that lift. You can see this in the "Leaderboard" worksheet, and the name "Adams, Andrew" appears for both lifts of 75. I would appreciate any help on how to correct this, as I've searched the Internet for weeks now looking for a solution. I've tried experimenting with different things as well, all to no avail.
View 9 Replies
View Related
Oct 16, 2009
I am trying to make a save&close workbook macro.
I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).
The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.
If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.
The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)
The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.
View 10 Replies
View Related
Jul 10, 2012
I'm using a CALL Macro to split up a HUGE macro into different pieces:
Code:
Sub RSLDASHBOARDV2()
'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D.
'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D.
'Do Not Modify Code Unless Given Proper Privileges to do so.
Dim APPSPD As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
[code]...
The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:
Code:
objField2.PivotItems( _
"TRC").Position = 1
objField.PivotItems( _
"MEDCO MAIL OR AOB").Position = 2
When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.
Code:
Sub STATSPIVOT()
'STATS PAGE BASED ON STATS DATA TAB
Sheets("STATS DATA").Select
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("STATS DATA").Select
Range("A1").Select
[code]...
View 4 Replies
View Related
Jul 21, 2007
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
Windows("estimate sheet one.xls").Activate
View 2 Replies
View Related
Jan 15, 2014
I have a macro where I manually select a cell then the macro kicks in to copy and paste the contents into the Find function. From there it goes to another work sheet, clicks on a cell in column one and searches for matching cell contents. Then If false it manually goes back to the original worksheet/cell and then I input a N in the left adjacent cell. If true I enter a Y. Right now I can only run this for the specific cell the macro was recorded for. I would like to expand this.
What I would like to do create a loop in the macro that waits for my cell choice input then continues with the with the Find function. If false the macro should just loop back to the original worksheet/cell and wait for input, ie the next cell selected. However, if true a worksheet/cell is selected, a Y is input and it loops back to the original cell and waits for input.
View 9 Replies
View Related
Aug 13, 2014
This is the code I use to call a macro when the macro Im calling is in the same workbook.
[Code].....
However, I would like to call this same macro when using another workbook. I copied the macro "Clearformating" and pasted into a personal macro workbook module. However when I add this code to the sheet tab it will not run the macro.
I also tried this code.
[Code] .....
View 2 Replies
View Related
Jan 26, 2009
I have a macro that I found somewhere on the net to look within a folder and list all the files of a certain file extension.
The macro to do this is in the attached example and is called 'Get_File_Names_Within_Dir_ext'.
I have created a basic userform outline, 'UF1' for the user to define:
Select File Extension
Select Folder to Search
Destination Sheet
I just don't have any idea how to sync the two.
If you type 'exe' into 'TB1_File_Extension' of 'UF1' the macro should search for '*.exe' files within the specified folder.
The search folder 'RefEdit1' box should open a windows explorer box (or some such) so that the user can select the directory in which to search for the previously specified file extension.
'TB2_Destination_Sheet' is a text box for the user to type the sheet within the workbook in which to list the files found within the specified directory.
'CB1_Find_Files' should activate the macro to find any files for the specified criteria.
There is also a Button 'Find File Types' in Sheet1 of the file which should activate the userform 'UF1'.
View 14 Replies
View Related
Feb 1, 2009
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.
My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?
View 5 Replies
View Related
Sep 17, 2009
I need a macro to get the values from cells D29 and H24 in the Resource Calculator sheet and populate it into cells N8 and O8 in the Input form.
Users will then be able to change the information in the calculator and click the macro again to populate N9 and O9 and so on.
Is there a way to do this?
I've attached the file for you to see.
View 13 Replies
View Related
Oct 3, 2012
I have a macro which refreshes a query when the spreadsheet is opened. This works fine when online.
However, if the user is not online, the query is unable to refresh and the macro just hangs.
Is there a code which will enable me to say " if unable to refresh then move on to the next line"?
here's the code below.
Private Sub Workbook_Open()
Sheets("Houselist").Activate
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Front").Select
Range("A1").Select
End Sub
View 2 Replies
View Related
Mar 4, 2014
I am looking for a macro that i can store in my personal.xlsb. what i need is pretty much is something like this
private sub workbook_open
if workbook.name "inventorysummary.csv"
then application.run "personal.xlsb!capacity"
end sub
I only need it to run just for this file and i cannot place it in the file due to it gets replaced every day. Which if it didn't get replaced. I know how to do auto opens when the file stays the same I am just unsure for this.
View 1 Replies
View Related
Nov 6, 2011
I use Excel 2003 at work. I'm looking for some code that will Open a folder and then lets the user to select a file then continues to run the macro.
The file name they select will look similar to this K2271011.504 or K3011111.201
View 3 Replies
View Related
Mar 16, 2007
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?
2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
View 2 Replies
View Related
Jul 16, 2007
I have written two VBA programs around the same time. Both run on open and pull external data and create graphs. My problem is that I want the end user to be able to run the report multiple times by choosing the name of the macro from the Excel macro menu (i.e. Tools>Macro>Macros) but only one of the workbook macros shows up on the menu. why the other macro is not visible on this menu???
View 2 Replies
View Related
Dec 15, 2008
I've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another.
When I run the macro from the Tools>Macros menu it works perfectly.
But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed.
The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button.
Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!
View 12 Replies
View Related
Jan 11, 2010
Put in a statement within a Macro that populates cells with the values that I want it to but instead of populating all at the same time, is it possible for the values to be delayed.
I have designed a mock spreadsheet (attached) it has two columns 'Before' and 'After'
After = Before values (in this mock)
When you press the button, the values are populated straight into the 'After column' can we add the delay between the values? So that the values dont come up straight away.
View 3 Replies
View Related
Jan 27, 2010
Macro to export and import a module and assigning different macro automatically ...
View 9 Replies
View Related
Oct 9, 2012
I have recorded the following marco and it works fine. I would like to modify it so the user would highlight a range of cells rather than have the fixed area :
"'ConsExecProp@20120920'!R4C5:R113C21"
What code lines would need to be added?
Application.Goto Reference:="R4C5"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
Array("'ConsExecProp@20120920'!R4C5:R113C21"), Version:=xlPivotTableVersion12 _
[code]....
View 4 Replies
View Related
Apr 17, 2014
I have a userform with a command button which fires a macro.
everything works fine so far.
my problem is:
I would like to add a msgbox at the end of the macro which confirmes "successfully completed".
I cannnot simply add the msgbox at the end of the macro. don't know what I'm doing wrong.
(see below)
Private Sub CommandButtonOK_Click().
If Me.OptionButton1.Value = True Then
ThisWorkbook.Worksheets("PropertyWorksheet").Range("A1").Value = "Government Securities"
ElseIf Me.OptionButton2.Value = True Then
ThisWorkbook.Worksheets("PropertyWorksheet").Range("A1").Value = "Corporate Bonds"
[Code].....
Unload Me
Application.ScreenUpdating = False
Sheets("MySheet1").Select
Application.ScreenUpdating = False
'run macro
MyMacro1 (adds, hides and deletes various sheets)
MyMacro2
[Code]....
View 2 Replies
View Related
Jul 3, 2007
Here in our department we made a pretty elaborate macro that takes a report and sorts them out to 17 different sheets in a one workbook. This Macro pulls a file from a specific location on our server and then opens the CSV sorts it out color codes all the important information and saves it back onto the server under you specific initials.
They are four PC's along with our Managers laptop that run this Macro daily.
About 3 weeks ago my Managers laptop stops running the Macro completely and hangs in the middle of the whole thing. Eventually crashing Excel.
We try to remove the modules and re-import them back into the personal macro workbork but this does not work. The Macro's did not change and still fully function on the other four desktops to this day.
I uninstall Office on my Managers laptop and reinstall. Import the Modules again and still hangs up in very same spot it did 3 weeks ago.
I've tried to lower the macro security to the lowest level also and I've still had no luck with this laptop. I don't understand. The Macro's function perfectly on other PC's but will not function on this laptop.
View 9 Replies
View Related
Jan 7, 2009
I have a Sheet sheet1 and I want to run a macro when the cell D2 in Sheet1 is equal to 10,7,5,and 3. I only want this macro to run when those values are reached the macro then puts the data onto a sheet called wps. The macro is run as a module and is a sub macro.
View 9 Replies
View Related
Oct 3, 2006
I have a question concerning Macros and the Disable/Enable prompt. My boss has a spreadsheet which has been used for years and he recently wanted to and did remove the macros from the spreadsheet (they were no longer necessary), but the disable/enable prompt still appears when the spreadsheet is opened. I replicated this in a test spreadsheet with a simple insert line macro and received the same results. Is there a way to remove the macros and the disable/enable prompt once they are removed? I know about setting the security to low to not see the prompt, but I would think that once the macros are deleted, the prompt should not appear any more.
View 2 Replies
View Related
Feb 18, 2007
I recently installed Excel 2007, and have shared others' joy in searching for things on the ribbon. I tried to record a macro to insert an autoshape. Excel creates the macro, but drawing the autoshape is not recorded. I'm sure I'm missing something obvious ...
View 7 Replies
View Related
