• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Recorded Macro - Need help in modification

Status
Not open for further replies.

Rajesh S

Member
Hi All, I have recorded a macro for inserting a line in A1 for updating the sheet name for all the rows. However, the constraint here is that the output is based on the final row selected (say 5000). Hence though the rows varies on the data in sheets, formula copies the information in a static place which results in deletion / addtion to the remaining fields. Also, format is not getting copied inspite of having recorded the format copier too.

Can anyone support me in this. Given below the macro which I use currently.


Code:
Sub Sheetnamecopy()

    Selection.EntireColumn.Insert
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Month"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "=MID(CELL(""filename"",R[-1]C),FIND(""]"",CELL(""filename"",R[-1]C))+1,255)"
    Range("A2").Select
    Selection.Copy
    Range("B2").Select
    Selection.End(xlDown).Select
    Range("B50000").Select
    Selection.End(xlUp).Select
    Range("A2").Select
    Selection.End(xlDown).Select
    Range("B1048576").Select
    Selection.End(xlUp).Select
    Range("A50000").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.End(xlUp).Select
    Range("B1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("B1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.End(xlToLeft).Select
    Range("A1").Select
End Sub
 
Last edited by a moderator:
Rajesh S
Ref to #2 & #3 Replies ...
Move ... means move from place 'one' to place 'two'
... not to copy or create new.
> This thread is closed.
 
Status
Not open for further replies.
Back
Top