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 RepliesI'm using Excel 2007.
I'm building a spreadsheet which includes a few columns which have activex check boxes which are linked to cells. I want to allow the users of the spreadsheet to insert new rows if necessary. Ideally, when these new rows are inserted, the check boxes would also automatically appear in the corresponding columns of the new row (and be linked to the relevant cells), just the same way that pull-down menus and formulas automatically copy into the new row. Is there a way to do this?
I am currently working on a project that adds shoes to a re-order list, and a receipt automatically when they are 'purchased'.
The re-order list is great as the shoes 'purchased' are supposed to be added one after another, my problem being that the receipt is for a customer.
Once the shoes 'purchased' are added to the receipt it can be printed. I then need to be able to clear the rows with values greater than 0 so that I can start with a new customer, on a clean receipt. I need it to check starting from row "A3" so that my receipt format is not messed up, and for it to finish leaving 2 blank rows so that my other macros still work.
I would like to use a macro to clear the rows to make it easier for the user.
If anyone has any suggestions I would be very greatfull and if you require the document I can arrange for it to be sent via e-mail.
I searched many sites and used codes which delete Rows based on criteria. In my case those codes works, BUT it took so much time about 30 min since there is about 75 thsd rows, and that solution in not time saver. I recorded code below and it is done in seconds. make it dynamic, VBA, since number of rows is every month larger. Basicly, I need code which delete rows based on Column B, where walue is "R"
Code:
Sub DelRowsBasedOnOneCriteria()
'
' DelRowsBasedOnOneCriteria Macro
[Code]....
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].....
Excel 2007.I have a list of postcodes (UK) which have different lengths of characters (including spaces) from 6-8, however our system seems to add additional spaces inbetween the postcode, so it could have upto 11/12 characters (inc spaces) Below is what could come out:
EH21 6PQ - 1 Space (8 Char)
EH12 9HG - 3 Spaces (10 Char)
E1 8DF - 3 Spaces (8 Char)
LL5 1GH - 2 Spaces (8 Char)
L5 1FG - 1 Space (6 Char)
What I need is a formula to ensure each postcode only has 8 characters by inserting spaces between if there's less than 8 char and trimming if there's more than 8 char
So from the above postcodes the desired results would be:
EH21 6PQ - 1 Space (8 Char) - This would be correct
EH12 9HG - 3 Spaces (10 Char) - Trim off 2 spaces from the middle
E1 8DF - 3 Spaces (8 Char) - This would be correct
LL5 1GH - 2 Spaces (8 Char) - This would be correct
L5 1FG - 1 Space (6 Char) - Insert 2 spaces in the middle
I have data in B4:B55 and need a formula to return a count of rows, including rows that are blank. However, there are hidden rows that need to be omitted from the count.
View 1 Replies View RelatedI 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 RelatedI 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].....
Below 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..................
On 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 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] .....
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
delete duplicate rows and leave only the LAST row. The codes I have seen leave the first row.
View 9 Replies View RelatedWhich formula should I use if I want to sum a column without including content from hide rows in the same column.
a2 is hided so I need the sum = 6
1
9
1
2
2
I have many rows that contain some common data but I need to be able to select the row with the most recent data and delete the others. For example, a car part is assigned a unique number which never changes, it may be ordered several times and in different quantities, for each car part I want to select the most recent order and delete any others. Is there a simple way of doing this?
View 9 Replies View RelatedI have a large worksheet with over 8,000 rows and about 5 columns. In several instances, I have several rows that are all exactly the same. It could be 2, 3, 4 or even more rows that are duplicate to one another.
I would like a macro that deletes ALL rows that are duplicates, including the original.
For example, let's say, rows 3, 4 & 5 are all exactly the same, I don't want to just delete rows 4 & 5, I want to delete row 3 as well!
Which formula should I use if I want to sum a column without including content from hide rows in the same column.
a2 is hided so I need the sum = 6
1
9
1
2
2