Copy row from one sheet to recent sheet

Question

I want to simply copy the first row "test" into all following sheets (Sheetxx1, Sheetx23, Sheet231, etc. ) ... (like 100 following sheets with different names).

So I tried this by recording a macro (with relative reference) and then went on the sheet, where I want to have it pasted (like Sheetx231) and then did run the macro. But what it did is it pasted again row "test" into Sheetxx23.

How can I make the macro paste the row test of Sheetxx1 into the recent sheet (I am in and run the macro)?

Sub Macro1() 
    Rows("1:1").Select 
    Selection.Copy 
    Sheets("Sheetxx23").Select 
    ActiveCell.Rows("1:1").EntireRow.Select 
    ActiveSheet.Paste 
End Sub

You are not being very clear. Do you want to paste this onto every sheet except the sheet it's copied from? Or is it only some sheets - and if so how do you tell determine what sheets to paste to and what sheets not to? — urdearboy, Jun 04 '20 at 01:50
Also, why are you complicating your `first row` statement by calling it `test`? Unclear if you mean it will always be the first row or if this is a variable. — urdearboy, Jun 04 '20 at 01:52
Please also include your attempt (code) and tell what was going wrong and where exactly you got stuck or errors. — Pᴇʜ, Jun 04 '20 at 05:38
To be clear: I want to copy the first row in sheet1 and paste it into every other sheet in the excel workbook. The Code: Sub Macro1() ' ' Macro1 Macro ' ' Rows("1:1").Select Selection.Copy Sheets("Sheetxx23").Select ActiveCell.Rows("1:1").EntireRow.Select ActiveSheet.Paste End Sub — Alex, Jun 04 '20 at 05:41
@Alex Paste in which location of every other sheet? Into the first row of every other sheet? Please clarify. And please add the code you have tried. — Pᴇʜ, Jun 04 '20 at 05:44
[An image of your code is not helpful](http://idownvotedbecau.se/imageofcode). Please always add code as text formatted as code block. — Pᴇʜ, Jun 04 '20 at 05:49
Sorry !!! This is the code: Sub Macro1() ' ' Macro1 Macro ' ' Rows("1:1").Select Selection.Copy Sheets("Sheetxx23").Select ActiveCell.Rows("1:1").EntireRow.Select ActiveSheet.Paste End Sub – — Alex, Jun 04 '20 at 05:51
Yes, To be clear: I want to copy the first row in sheet1 and paste it into the same place (first row) to every other sheet in the excel workbook. — Alex, Jun 04 '20 at 05:51
Sorry to bother you again, as you can see the code gets pretty unreadable in comments. So the good practice is to always add the code to the original question where it can be formatted. — Pᴇʜ, Jun 04 '20 at 05:52

Pᴇʜ · Accepted Answer · 2020-06-04T08:06:16.367

0

You need to loop throuh all worksheets and copy/paste for each worksheet.

Option Explicit

Public Sub CopyFirstRowIntoAllWorksheets()
    Dim SourceWs As Worksheet
    Set SourceWs = ActiveSheet

    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets 'loop throuh all sheets
        If Not ws.Name = SourceWs.Name Then 'makes sure source and destination is not the same sheet
            SourceWs.Rows(1).Copy Destination:=ws.Rows(1) 'copy first row
        End If
    Next ws

    Application.CutCopyMode = False
End Sub

You might benefit from reading How to avoid using Select in Excel VBA.

Edit according comment:

If you need only the worksheets right of the active worksheet to be pasted, replace

If Not ws.Name = SourceWs.Name Then

with

If ws.Index > SourceWs.Index Then

edited Jun 04 '20 at 08:06

answered Jun 04 '20 at 05:50

Pᴇʜ

56,719
10
49
73

just one more question: How can I avoid, if I have for instance before Sheet xx1two more sheets (Calendar and another one), that it pastes also into the sheets before? So I would just like to have it pasted into all 100 following sheets after Sheetxx1 – Alex Jun 04 '20 at 06:23
I tried it like this: Public Sub CopyFirstRowIntoAllWorksheets() Dim SourceWs As Worksheet Set SourceWs = ActiveSheet Set ExcludeWs = Worksheets("Calendar") Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets 'loop throuh all sheets If Not ws.Name = (SourceWs.Name Or ExcludeWs) Then 'makes sure source and destination is not the same sheet' SourceWs.Rows(1).Copy Destination:=ws.Rows(1) 'copy first row End If Next ws Application.CutCopyMode = False End Sub – Alex Jun 04 '20 at 07:27

Copy row from one sheet to recent sheet

1 Answers1