Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Soon!)

Close Window (X)   
Excel VBA Course
[80% Discount] Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Today!)

Macro For Paste Values And Transpose

Hi all,

This is driving me crazy. Really grateful for help. 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!

Any ideas? Code below....

Thank you!!


Sub Transpose()
'
' Transpose Macro
' Macro recorded 11/03/2009
'
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End Sub


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

(80% Discount Ends Soon!)

View Course




Similar Topics







I did a search in here but didn't find an answer so here goes nothing...

I have a spreadsheet with data from A1 to H1 down to A275 to H275. I select all of it, Copy, go into the Sheet2 tab and select A1, Paste Special -> Transpose and it give me the following error message:
Quote:

The information cannot be pasted because the Copy area and the paste area are not the same size and shape. Try one of the following:
- Click a single cell, and then paste.
- Select a rectangle that's the same size and shape, and then paste.

For the life of me, I can't figure out why it's not working! I have done this many times with other spreadsheets. Has anyone ever experienced this? If so, were you able to resolve it and how?

Many Thanks,
Mike


We have a number of Excel users in our office who cannot copy and paste
between Excel workbooks. They can copy and paste between worksheets. When
you highlight the section to copy and then go to the new workbook both the
paste
and paste special are "grayed out". This is true whether you right-click the
mouse, go to the edit menu, or use control keys. This occurs with any data
type and the most simple workbooks. I have seen some suggestions here but
none have worked for this particular problem. I have reset the menus and
renamed the .xlb files and neither helps. You can open the clipboard and the
paste will work, but there is no paste special option. Any help would be
greatly appreciated. Thanks!




Hi guys,

Looking for some help and would appreciate your help. I want to prevent people cutting/copy/pasting on a spreadsheet I have developed. A couple of users keep doing so which in turn knackers my formulas. There will be occasions where I will need to be able to use these functions for maintenance and updates. Ideally I would like have a private marcro which I can run to enable these features as and when needed.

I've been at this all morning and feel like I'm going round in circles. I've tried various bits of code (sourced via google etc) & have ended up with a whole manner of outcomes but not the one I want.

To summaraise what I am looking for is some vba code which will do the following Disable cut,copy and paste when sheet is opened Enable cut,copy and paste when closed Have a macro which when run will allow me to cut/copy paste so as to implement updates when necessary.
Can anyone help?

Thanks in advance


I am trying to do a very simple copy and paste of a simple formula in Excel 2007 and nothing seems to be working. Excel will not let me copy and paste a formula and will only paste the value into to workbook. For instance, if I make a very simple spreadsheet such as:
A1 type in 10, A2 type in =A1 (calculated A2 to be 10)
B1 type in 5

And then click the copy on B1, and then click paste special on cell B2, the only options it gives me are text and unicode text and so no matter how I paste, cell b2 will always be populated with a value of 5. I cannot imagine a simpler copy and paste and no matter what I do I can't make this work.
All cells are formatted as general. It seems that all copy and pasting of formulas in my excel has been disabled. If I open any spreadsheet on my computer, I can't copy and paste formulas, but do the exact same thing on the exact same spreadsheet on any other computer and it works no problem.

Any help?


Column 1 has roughly 20 rows of information. Cell C1 has the formula =A1.

Is there a formula so that when I drag C1 horizontally into D1, E1, F1, ..., the values placed in each cell will be =A2, =A3, =A4, ...

I do not want to transpose the values from column 1 into C1, D1,.... I want these cells to have a formula that links them up to column 1's values

Thanks

Newbie here and I don't know where to start... I'm trying to set up a macro to when data is pasted to a TEMPLATE worksheet tab it will automatically copy and paste data to another sheet based on the month value.
For example:
If data contains 06/01/2011 in cell A1, then data needs to be pasted to "Jun" worksheet within the same workbook.

Can someone assist or can give some direction? Thank you


Hi,

I use filters a lot at my work. One thing I've noticed is that if I have something filtered and want to copy and paste only what I see from one column to another column right next to it, it will copy what is filtered, but then paste into the unfiltered portion.

Is there some way to copy only what I see when it's filtered, THEN paste only onto the adjancent cells which is also filtered?

For example let's say i have each of the number below in a cell (Fig 1). And then I hide rows 2 -3, so only 1 and 4 is showingI only see 1 & 4 in column A. I want to copy and paste 1 and 4 to the next column over one row so it looks like Fig. 2 when unhidden. Not like Fig. 3.

Fig. 1

1
2
3
4

Fig. 2

1 1
2
3
4 4

Fig. 3
1 1
2 4
3
4


Hi all,

I have some formulae in A1, is it possible for cell B1 to display the value only of A1 without the link to the formulae. I dont want to have to run a macro, or paste values, more of a formula that could be assigned to cell B1 so whenever A1 is changed, so is B1 automatically.

Thanks,

Pedro.


I know that you can do PASTE > VALUES in order to keep your conditional formatting on an existing sheet, but sadly the people in my office are prone to not understanding this ("It's a bit technical") and so they just pasting blocks of text from elsewhere and lose it all.....

Does anyone have any suggestions about how to maintain the formatting when people just do a traditional C&P ?

