Copy and paste data into multiple worksheets (efficiency)

Question

I want to copy a range of data from a worksheet (in this case worksheet Data) and paste this in multiple worksheets.

The code below does the job, but is very inefficient. Does anyone have any tips or an example of how this can be written more efficient?

I'm still getting the hang of VBA. Thanks in advance.

Sub Tabs()

'Compliance
Worksheets("Data").Select
    Range("A1:O33").Select
        Selection.Copy

Worksheets("Compliance").Activate
    Range("A1").PasteSpecial

'Advies
Worksheets("Data").Activate
    Range("A1:O33").Select
        Selection.Copy

Worksheets("Advies").Activate
    Range("A1").PasteSpecial

'IBM Fit For Future
Worksheets("Data").Activate
    Range("A1:O33").Select
        Selection.Copy

Worksheets("IBM Fit For Future").Activate
    Range("A1").PasteSpecial

'30%
Worksheets("Data").Activate
    Range("A1:O33").Select
        Selection.Copy

Worksheets("30%").Activate
    Range("A1").PasteSpecial

'ITC
Worksheets("Data").Activate
    Range("A1:O33").Select
        Selection.Copy

Worksheets("ITC").Activate
    Range("A1").PasteSpecial

'Expenses
Worksheets("Data").Activate
    Range("A1:O33").Select
        Selection.Copy

Worksheets("Expenses").Activate
    Range("A1").PasteSpecial

Worksheets("Data").Activate
    Range("B4").Select

End Sub

For some reason Stack Overflow does not let me post this question, I get the error that I have to much code in comparison to text, so don't mind this sentence. I think the code above is self explanatory, nevertheless, If something is not clear please let me know and I'll elaborate further. Again, thanks for any help.

`Does anyone have any tips or an example of how this can be written more efficient?` [OH YES!](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Siddharth Rout, Feb 12 '20 at 12:10
^^, I would want to add that you could iterate over an array of worksheet names. To make this possible in just a few lines of code. Also, is copy/paste a necessary? Or do you just need to transfer values? — JvdV, Feb 12 '20 at 12:12
Thanks, I've seen the post before and is very helpful, how do I implement it in this example? Seems it should be a quick fix. — HoekPeter, Feb 12 '20 at 12:18
That post has all that you need. Could you show us what did you try after reading that link? — Siddharth Rout, Feb 12 '20 at 12:23
You don't need copy repeated for the same range, just copy once and paste for several time. Or you can copy and paste at once — user11982798, Feb 12 '20 at 13:03

Czeskleba · Accepted Answer · 2020-02-12T12:21:37.710

0

SheetsToCopyTo = Split("Compliance,Advies,IBM Fit For Future,30%,ITC,Expenses", ",")

For i = LBound(SheetsToCopyTo) To UBound(SheetsToCopyTo)

'Worksheets("Data").Range("A1:O33").Copy Worksheets(SheetsToCopyTo(i)).Range("A1")
'or
Worksheets(SheetsToCopyTo(i)).Range("A1:O33").Value = Worksheets("Data").Range("A1:O33").Value

Next

the second one is 5 times faster but you need to tell it the range on the right side of the = as well. Was that what you were asking?

edited Feb 12 '20 at 12:21

answered Feb 12 '20 at 12:16

Czeskleba

464
3
11

`the second one is 5 times faster but you need...` Interesting.. Can you support that statement with any valid test or MS article or any other reputable article? – Siddharth Rout Feb 12 '20 at 12:22
I just tested it. – Czeskleba Feb 12 '20 at 12:28
Yes the second Will be more faster, but just the value, without any format from the source sheet – user11982798 Feb 12 '20 at 13:08
1

yeah I assumed OP just used the macro recorder for an example to post here. After all, taking A1:O33 from one sheet to all others seems odd. This can be expanded to include a source array or different ranges etc.. If I remember correctly the macro recorder always gives you PasteSpecial? So if he wants formatting or something instead of only values, the first one should work. obviously the 5x depends on tons of stuff. another test with clearing all sheets and just a range filled with "a" in every cell resulted in 15x. Obviously code efficiency can`t be boiled down to a single sentence. – Czeskleba Feb 12 '20 at 13:26
I aggree with you here, and also I think OP just want take values only for his purpose, so your code is nice – user11982798 Feb 12 '20 at 13:47

score 0 · Answer 2 · answered Feb 12 '20 at 13:42

Here I will give you two method, copy or just take value:

Sub BulkCopy()
    Sheets("Data").Range("A1:O33").Copy

    Sheets(Array("Compliance", "Advies", "IBM Fit For Future", "30%", "ITC", "Expenses")).Select
    Sheets("Compliance").Range("A1").Select
    Sheets("Compliance").Paste
    Sheets("Compliance").Range("A1").Select


    Sheets("Data").Select
    Sheets("Data").Range("A1").Select
    Application.CutCopyMode = False
End Sub

Sub BulkWrite()

    Dim myRange As Range
    Set myRange = Sheets("Data").Range("A1:O33")

    Dim myArr() As Variant
    myArr = Array("Compliance", "Advies", "IBM Fit For Future", "30%", "ITC", "Expenses")

    For Each myVal In myArr
        Sheets(myVal).Range("A1:O33").Value = myRange.Value
    Next


    Sheets("Data").Select
    Sheets("Data").Range("A1").Select
End Sub

Copy and paste data into multiple worksheets (efficiency)

2 Answers2