Macro for copying pasting to a new column every time

[Marc L]

As my code does not use worksheet name but worksheet CodeName,
there is nothing to mod when worksheet is renamed
as CodeName belongs to Sheet2 !

Sub TryMe()
    MsgBox "Worksheet :" & vbLf & vbLf & "        Name  :  " & ActiveSheet.Name _
                         & vbLf & vbLf & "CodeName  :  " & ActiveSheet.CodeName
End Sub

And as it's visual in VBE Project window :

​

in this sample first worksheet name is "abc" and its CodeName is Sheet1 …
Its original name was "Sheet1" but its CodeName did not change !

If you really wanna use worksheet name, replace in code its CodeName
by Worksheets("name") …
​

 

[Saraah]

Hi Marc,
I tried the below code, it is pasting numbers but I want to do paste special as there are formulas in the original file.
How this can be edited to paste values ?

Sub Macronew()
Worksheets("Sheetname").[C4:C40].Copy Worksheets("Sheetname").[B4].End(xlToRight)(1, 2)
End Sub

 

[YasserKhalil]

Hello Saraah
Please put the code between code tags
Try the following lines

Sub Test()
    Worksheets("Sheetname").Range("C4:C40").SpecialCells(xlConstants).Copy
    Worksheets("Sheetname").[B4].End(xlToRight)(1, 2).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
End Sub