By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,959 Members | 1,139 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,959 IT Pros & Developers. It's quick & easy.

excel VBA paste special

100+
P: 142
In exce VBA, I want to copy the format from A1 to the cell end with "*end" to another sheet. All I know is use the code below, how to change the 'A301' to the cell end with "*end".

Expand|Select|Wrap|Line Numbers
  1. worksheets("sheet1").Range("A5:A301").Copy
  2. worksheets("sheet2").Range("C4:C300").PasteSpecial Paste:=xlPasteFormats
  3.  
thanks
May 10 '07 #1
Share this Question
Share on Google+
17 Replies


Expert 5K+
P: 8,434
Sorry, I don't quite follow what you mean about the "cell end". Could you explain a bit more, please?
May 12 '07 #2

Dököll
Expert 100+
P: 2,364
In exce VBA, I want to copy the format from A1 to the cell end with "*end" to another sheet. All I know is use the code below, how to change the 'A301' to the cell end with "*end".

Expand|Select|Wrap|Line Numbers
  1. worksheets("sheet1").Range("A5:A301").Copy
  2. worksheets("sheet2").Range("C4:C300").PasteSpecial Paste:=xlPasteFormats
  3.  
thanks
Hello, joemo2003!

I've had this problem before and could not quite pin it down. Good thing I saw your note. Anyway, I Googled your title and pulled up below link:

http://www.google.com/search?q=excel...e7&rlz=1I7SUNA

Check out the first one, looks promissing. Please let us know whether or not helpful, cannot do it now...I'll need to have the link removed if not conclusive.

Good luck!
May 12 '07 #3

Denburt
Expert 100+
P: 1,356
I would use something along the lines of the following code:

Expand|Select|Wrap|Line Numbers
  1.     Worksheets("sheet1").Select
  2.     Worksheets("sheet1").Cells(6, 1).Select
  3.     Range(Selection, Selection.End(xlDown)).Select
  4.     Selection.Copy
  5.  
  6.     Range("C4").Select
  7.     Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
  8.         SkipBlanks:=False, Transpose:=False
  9.  
Tip: Go to tools - macros - start then do what needs to be done then go to the VBA module look for your macro (usually in a modules folder) then try to figure out the mess VBA created for you. I don't use Excel everyday so I find this to be somewhat helpful at times.
May 14 '07 #4

danp129
Expert 100+
P: 321
I try to stay away from Excel when I can but still know it decent. Anyhow I think this is what you are wanting.

Expand|Select|Wrap|Line Numbers
  1. Dim wksMySheet As Worksheet
  2. Set wksMySheet = ActiveSheet  'or ActiveWorkbook.Sheets("sheet name") 'if you're executing macro from a different sheet.
  3. With wksMySheet
  4.     .Range("a1", .Range("a1").SpecialCells(xlCellTypeLastCell)).PasteSpecial _
  5.         xlPasteFormats, xlPasteSpecialOperationNone, False, False
  6. End With
May 14 '07 #5

100+
P: 142
In exce VBA, I want to copy the format from A1 to the cell end with "*end" to another sheet. All I know is use the code below, how to change the 'A301' to the cell end with "*end".

Expand|Select|Wrap|Line Numbers
  1. worksheets("sheet1").Range("A5:A301").Copy
  2. worksheets("sheet2").Range("C4:C300").PasteSpecial Paste:=xlPasteFormats
  3.  
thanks
well, i think all of you not got what i looking for, what i need help is on how to select the copy range. In my example above, the copy range I want will be various everytime, sometime range("A5:A301"), sometime range("A5:A200"), and there will be text "*end" specify at the end of the range (A301 or A200 in this example). So how to set the copy range from A5 to the cell with text "*end" (A301 in the example above)?
thanks
May 16 '07 #6

Denburt
Expert 100+
P: 1,356
I really would love to help so i will keep trying. You were shown two ways to accomplish the task you described... It is obvious someone does not understand. If you would like to select the last cell used before a blank one in column A you would use:
Expand|Select|Wrap|Line Numbers
  1. Range("a1").SpecialCells(xlCellTypeLastCell)