Thanks


I was testing the following methods (suggested in another thread) to disable copy/paste on a particular worksheet -- now copy and paste is disabled entirely from Excel and, apparently, from each and every excel workbook I've got. HELP!!!!!

http://www.xcelfiles.com/VBA_Quick13.html

http://www.mrexcel.com/archive2/75500/87639.htm



Taxstar


When using the copy and paste function it is extremely slow to paste the
result. Also the file size seems to be increasing to a much larger size for
no reason.
Any feedback would be appreciated.



Hi there, longtime user firsttime poster. Looking for some help as I am a non-expert with macros. Here's what I'm trying to do:

We have to submit things to a certain regulatory body and we usually enter tasks in as soon as they come, do the submission, and then keep a record of that submission.

So, I have a workbook with two sheets, one is "TO DO", the other is "ARCHIVE". Both sheets have the same columns and everything. I am looking for a macro that will automatically cut a (row) from the TO DO sheet and paste it in into the ARCHIVE sheet once it is done, then delete the cut row from the TO DO list so it stays topped up.

The trigger for archiving is the columns M and N which are titled "Complete ?" and each has a validation drop down that says "YES". When both cells in columns M and N have the YES in them, I would like the macro to make the above mentioned actions.

I ran a search on the forums and found something similar, but not quite what I was looking for.

Any help?


I am looking for the code to select the visible data after applying a data filter. Actually I know how to select the data after applying the data filter but the issue is I am not able to exclude the header row and give the target range as used (non-blank) rows only!!

I am using below code to Select the Visible rows in the target range:

Code:

Range("A:p").SpecialCells(xlCellTypeVisible).Select


Problems in this code a

1) after applying the filter, while selecting the data it is selecting all the rows in given range till last row on the workbook. I need this to select the the data only till the last used row in the given range.

2) It is not possible to provide the address of the first row after we apply the filter since the first row address may change depending on the values in the table.

E.g. 1st time when I am running the macro the first row in the visible filtered data is starting at Cell address A4 and next time when I will run the macro it may be A6

3) The Code is also selecting the 1st row which is a header row. How can we exclude it from selection.

Some one please revert with the solution.
Thanks in advance.


I have a spreadsheet that when I copy the formula, it copies correctly
(changes the cells it should subtract), however, the result stays the same.
It matches the formula I copied it from, even though the cells to calculate
are now different. I even did a paste special and said only formula, but
still, same result. If I actually type in the formula, it works fine, but I
have a lot I need to copy and paste. Is there a way I can fix this?

Thanks,



Suddenly, I lost the ability to use copy and paste by right clicking my
mouse. Using Office 2000. Can you help? Thank you!!!



I am trying to get my macro to do this on open. Check if a1 is empty, if its empty run another macro (which is an input box to put something IN a1), but if A1 has something in it, DO NOTHING.

I keep getting errors, saying I'm not formulating my if statements correctly.

This is what I have:
Code:

 
Private Sub Workbook_Open()
If Range("a1") = "" Then macrox
else do nothing
End If
End Sub





I have a co-worker's file that he is having trouble with. He is using Excel 2000 SP3. When copying a cell with a formula in it of "=D6+C6" and pasting it into the next cell down, it will display the same value in the cell as the calculated value from above, but has the correct formula displayed in the formula bar of "=D7+C7".

Example:
A1: 50
A2: 10
B1: 60
B2: 20

A3: Formula: =A1+A2 Displays: 60
Right click A3, Copy, right click B3, paste
A3 displays 60

When I click save, it will change the display value to 80.

I am trying this on his workstation and mine. Mine has Office 2010, so I think there might be an issue with the file itself.

Also, not just copy and paste. I can also just click the top cell after filling in the formula and then drag the bottom right of the cell downward and it will do the same of filling in the correct formula, but have the incorrect value.

I know that I could get him to just click save each time before really looking at the results, but that is just a band aid to the problem.

Any ideas how to fix this?


Hey guys,

I'm trying to write a macro which prints to PDF and saves the file name as the contents of a cell. I've been looking through all the posts currently on this forum to get something working. I'm using the following code -

Sub PrintPDF()
Filename = "C:\Documents and Settings\samb\My Documents\" & ActiveSheet.Range("Z1").Value
SendKeys Filename & "{ENTER}", False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF:", Collate:=True
End Sub

The macro prints to PDF, but then it stops at the Save As stage, where I have to manually enter the name of the file and click Save. I want the macro to automatically name the file with the contents of cell Z1. I then want it to then automatically press enter. Any ideas where I'm going wrong? Any help would be much appreciated!


Hi
I am a new user and trying to setup a database in excel. I can select all addresses and copy but when trying to paste into excel it does so in 1 cell.

Is there a way I can paste the separate email id's into individual cells so I don't have to do it individually?

Many thanks
Patrick


Hey guys,

I want to know how to copy part of a cell and paste it into a new cell.

Example:

a1= 8hAs7c

portion needed: 7c


I used this formula:

=RIGHT(A1,2)

This returns 7c like I want.

However in the next cell I want to source the middle two (As)

And in another cell I want the first two (8h)

How do I go about this?