2 Quick Methods to Copy Contents to Multiple Worksheets in Your Excel

Often you will input same contents into several worksheets. Here we have two methods to finish this task quickly.

In our previous article 2 Quick Methods to Input Duplicate Column Headers in Excel, we have introduced two methods to input same contents into several different worksheets. Now we have found another two methods.

Method 1: Insert across Worksheets

The first method will also use the feature of worksheet group. And in this method, the contents are already existed in one of the target worksheet.

  1. Select the target area that contains the contents.
  2. Press “Ctrl” button in the keyboard.
  3. Next click the target worksheets that you want to input the contents in. Therefore, you have created a worksheet group. If you need to select all the worksheets, you can right click and click the option “Select All Sheets”.
  4. Now click the button “Insert” in the toolbar.
  5. And then in the drop-down menu, choose the option “Across Worksheets”.Across Worksheets
  6. Next you will see a new window. You can choose whether you want to copy the contents or formats. In this example, we want to input the same contents into other worksheet, thus we choose “Contents”. Of course the option “All” is also available.Fill Across Worksheets
  7. Next click “OK”. Thus, all the contents will appear in other worksheets. And you don’t need to input the same contents repeatedly.

Method 2: Use VBA Codes

Instead of using the insert feature, here you can also use the VBA code to finish this task.

  1. Press “Alt + F11” on the keyboard.
  2. Right click the “VBA Project”.
  3. Next put your cursor on the option “Insert”.
  4. And then click the option “Module”.Insert Module
  5. Now input the following code into the module.

Sub CopyContents()

Dim i As Integer

    For i = 2 To ThisWorkbook.Sheets.Count

        Sheet1.Range(“A1:C10”).Copy Sheets(i).Range(“A1 “)

    Next i

Application.CutCopyMode = False

End Sub

In this example, the contents are in the first worksheet. And we need to paste the contents into all the other worksheets. In your actual workbook, you may also change the codes and the range according to your actual need.

  1. And then press the button “F5” on the keyboard. Therefore, the same contents will also appear in the other worksheets.

A Comparison between the Four Methods

Now we have listed the advantages and disadvantages in the table below. The other two methods can be found in the article: 2 Quick Methods to Input Duplicate Column Headers in Excel.

Comparison Create Worksheet Group Copy and Paste Worksheet Insert across Worksheets Use VBA Codes
Advantages 1.      This method is very useful if you haven’t input contents into cells.

2.      If you are not familiar with VBA, this method can be a good choice.

1.      You can copy the same worksheets to other workbooks easily.

2.      All the content can be well-preserved in new worksheets.

1.      You can choose to copy contents or formats according to your need.

2.      This method is easy to use if you are not familiar with VBA codes.

1.      VBA code can also be applied in other workbooks.

2.      You can change the codes to fulfill many different requirements. In VBA, you can also copy with or without format with certain rules.

Disadvantages 1.      If you have already input contents into certain cells, this method is not available.

2.      If there are many non-adjacent worksheets, you need to spend a lot of time on creating group.

1.      This will be very annoying if you need to create many same worksheets.

2.      You cannot choose to copy only contents or only formats.

1.      Similar to the method “Create Worksheet Group”, if there are many non—adjacent worksheets and ranges, you will spend a lot of time to select the target area and create the group.

2.      You need to use this feature every time you create a new workbook.

1.      Sometimes the VBA code will make things more complicated.

2.      If you are not familiar with VBA codes, you will meet with problems.

With this table, you can know which method you can use the next time.

Face with Excel Corruption

Excel corruption is one of the data disasters that no one wants to meet with. However, it will still happen even if we don’t like it. Therefore, we should take actions in advance to avoid more serious results. The sophisticated repair tool created by out programmers has the best technology in the world. Hence, you can invest it beforehand. Once you find your file corrupts, you can use it repair Excel at once.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.