Repeating macro code

Question

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.

What did you try? What are you having trouble with?

SLaks
– SLaks

2013-01-18 18:04:49 +00:00
Commented Jan 18, 2013 at 18:04 — SLaks
– SLaks, Commented Jan 18, 2013 at 18:04

Jon Crowell · Accepted Answer · 2013-01-18 19:10:21Z

2

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

edited Jan 18, 2013 at 19:10

answered Jan 18, 2013 at 18:19

Jon Crowell

22.4k15 gold badges94 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zaidi Badron Over a year ago

in sheet1 column A1 is ALB1, column D1 is ALB001, E1 is ALBV001, another clumn A2 is ALB2, cumnn D2 is ALB002, E2 is ALBV002, this is repeat 100 times over to the next row, i have another tab sheets which is name is ALB1,ALB2, ALB3 and so on, so refer to column A as a name of another sheets in the same work book, i want to copy data in the every each row to the another sheets.

Zaidi Badron Over a year ago

the location to paste the data is the sama at every tab sheets , which is C1. thanks

Collectives™ on Stack Overflow

Repeating macro code

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related