macros for copy paste command for various sheet of the same excel file?

0
votes

I am a new for excel, I have created excel spreadsheet with about 50 sheets with different names. I want to copy cell a5:a10 to all the sheets at cell b15:b20.

I also want to delete cells a25:a35 from each sheet

at present I have written like this, but it is too much lengthy to include all the sheets. Is there any other short cut method which can copy and paste to all sheets..?

Sheets("Sheet1").Select
Range("a5:a10").Select
Selection.Copy
Sheets("Sheet2").Select
Range("b10:b20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

The name of the sheets : sheet1, sheet2,sheet3....like that upto sheet50

1
excelvba

1 Answers

0
votes

Next code copies range a5:a10 from sheet1 to another sheets in b15:b20 and also deletes range a25:a35 from each sheet: 

Sub test()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then
            ws.Range("b15:b20").Value = Worksheets("Sheet1").Range("a5:a10").Value
        End If

        'ws.Range("a25:a35").ClearContents
        ws.Range("a25:a35").Delete xlShiftUp
    Next

End Sub

if you want just clear content in a25:a35, remove line ws.Range("a25:a35").Delete xlShiftUp and uncomment line 'ws.Range("a25:a35").ClearContents