PDA

View Full Version : Formatting issues: copy/paste & Ctr-F



Lawrence
07-22-2008, 10:33 AM
I used the macro recorder but I need some help tweaking things.

The first is a copy/paste. Right now it copies/pastes values and I need to change it to also include formatting. Basically I want to hardcode as I paste, I'd like to paste everything but the formulas.

Also instead of setting the range to A22:K125, how do I set it to where it finds the last row?

Private Sub CommandButton1_Click()
Application.EnableEvents = False
Range("A22:K125").Select
Application.CutCopyMode = False
Selection.Copy
Range("A31").Select
ActiveSheet.PasteSpecial Format:=12, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Application.EnableEvents = True
Range("A1").Select
End Sub


The next macro I recorded is to find/replace. I tried to change "May-08" and "Jun-08"with Me.Range("B5").Value and Me.Range("B4").Value respectively, but I ran into a road block. It looks for "5/1/2008" instead of "May-08" and therefore doesn't work. How can I change it to a mmm-yy format?
Private Sub CommandButton2_Click()
Application.EnableEvents = False
Range("D23:J27").Select
Selection.Replace What:="May-08", Replacement:="Jun-08", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.EnableEvents = True
Range("A1").Select
End Sub

Lawrence
07-22-2008, 10:43 AM
Well, I found a solution to the Find/Replace in the "Dates Screwing up" thread. This is what I came up with:

Private Sub CommandButton2_Click()
Application.EnableEvents = False
Range("D23:J27").Select
Selection.Replace What:=Format(Me.Range("B4").Value, "mmm-yy"), Replacement:=Format(Me.Range("B5").Value, "mmm-yy"), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.EnableEvents = True
Range("D18").Select
End Sub

Still need help for the copy/paste thingy :)

mdmackillop
07-22-2008, 11:01 AM
Private Sub CommandButton1_Click()
Dim Rng As Range

Application.EnableEvents = False
Set Rng = Range(Cells(22, 1), Cells(Rows.Count, 1).End(xlUp)).Resize(, 11)
Rng.Copy
Range("A31").PasteSpecial xlValues
Range("A31").PasteSpecial xlFormats
Application.EnableEvents = True
Range("A1").Select
End Sub

mdmackillop
07-22-2008, 11:03 AM
Try to avoid selecting ranges. It is very rarely necessary

Private Sub CommandButton2_Click()
Application.EnableEvents = False
Range("D23:J27").Replace What:=Format(Me.Range("B4").Value, "mmm-yy"), _
Replacement:=Format(Me.Range("B5").Value, "mmm-yy"), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.EnableEvents = True
End Sub

Lawrence
07-22-2008, 11:08 AM
Hmmm... it gives me a run time error '1004': PasteSpecial method of Range class failed.
Range("A31").PasteSpecial xlFormats

mdmackillop
07-22-2008, 11:17 AM
It fails if the ranges are overlapping. Is this deliberate?

Lawrence
07-22-2008, 11:22 AM
It fails if the ranges are overlapping. Is this deliberate?
Yes it is, I am moving everything down 9 rows and taking the formulas out.

Lawrence
07-22-2008, 11:29 AM
Try to avoid selecting ranges. It is very rarely necessary
Not sure what you mean as I am new to this. Are you referring to the Range("A1").Select I had in there? If so, it was to "be back" on the worsheet instead of having the CommandButton still depressed.

Also, thank you for tweaking the code. Is there an easy way to have a message box pop up when the user clicks CommandButton2? Something like "Message Here" and if the user presses OK then it goes through the find/replace, and if the user presses Cancel, it exits and does nothing.

Lawrence
07-22-2008, 04:46 PM
Anyone?