I would like to create/use a copy command in the Quick Access Toolbar that combines Paste Values and Transpose into a single command. Have tried recording a macro but it becomes specific to the individual workbook that I'm accessing and it copies the formula from that last cell that I copied from. Problem is that I have 19 workbooks to deal with and would like to save some keystrokes.
View 4 RepliesI've created some macros in Excel 2010 which I've added to the Quick Access Toolbar and want to show them using some of my own icons rather than the ones that show when you click the 'Browse' button. There is no option to navigate to any other location.
Is it possible to access icons other than the set provided?
When adding functions to the quick access toolbars in Excel 2007 sometime they do not have an associated icon with the function. The icon shows as a green circle. How to change the icons and also to have a selection of icons like you have had in previous versions of Excel?
View 5 Replies View RelatedI have written a macro. The macro in question simply shows a user form. There is far more code within the user form itself. It is designed to automatically format a workbook.
I saved the macro as an .xlam add-in file to the default folder. I then added a button for the macro to my Quick Access Toolbar. This seemed to work great.
However, when I open a new file that I want to run the macro on, the button disappears. In fact, a 2nd Excel window opens up on my task bar. The original window still has the macro button, but the new window does not.
Is it possible to install a dropdown menu straight onto the Quick Access Toolbar in 2010?
Tried
Code:
Application.CommandBars("Quick Access toolbar")
To reference it, but no joy.
I need to send out an order form (spreadsheet) to 100's of people that need to complete the form and email back to me as an attachment. If I was completing the order form myself I would use the "email" icon that I have pinned to my Quick Access Toolbar (QAT). However, most of the recipients don't even know the Toolbar exists.
Is there a way I can insert an icon / hyperlink in the spreadsheet that does the same thing as the QAT icon. I can insert text to say "click here to email your order" (or similar).
I need to keep it in an excel format and an icon is so much better that asking them to save to their hard-drive and attach to an email, etc.
The QAT icon is exactly what is needed but I need to provide a spreadsheet that works for folk who haven't got the icon.
I've developed a fairly simple procedure which lets me reformat cell backgrounds in a worksheet after various cut/paste operations.I've created a template called BASIC.XLTM which has this code in it. I've added a button to the QAT to enable me to invoke the procedure, but when I click the button to do the reformat, it works for the active sheet, but it also invokes another instance of BASIC.XLTM.
View 2 Replies View RelatedI noticed that my custom "Close without Saving" button stopped working. I checked the VBA editor and my Personal workbook was missing and all of my personal macros were gone. I restarted excel, same issue. Rebooted my machine and they re-appeared. Not sure how what happened, but at least the Personal workbook was not lost. I'm working off my company's network, so often I get the "Personal Workbook is locked for editing" message. I usually select the open as read-only file and go about my merry way. Perhaps I clicked something different on accident without paying attention.
Anyway, I digress, the custom macro button that I had in my ribbon still didn't work, so I removed it and tried again in both the ribbon and the quick access toolbar with no success. The Macro executes if I execute it manually or via the keyboard shortcut, but the ribbon/quick access toolbar buttons won't work. Frustrating, because I use this quite often. I've even tried writing a quick "dummy" macro to see if it was some sort of code error related to the macro, but got the same result. My "Close without saving" code is below (super basic code).
Sub Close_Without_Saving()'' Close_Without_Saving Macro' Closes active workbook without saving changes.'' Keyboard Shortcut: Ctrl+q' ActiveWorkbook.Close (False)End Sub
I want to make a macro that I can copy a column of numbers, select a cell... then run the macro - which will transpose them and paste the values.
I've tried to make this by Recorder... but I keep getting an error.
Run-time error 1004
Paste-Special method of Range class failed.
I've seen other people in the past post this questions, but it seems noone has solved it!
Sub Transpose()
'
' Transpose Macro
' Macro recorded 11/03/2009
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End Sub
I think I have a very straight forward problem, I'm copying about 400 values from one workbook to another (from vertical range to horizontal range) and I currently have about 400 lines of code in order to do this. Below I've pasted the code I'm using now but the macro takes an estimated 30 seconds to run. I figure if I can reduce the number of lines the macro will run a lot faster
RowCount = openWb.Sheets("Library Raw Shear Rates").Range("A3").CurrentRegion.Rows.Count
With openWb.Sheets("Library Raw Shear Rates").Range("A3")
[Code]......
I am running Excel 2003 and in my workbook I created a macro and assigned it to a toolbar comman button, "Copy Filtered Data". This macro copies the visible data into a new workbook.
This works well but others in my company will also be accessing this workbook from a sharepoint and I discovered that when they open it this command button is not on their toolbar.
Would anyone know what I need to do to have this button load on the toolbar when the workbook opens?
Also, I would like this button to either unload or greyout when the workbook that this button is intended for is closed.
The goal of this code is to check between two sheets, if it finds a match between both sheets (somewhere in column A) then it should copy E,F,G columns from Sheet2 to Sheet1..
The Sub Test()
aLastRow = ActiveSheet.UsedRange.Rows.Count ' last row of active worksheet; assume master is active sheet .....
If Range("a7").Value = "" Then
Range("A1:A6").Copy Destination:=Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteAll, Transpose:=True
'Range("C1").PasteSpecial xlPasteAll, Transpose:=True
End If
This command shows error?
I would like to implement specific cell ranges from two specific worksheets each within 33 workbooks (which all have several tabs) into a summary page in a separate workbook.
The cell ranges are going across my spreadsheet in rows and I would like for them to transpose into a columns depending on the data which I have separated by catergory on the summary page. They are all on the same location in each workbook which is separated by country. The cell ranges are E26:P37 and I would like to transpose them and have them put below eachother without overwriting for my format on the summary page, how I can put this together in a macro?
is it possible to import an excel file into access from an excel form?
without using the access, DO.CMD call?
I am getting ready to create a spreadsheet to list vendor's names and emails along with other data. I would like to include at the bottom of the email addresses a quick copy and paste distribution list. I have achieved this in the past by using a similar formula:
View 4 Replies View RelatedIs there a quick way to remove duplicate values from a ROW. The "Remove Duplicates" operation under the "Data" tab only works for a column selection and not a row selection.
One approach I am thinking about, is to transpose the row into a column, run "remove duplicates" and then transpose again into a row.
I was thinking of how the Worksheet_Change(ByVal Target As Range) macro can be used to paste values instead of formula when the user uses Ctrl-C and Ctrl-V. Instead of assigning a macro to a command button for user to activate.
Suppose I have a worksheet where the range ("D7:D56") is where I would want the user to paste his values in it.
I have to factor in the possibility that these values have formulas attached to them, and that the user is not tech-savvy enough to know about the "paste special --> values" functionality of excel, and chooses to use the Ctrl-C, Ctrl-V method instead.
What then appears are ####### which might alarm the user.
Neither would we want the user to press a button (which we can assign the xlpastevalues macro as an alternative) as that adds an extra step for the user. There is also a chance the user might not use the button, or overlooked it.
Is it possible to use the Worksheet_Change(ByVal Target As Range) macro, or sth similar, to paste values instead of formula when user uses Ctrl-C and Ctrl-V?
It appears that my toolbar paste button has been disabled and I think this is due to an auto open macro.
Does anyone know what macro code I will need to run to regain the use of this button?
I have read several articles saying using a command button as a toggle button can't be done but some articles have said it is possible using the state properties.
.State = msobuttondown
.State = msobuttonup
Is there any way to combine this with the onaction property so that when you press the button, it stays down and activates a macro then when you press it again it pops back up and activates another macro.
I use the Paste Value shortcut button that I've added to my toolbar in Excel. Just as regularly, I also need to do a Paste Special, Values Only with the Transpose option selected. Is there a way to create my own shortcut icon and build code to do this?
View 9 Replies View Relatedwhich of these toolbars provides the better 'controls' to paste onto worksheets (as opposed to UserForms)? For those wondering, both toolbars contain some apparently similar controls, e.g., combo box, radio button, spinner etc. but there are differences in their behaviour it would seem...
View 2 Replies View RelatedI have two excel tabs on a spreadsheet, one titled "Reviews" and one titled "Details". On my Reviews tab, I have:
A B C D
1 0 0 1
0 0 0 0
1 1 1 1
. . . .
. . . .
etc.. On my Details tab, I have
A 1 0 1 . . .
B 0 0 1 . . .
C 0 0 1 . . .
D 1 0 1 . . .
(The same information, but presented in different ways). I'm trying to continue to make my Reviews tab as long as possible to match my Details tab but when I drag the cross to continue the pattern, it doesn't follow what I need it to. How can I continue my Review tab down for 600 or so fields to match my Details tab even though they are both in different layouts?
see the attached file below. I have monthly precipitation data for yrs 1950-1999. I need to have the data formatted such that the precipitation data is copy/pasted into one single column, going all the way from jan 1950 to feb 1950 (next row) to mar 1950 (next row).....dec 1999 (last row). On occasion, I will also have data for shorter/longer time periods. the macro code I'd need to take this data and line it all up into one single column?
View 7 Replies View RelatedI have over 300 lines of data that I need to transpose from columns to rows and I am not sure the best way to do it.
I can do it manually 125 times using paste special and transpose and get the results I need but I am thinking there must be a better way.
Example spread sheet inclosed. Column A is what I have - Column C-F is what I need.
I m new to macro and I need to copy the data from 49 cells value which are
present in Two Rows to be converted into TWO columns and 48 rows
I have run a macro for this and I got
Sub Macro4()
'
' Macro4 Macro
'
'
Sheets("WRIGLEY011209").Select
Range("C1:AX2").Select
Selection.Copy
Sheets("Sheet3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("A1").Select
Sheets("WRIGLEY011209").Select
Range("A2:B2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A1:B48"), Type:=xlFillDefault
Range("A1:B48").Select
End Sub
Is there a simple way to combine Paste Link and the Paste Transpose function? I receive a lot of data in rows that I need to put in columns for display purposes. Using transpose is great for doing that but I would like to link it using paste link so I don't have to keep doing each time the data changes. I have recorded macros to see what is happening when I perform the operations seperately as shown below but have been unable to combine them.
Range("A44").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
' need to combine with this
Range("A44").Select
ActiveSheet.Paste Link:=True
How to make a short cut or hot key to do a paste special transpose? Dealing with a lot of data. Want to program it to ctr+f or something of that nature...
View 7 Replies View RelatedAverage formula that will give an accurate answer. My issue is that I need an average of what has been entered rather than for the entire selection. I have paste link transposed the data as it needs to run on Excel 2003 which can only hold 30 items in the Average(number1, number 2 ... etc) and I need 52. My problem occurs when the data is paste linked all blank cells appear as a Zero therefore increasing the count of the average and providing a wrong answer.
Eg when (1, 2, 0, 3) Average = 1.5 is entered the formula works out (1, 2, 0, 3, 0, 0, 0, 0, 0, 0, etc) Average = 0.12 because it divides by 52 instead of 4. As you can see I can't ignore zeros as they are a possible input. What would be perfect is for the paste link to paste the blank cells instead of the providing a zero then the plan =Average(B3:B55) would be fine.
(Note: the new entered data is on the same row but different columns hence the reason for the paste link transpose to get all the data in one column)
I need to copy and paste transpose, some over 100 values, but I want to add a comma after each value, so they can be put into JD Edwards for running a report.
View 3 Replies View Related