I 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].....
I 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] .....
With Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
r.EntireRow.Copy
Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
End If
Next r
End With
-------------------
I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).
I have a named range called 'territories' that contains the list of X101 -> X152.
I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!
I am having trouble with trying to repeat or loop the below statements. I am trying to start with applying text in cell "B2" which will hide column "A" on a separate sheet. I want to know if it is possible to repeat the same statement but loop the statement until cell "SL2" and column "SK"?
[If Range("B2").Text = "" Then
Sheets("43mm").Columns("A").EntireColumn.Hidden = True
Else
Sheets("43mm").Columns("A").EntireColumn.Hidden = False
End If
how do I write a Do Loop so it will repeat the below until row A is empty? I want the sheets to be added and named based on what someone enters on a data input sheet. i.e. if there are 5 rows with data, then it will create 5 sheets named accordinlgy.
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Tab Organization").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "ABC DEF"
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "ABC DEF"
I would like click in cell a6 and then click a button loaded with VBA code that would:
1. Offset over 2 and down 1 to c7 so that is the current cell
2. Repeat down until an empty cell is reached (our example will be c10)
3. Put the items found in c7, c8, c9 into a variable (is that right?)
Variable would then be used in a vba generated email that I have all ready to go. I would want each item placed in the email one after the other like:
Name
Date
Phone
I want to clean up this recorded macro but not sure how, it highlights duplicate values in a column
[Code].....
shortening the below recorded macro?
Code:
Sub Macro1()
'
' Macro1 Macro
[Code].....
I 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] ..........
I have recorded certain macros. But after executing macro 'Undo' do not work. How can I do it ?
View 3 Replies View RelatedI 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?
I 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 View RelatedBelow 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.
I'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.
I 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: ...
Im 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..................
I 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 RelatedOn 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 RelatedI 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
I 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]...
I 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.
I 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] .....
I'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]...
I 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
I have created a macro that copies the outcome of a specific calculation (that is driven by random numbers) and pastes it (as values) into a seperate table. It then recalculates the sheet, copies the new outcome and pastes it into the same table at a row beneath the previous one.
However, I would like to be able to determine how many times this loop is repeated (i.e. how many rows of outcomes will be generated) without having to change the macro each time. Is this possible? Maybe by just linking it to a cell where the number of outcomes/repeats is specified.
I want to loop through all worksheets and sort all columns in each worksheet. Here is what I have, but for some reasson, it only sort the first sheet. Any suggestions?
Sub test2()
Dim ws As Worksheet
For Each ws In Worksheets
Cells.Select
Selection.Sort Key1:=Range("E1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next
End Sub
I am trying to repeat this macro until the last row of data present in column "D".
Sub inserttext()
'
' inserttext Macro
'
'
Range("D2").Select
ActiveCell = "Test Description:" & Chr(10) & "" & Chr(10) & "Expected Results: ERROR level fires stating " & ActiveCell
Range("D3").Select
End Sub
I have a workbook with 4 worksheets. I have a macro ( Cntrl + U ) that runs a series of steps and prints the results with it ending on the beginning page. This works well but I want it to repeat the macro until it reaches a blank cell on the starting page.
View 9 Replies View RelatedI 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 Relatedcan I add to a macro that will repeat that macro on every subsequent worksheet in the workbook.
View 4 Replies View Related