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