Inserting A Muliple IF Statement In My Recorded Macro
Sep 21, 2009I recorded a macro (which worked), then tried to get more out of it by adding code to add a further function (which didn't work). The code is as follows:-
View 3 Replies
ADVERTISEMENT
Clean Up Recorded Macro?
Jun 1, 2014I want to clean up this recorded macro but not sure how, it highlights duplicate values in a column
[Code].....
Shortening Recorded Macro?
Jul 15, 2012shortening the below recorded macro?
Code:
Sub Macro1()
'
' Macro1 Macro
[Code].....
Eliminating Clutter In Recorded Macro
Oct 26, 2011I have recorded the following macro and made few changes to it with my little VBA knowledge. But the code still looks very messy.
Code:
Application.ScreenUpdating = False
Sheets("Sheet2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;E:MacrosBSE Indices.iqy", Destination:=Range("A1"))
.Name = "BSE Indices"
.FieldNames = True
[Code] ..........
Build Undo In Recorded Macro
Oct 2, 2006I have recorded certain macros. But after executing macro 'Undo' do not work. How can I do it ?
View 3 Replies View RelatedRecorded Macro Missing Paste Commands
Nov 13, 2008I have been using the "record macro feature". What I want the macro to do is as follows.
1. cut all data from column C and paste it into G.
2. Use the textTocolumns feature to split a comma delimited string into 2 seperate columns.
3. user the textToColumns feature to remove any leading spaces from column G.
4. cut columns d-H and shift them left startign in column C.
I can do all of this find bu using the GUI while recording the macro but when I check the vb script it's completely missing the paste commands.
When I try and run the macro it, quite rightly, returns "run-time error '1004':No data was selected to parse.
Below is the resulting recorded macro.
How do I amend this macro do include pasting the cut data?
Loop Or Repeat Recorded Sort Macro?
Aug 10, 2011I have a workbook where I need to sort data with range (K2:L24), then move down 208 rows and sort range (K210:L232) and repeat this upto 1000 times. I have shortened a recorded macro as an example and would like to know how to loop or repeat.
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 6/04/2011 by Greg
[Code].....
Recorded Macro - Apply On Pivot Table
Dec 8, 2011Below is a recorded macro i am trying to apply on a pivot table.
Code:
Range("F4:H4").Select
Range("H4").Activate
Selection.AutoFilter
ActiveSheet.Range("$G$4:$G$100000").AutoFilter Field:=1, Criteria1:="21", Operator:=xlTop10Items
The problem i am facing is that when i am trying to run it is not working.
Recorded Macro Incorrectly Hiding Columns
Nov 18, 2013I've recorded a macro in which I unhide certain columns, copy and paste some information then hide those columns again. The problem is that when the macro is finished, it incorrectly hides columns K to AN. I did not record that and it's not in the code so I'm lost as to why it's happening.
The macro is as follows :
Code:
Sub COPY()
'
' COPY Macro
'
'
Application.ScreenUpdating = False
Columns("K:BT").Select
Selection.EntireColumn.Hidden = False
Range("M5:N24").Select
Selection.COPY
[Code]...
Why it's hiding everything from K to AN. I've tried recording the macro several times but it's just not working, no matter the order in which I hide columns when recording it.
Worksheet Function Too Long To Be Recorded In Macro
May 11, 2009I have a list of items in Sheet1 column A (starting from row 1).
I would like the corresponding column B to have certain values depending on the value in column A (same row), as follows:
If the value in column A has "SYS????CZ", column B should be "HPC"
If the value in column A has "SYSNIS", column B should be "NIS"
If the value in column A has "SYSJBE", column B should be "JBE"
If the value in column A has "ICG????", column B should be "HPC"
If the value in column A has "IL????", column B should be "RUP"
If the value in column A has "SYSHPC08", column B should be "HPC"
What I tried was to record a macro while typing the formula in B1: ...
Speed-Up/Optimize Recorded Macro Code
Aug 30, 2006Im setting up a spreadsheet that does engineering calculations. Im using macros to run sizes from a standard schedule. It basically takes the values from one sheet (schedule) to another (calculation), then the result from the calculation sheet (Value only, not the link) is pasted back into the schedule. The macro seems very bulky and im sure that it can be made more efficient with a loop. here is a sample of the code from the macro;
Sheets("Calc sheet").Select
Range("C6").Select
ActiveCell.FormulaR1C1 = "=Schedule!R[1]C"
Range("C7").Select
ActiveCell.FormulaR1C1 = "=Schedule!RC[1]"
Range("C8").Select
ActiveCell.FormulaR1C1 = "=Schedule!R[-1]C[2]"
Range("C9").Select
Sheets("Schedule").Select
Range("G7").Select..................
Recorded Macro Not Including All Rows. 2007
May 30, 2008I manually create a pivot table (and record my actions) the pivot table references all of the information in my data range (70k+ lines). When I run the recorded macro the new pivot table limits the data range to the first 65536 lines (the old limit)....
View 6 Replies View RelatedInserting A Block Of Cells Using An If Statement
Jun 26, 2008task of inserting a block of cells into a worksheet.
I have saved 4 different blocks of cells and depending on an option chosen from a drop down menu i want to select one of more of these sets of data and paste them into a worksheet.
Recorded Macro Doesn't Seem To Provide Expected Result
Sep 21, 2009On column A, I simply do an A-Z sort that gives the expected result. So I recorded it as a macro. In column G is a code that corresponds with column A. When the marcro is executed Column A is sorted but the corresponding value in G stays where it is. Clicking the A-Z button works. But when that action is recorded as a macro I get the above quirck. Since VBA is not my beef, I don't really know what wrong.
View 4 Replies View RelatedMacro Recorded To Extract Data From Mainframe System?
Oct 12, 2012I have following macro recorded to extract data from a mainframe system
open "C:DesktopReportRBDRBD.txt" for OUTPUT as #1
rc% = ps.sendstring("{clear}")
rc% = ps.sendstring("a24octdelbom*9w{enter}")
test1 = PS.getdata(450,3,27)
print #1,test1
The requirement here is run the above macro for the next 30 days automatically, for example
rc% = ps.sendstring("{clear}")
rc% = ps.sendstring("a25octdelbom*9w{enter}")
test1 = PS.getdata(450,3,27)
print #1,test1
and so on till 23 Nov. From the start date the macro should run for the next 30 days. Currently i am changing the dates manually in the macro
Converting Recorded Macro Into Workable VBA To Add New Sheet At End Of Workbook
Apr 22, 2013I have a button with a macro assigned. The macro needs to create a new worksheet at the end of the workbook, rename the new sheet to "New County", make the cell size match the rest of the workbook, and then paste in a template from another sheet called "Master".
I recorded a macro that does this, but it has a static reference to the new sheet instead of what ever the new sheet is. So the Macro works one time, but never again. I'm sure this is an easy fix, and perhaps there is a much easier way to do this, but my limited VBA knowledge is hampering me.
Code:
Sub AddNewCounty()
'
' AddNewCounty Macro
' Adds a new County sheet to the workbook.
'
'
[Code]...
Validate Texbox :: Value Entered Is A Muliple Of ...
Feb 7, 2009Is it possible to validate a texbox at entry time to ensure the value entered is a muliple of 6.50.
i.e. ONLY 6.50 or 13 or 18.5 etc is entered. I have used the MOD command at spreadsheet level to accomplish this but have no idea if it's possible at userform / VBA level.
Working With Muliple Workbooks/xls Files
Jun 20, 2007in simple terms im trying to cross reference a name list with registers from another wrokbook. so basically the desired result is. i press my button and it loads the second workbook up, it then uses the a name list in the new book and searches for that names maches in the registers (a group of sheets) on the previous workbook then if a match is found it colours the background in green. I am however getting errors e.g. "run time error: '424': Object required"
with this line then highlighted in yellow:
u = ShtNm.Sheets("Sheet1").Cells(1, "C").Value
Sub Macro1()
Dim u As Integer
Workbooks.Open FileName:= _
"S:HR TrainingO2 Operational TrainingRetentions and CollectionsUpgrades
ames 18th june onwards.xls"
ShtNm = ActiveWorkbook.Name...............
Excel 2010 :: Result Of Recorded Macro Is Different From Hand Work
Mar 10, 2014I have csv file with tab delimited fields (see attached blacklist.csv). 3rd and 4th fields are in format dd/mm/yyyy hh24:mi:ss, some fields can be zero. I need to split data to columns and display datetime fields in format dd.mm.yyyy hh:mm:ss.
OK... open csv in Excel, select column A, press button "Split in columns", select tab as delimiter, choose date format (DMY) for 3rd and 4th field and press OK. Test is splitted into columns, now select columns C and D and select desired format "dd.mm.yyyy hh:mm:ss". Then I select columns A:E and set column widths to fit data in columns. This all is OK and without problems (see attached file Handmade.xlsx).
But now... Now I record all previous step into macro. There is macro text: [Code] ....
Now I copy macro text into clipboard and open blacklist.csv again. Insert button to list and bind it to new macro. Paste text of macro from clipboard. (See attached file blacklist_beforeClick.xlsm). Now click button Btn and voila... some dates remain in previous format, some dates seems to be in desired format, but day and month are swapped. Why are there differences when comparing data made by hand and data made by macro??? compare.jpg This is the same behavior in more computers, some use Czech version of Excel 2010, some use English version.
Automate Recorded Macro - Sort Range Of Cell Based On Two Values
Jan 23, 2013I have recorded a macro to sort a range of cell based on two values that is dependent on time in another cell. I now want the macro to run automatically when refreshing the workbook with F9, so as the time changes so will the sorting. Everything works fine except the sorting doesn't refresh when F9 is refreshed.
My recorded macro is:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, FillCT As Long
For X = 1 To 4
If Cells(Rows.count, 1).End(xlUp).Offset(0, X - 1) <> "" Then FillCT = FillCT + 1
[Code] .....
Excel 2010 :: Change Default Pie Chart Colors In Recorded Macro?
Mar 20, 2014I am working on a project where I am virtually almost finished except for a minor change with the pie chart. I am analyzing some data and recorded a macro to do this and also the pie chart for visualization. However, I do not like the color of the default pie chart colors and would like to customize it. How could I change this within the macro I have recorded?
[Code] .....
Looping Recorded Macro - Copy And PastE Information From One Cell To Other Cells
Jul 15, 2014I've recorded a Marco copying and pasting information from one cell to other cells and some of the copying functions paste to two cells. I just need this to loop through all information I'm copying from the the new cells.
Range("I1").Select
Selection.Copy
Range("D1").Select
ActiveSheet.Paste
Range("J1").Select
[Code]...
Excel 2011 :: Mac Recorded Macro Does Not Work With Windows - Runtime Error 1004
Nov 20, 2013I have been given a macro recorded on a mac and it works fine with Mac Excel 2011. Unfortunately, I need to make it to work on a windows machine, but it comes up with an error message "macro unable to set width property of the window class. Run-time error 1004". When I press debug it takes me to the line .Width = 1456.
Below is the chunk of the code that becomes highlighted when pressing debug.
With ActiveWindow
.Width = 1456
.Height = 795
End With
Recorded A Function And Got The Message Box "Invalid Outside Procedure" When I Tried To Run The Macro
Jul 28, 2006I recorded a function and got the message box "Invalid Outside Procedure" when I tried to run the macro. I don't understand what it means or how to prevent it.
View 2 Replies View RelatedInserting Multiple Cells And Shifting Down Column Multiple Rows In IF Statement
Sep 7, 2012how to shift data in a column down multiple rows while in an IF statement. I am assuming you cannot just simply repeat the insert cell formula.
Here is my code - it's ugly but it was working when I just needed it to move down one cell:
lastrow = Range("A2").End(xlDown).Row
For i = 2 To lastrow
Range("B" & i).Select
If Range("B" & i).Value = Range("B" & i).Offset(-1, 1).Value Then
[Code].....
Inserting Row Using Macro
Mar 16, 2009I'm wanting to add a button on the last row of a worksheet to "add rows" above the buttion (entire row). I have some cells (6 total) that have a formula that needs to carry over to the newly created rows. Some cells remain blank but some cells need to have the formulas fill down to the new rows (i.e.: =sum(a2+1), =sum(a3+1)...
Is it possible to give option of choosing how many rows someone wants to insert?
Help in creating the button, option to choose # of rows and the macro to insert those rows.
Inserting Code Via Macro
Mar 5, 2012Is it possible to insert for example Private sub Workbook_open() into This workbook by executing macro?
View 8 Replies View RelatedMacro That Allow For Inserting New Columns?
Oct 17, 2012I have recorded a basic macro that allows me to copy a formulas in cells CF11 to CH11, and it pastes it into cells in columns CJ to CL. Copy of code is below if you need it.
The issue I have is that I need to insert new coumns into the worksheet, and I need to copy the formulas from columns CF to CH, and they will now need to pasted to columns CN to CP. Note that this is a monthly report where we keep the prior months, so each month will need to add new columns. There are 8 tabs in the workbook, and they all use the same macro, just over different rows (columns all line up).
Is there a way that I can get the macro to paste into the correct column without me having to adjust the macro each time?
Copy of code is as follows:
Range("CF11:CH11").Select
Selection.Copy
Range("CJ11").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("CJ20:CL20").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
[code].....
Inserting Column Macro
Dec 22, 2006simple little procedure to insert columns on two sheets and call each by the same name. Macro runs off a command button and inserts a column at E:E and gives the user an input box with which to name the inserted columns.
Code: ...
Macro For Inserting Rows, After Every Nth Row
Aug 8, 2006I need to insert a new row, in a spreadsheet, after every ninth row that now exists. How is this easiest done?
View 3 Replies View Related