this should be a really simple one for experienced excel users but me I'm very much the novice.
I've created a command button that should cut and paste A1:O83 and paste to A:86 and it does, but I'm having trouble getting it to paste all my drop downs, formulas, fonts and colors.
if I hit the command button it does copy and paste, but only some of the drop downs and none of the formulas or font/colors.
Welcome to the Forum julesjcombe!
If you are doing this with a button, you must be using VBA when you click the button. You need to show all of the VBA code you are using.
When you paste your VBA code, use code tags. There are two ways to add code tags. One is to select the code part of the text so it is highlighted, then press the "#" button in the edit controls. The other is to simply type in the tags:
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]
Why use code tags? Code tags:
1. Are required by rules (see rule #2)
2. Preserve spacing used to show code structure (otherwise leading spaces and repeated spaces are removed)
3. Add scroll bars to navigate code with long lines or a lot of lines
I didn't write the code and please because I might be saying really silly things here but what are code tags?
I purely copied the code and edited it to make it work, well partly work?
.
He meant ... to post your code here so we can study what the goal is and how it is working, click on the pound sign in the REPLY MENU.
Be certain all of your code shows up between the two HTML Tags [ CODE ] Your code goes here [ / CODE ]
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim xSheet As Worksheet
Set xSheet = ActiveSheet
If xSheet.Name <> "Definitions" And xSheet.Name <> "fx" And xSheet.Name <> "Needs" Then
xSheet.Range("A1:O83 ").Copy
xSheet.Range("A86").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
Application.ScreenUpdating = True
End Sub
BTW I've tried to add Paste:=xlValuesAndNumberFormating etc
.
The use of "Paste:=xlValues" refers to cells with formulas.
If you do not have formulas in that range of cells A1:O83 ... you can use :
Please Login or Register to view this content.
Last edited by Logit; 04-28-2021 at 10:12 AM.
yeah well its coping and pasting but the formulas and drop down list as well as cell color and fonts need to be copied and pasted and this is not happing with this current VBA nor did it work with Paste:=xlVavuesAndNumberFormating
.
Try this :
Because you used the term "ActiveSheet" ... I presumed you wanted the same range copied and pasted from each sheet except those not in the list.Please Login or Register to view this content.
This macro should repeat the action on all sheets not listed.
no sorry now its not pasting anything its just blank?
so in design mode I can see my button is =EMBED("Forms.CommandButton.1","")
so does this mean I need to remove it and start over to get it working correctly?
That doesn't make any sense. It works here.
Look at the attached.
well you don't have and formulas in the cells and I've just put a drop down and and an equals sum and in your data and it only copies a blank cell where the drop down is? and only copies the result of the sum not the formula?
colors TICK
fonts TICK
general copy and paste TICK
formulas copy and paste NO
drop down lists copy and paste NO
am I asking to much can it be done at all?
Yes it can be done.
It is very late here at the moment and I'm falling asleep at the computer. If someone else doesn't provide an answer by tomorrow, I will.
Hope you understand.
Have a good day.
yeah dam I didn't realize you where in the States, go to bed ill get it sorted tomorrow with ya help.
Hello Jules,
My offering to you:-
As you're operating (copy/paste) each time on an Activesheet, I don't see the need to exclude other sheets as per your code in post #5.Please Login or Register to view this content.
I hope that this helps.
Cheerio,
vcoolio.
I gave detailed instructions in post #2 about how to use code tags when you put code into a forum post. The rules require it. Please edit your post to add code tags.Originally Posted by julesjcombe
No further posts are permitted in this thread until this moderation request has been fulfilled.
I gave detailed instructions in post #2 about how to use code tags when you put code into a forum post. The rules require it. Please edit your posts to add code tags.
No further posts are permitted in this thread until this moderation request has been fulfilled.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks