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