CODEWITHSUNDEEP
vbaexcel
Zaidi Badron
Zaidi Badron

Reputation: 1

Repeating macro code

I recorded this macro:

Sheets("Sheet1").Select
Range("D4:E4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("ALB3").Select
Range("C1").Select
ActiveSheet.Paste

I want to make a loop to repeat the process. From range D4:E4 to D200:E200 when do select

To paste that on respective sheet name from ALB3 to ALB196.

My data in sheet 1.

Column a is sheets name, column d4 and e4, is the data that I want to paste on every sheet already created.

Upvotes: 0

Views: 527

Answers (1)

Jon Crowell
Jon Crowell

Reputation: 22358

If you're trying to copy a range from one sheet to another, you don't need a loop and you don't need to select. You can use copy syntax that doesn't use your clipboard.

Try this:

Sub CopyRangeToAnotherSheet()
    Dim source As Worksheet
    Dim target As Worksheet

    Set source = ActiveWorkbook.Sheets("Sheet1")
    Set target = ActiveWorkbook.Sheets("Sheet2")

    source.Range("D4:E200").Copy target.Range("ALB3")

End Sub

To copy the source range to all sheets in the workbook except the source worksheet, try this:

Sub CopyToAllSheets()
    Dim ws As Worksheet

    For Each ws In Worksheets
        CopyRangeToAnotherSheet (ws.Name)
    Next
End Sub

Sub CopyRangeToAnotherSheet(targetName As String)
    Dim source As Worksheet
    Dim target As Worksheet

    Set source = ActiveWorkbook.Sheets("Sheet1")
    Set target = ActiveWorkbook.Sheets(targetName)

    If target.Name <> source.Name Then
        source.Range("D4:E200").Copy target.Range("ALB3")
    End If
End Sub

Upvotes: 2

Related Questions