CODEWITHSUNDEEP
vbaexcel
user2740022
user2740022

Reputation: 21

Copy row on Sheet 1 to all existing sheets

I got a excel file with 30 sheets in it all containing the same first row. I'm searching for a code/way that copies the first row on sheet 1 and pastes it on all the existing sheets.

The rows change regularly and through this way I only have to adjust it once.

Upvotes: 2

Views: 10175

Answers (4)

Jon Crowell
Jon Crowell

Reputation: 22338

You can use .FillAcrossSheets to do this:

Sub CopyToAllSheets()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Sheets.FillAcrossSheets ws.Range("1:1")
End Sub

Note that you can specify what you want to copy with an optional type parameter:

  • Copy both contents and formatting = xlFillWithAll
  • Copy contents only = xlFillWithContents
  • Copy formats only = xlFillWithFormats

The default is xlFillWithAll. If you want to use one of the other options, do this:

' copy contents
Sheets.FillAcrossSheets ws.Range("1:1") xlFillWithContents

or

' copy formats
Sheets.FillAcrossSheets ws.Range("1:1") xlFillWithFormats

In case you don't want to copy to every sheet in your workbook, you can specify a list of target sheets like this:

Sub CopyToSpecificSheets()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim targetSheets As Variant

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")
    ' the sheet we are copying from must be included in the array
    targetSheets = Array(ws.Name, "Sheet2", "Sheet4")
    wb.Sheets(targetSheets).FillAcrossSheets ws.Range("1:1")
End Sub

Upvotes: 4

Dancharim
Dancharim

Reputation: 26

You can try the following : Click and hold on "Shift" on your keyboard, then with your mouse click the last sheet. You'll notice that all sheets will be grouped ( Notice the keyword "Group" at the top top of your excel file). Now everything you do on 1 sheet will be applied to all while in group mode.

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Consider something like:

Sub qwerty()
    Dim r As Range
    Set r = Sheets(1).Range("1:1")
    For n = 2 To Sheets.Count
        r.Copy Sheets(n).Range("A1")
    Next n
End Sub

Upvotes: 0

exussum
exussum

Reputation: 18550

could do a simple way assuming you have the standard 3 sheet set up (Sheet1, Sheet2, Sheet3)

Do all of the changes in Sheet 1, Then click Sheet 2, Shift + Click Sheet 3.

in Cell A1, type =sheet1!a1

copy it across how ever far you need.

Select Sheet 1 (to remove the grouping)

Now sheet 2 and 3 will have the same top row. This works for as many sheets as excel supports. So clicking sheet2, then shift click sheet 50 will fill out all sheets in the range Sheet2 - sheet 50

Upvotes: 0

Related Questions