excel VBA paste special

[joemo2003]

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".

worksheets("sheet1").Range("A5:A301").Copy
worksheets("sheet2").Range("C4:C300").PasteSpecial Paste:=xlPasteFormats

thanks

[Killer42]

Sorry, I don't quite follow what you mean about the "cell end". Could you explain a bit more, please?

[Dököll]

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".

worksheets("sheet1").Range("A5:A301").Copy
worksheets("sheet2").Range("C4:C300").PasteSpecial Paste:=xlPasteFormats

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+VBA+paste+special+&rls=com.microsoft:en-us:IE-SearchBox&ie=UTF-8&oe=UTF-8&sourceid=ie7&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!

[Denburt]

I would use something along the lines of the following code:

    Worksheets("sheet1").Select
    Worksheets("sheet1").Cells(6, 1).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    Range("C4").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

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.

[danp129]

I try to stay away from Excel when I can but still know it decent. Anyhow I think this is what you are wanting.

Dim wksMySheet As Worksheet
Set wksMySheet = ActiveSheet  'or ActiveWorkbook.Sheets("sheet name") 'if you're executing macro from a different sheet.
With wksMySheet
    .Range("a1", .Range("a1").SpecialCells(xlCellTypeLastCell)).PasteSpecial _
        xlPasteFormats, xlPasteSpecialOperationNone, False, False
End With

[joemo2003]

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".

worksheets("sheet1").Range("A5:A301").Copy
worksheets("sheet2").Range("C4:C300").PasteSpecial Paste:=xlPasteFormats

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

[Denburt]

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:

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.

Dim mc
Set mc = Range("A1").End(xlDown) 
MsgBox mc.Address()
Set mc = Nothing

[joemo2003]

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

[Denburt]

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.

[joemo2003]

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("she et1").Range("A5 ", Range("A5").End (xlDown)).Copy
and
Worksheets("she et1").Range("A5 ", Range("A5").Spe cialCells(xlCel lTypeLastCell)) .Copy

but both are not work for me
thanks

[Denburt]

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?

[Denburt]

If you do have some blank cells in between the data you might try starting at the bottom and work your way up.
[CODE=vb]Range("A65536") .End(xlUp).sele ct[/CODE]

[joemo2003]

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

[Denburt]

Use a command to select 1 cell.
[CODE=vb]Cells(1, 1).Select[/code]

[joemo2003]

actually, it is not working well,

ThisWorkbook.Worksheets("sheet1").Range("A5", Range("A5").End(xlDown)).Copy
ThisWorkbook.Worksheets("sheet2").Range("C4").PasteSpecial Paste:=xlPasteFormats
ThisWorkbook.Worksheets("sheet2").Cells(1, 1).Select

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?