Are you trying to get the address of that last cell so it can be referenced by other cells or other code? If that is what your looking for then great see below or maybe you can explain in greater detail just what we are trying to accomplish. I could go on guessing alllllllll day.

Expand|Select|Wrap|Line Numbers
  1. Dim mc
  2. Set mc = Range("A1").End(xlDown) 
  3. MsgBox mc.Address()
  4. Set mc = Nothing
  5.  
May 16 '07 #7

100+
P: 142
forget everything i wrote above, What i want just try to copy range("A5:xxx"), where cell "xxx" is in column A, but the row is not know, and cell "xxx" have text "*end". I can do it by use the "do untill" loop to copy the cell one by one, but it is too slow if the range is large. So what I want is something like
range("A5:xxx").copy
Is taht make sense?
thanks
May 16 '07 #8

Denburt
Expert 100+
P: 1,356
No I don't understand are you telling me that Range("A1").End(xlDown).Select
doesn't select the last cell in that row? We have showed you numerous methods for achieving what we think you are doing but you are not telling us why or if they don't work. That just confuses me further try using one of the methods already shown and tell us what happens. Then we can try to proceed.
May 16 '07 #9

100+
P: 142
No I don't understand are you telling me that Range("A1").End(xlDown).Select
doesn't select the last cell in that row? We have showed you numerous methods for achieving what we think you are doing but you are not telling us why or if they don't work. That just confuses me further try using one of the methods already shown and tell us what happens. Then we can try to proceed.
Not as far i try,
I try

Worksheets("sheet1").Range("A5", Range("A5").End(xlDown)).Copy
and
Worksheets("sheet1").Range("A5", Range("A5").SpecialCells(xlCellTypeLastCell)).Copy

but both are not work for me
thanks
May 16 '07 #10

Denburt
Expert 100+
P: 1,356
ok why didn't it work, do you have blank cells in between some of your data? That would be the only reason I can think of as to why it would not work. Did you get an error message or what?
May 16 '07 #11

Denburt
Expert 100+
P: 1,356
If you do have some blank cells in between the data you might try starting at the bottom and work your way up.
Expand|Select|Wrap|Line Numbers
  1. Range("A65536").End(xlUp).select
May 16 '07 #12

100+
P: 142
ok why didn't it work, do you have blank cells in between some of your data? That would be the only reason I can think of as to why it would not work. Did you get an error message or what?
oh, somehow it is working this time, after it paste the selection to another sheet, how can I deselect it?

thanks
May 16 '07 #13

Denburt
Expert 100+
P: 1,356
Use a command to select 1 cell.
Expand|Select|Wrap|Line Numbers
  1. Cells(1, 1).Select
May 16 '07 #14

100+
P: 142
actually, it is not working well,
Expand|Select|Wrap|Line Numbers
  1. ThisWorkbook.Worksheets("sheet1").Range("A5", Range("A5").End(xlDown)).Copy
  2. ThisWorkbook.Worksheets("sheet2").Range("C4").PasteSpecial Paste:=xlPasteFormats
  3. ThisWorkbook.Worksheets("sheet2").Cells(1, 1).Select
  4.  
when i debug it, it have error message: run-time error'1001'; applicaiton-defined or object-defined error.
when sheet1 is open up for view, then the first two line code is working, but not the last line. then when i switch to sheet2 for view, the last line work too.
do u know what is wrong?
May 16 '07 #15

Denburt
Expert 100+
P: 1,356
try adding the following before the third line, it will activate sheet2 and off you go.
Expand|Select|Wrap|Line Numbers
  1. ThisWorkbook.Worksheets("sheet2").Activate
May 16 '07 #16

100+
P: 142
try adding the following before the third line, it will activate sheet2 and off you go.
Expand|Select|Wrap|Line Numbers
  1. ThisWorkbook.Worksheets("sheet2").Activate
thanks, it is working now
May 16 '07 #17

Denburt
Expert 100+
P: 1,356
I like it when I read those words! :):):):):)

Happy coding let us know if you run into anything else.
May 16 '07 #18

Post your reply

Sign in to post your reply or Sign up for a free account.