-1

I am new at VBA, I have 300 columns of data in row "A", that needs to be copied into all the other sheets, no matter the name of the sheets.

The only thing I can find online is how to take data from multiple sheets and copy into one sheet. Is there a way to do it the other way around?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Aske M.
  • 61
  • 6
  • Yes, there is a way. – Pᴇʜ Jan 29 '20 at 12:24
  • Does this answer your question? [Copy a range of data to all sheets of the workbook](https://stackoverflow.com/questions/33731417/copy-a-range-of-data-to-all-sheets-of-the-workbook) – Plutian Jan 29 '20 at 12:25
  • Sure, VBA method: look into `For Each Worksheet` loops , Formula method: way easier just make references to the source data sheet i.e. `=Sheet1!A1` on Sheet2 A1, Pivot Table method: add all values to a pivot table and format and change report layout to tabular form. Etc. etc. – Daghan Jan 29 '20 at 12:28

2 Answers2

0

No, there is not a way to work with all the worksheets together.

You have to loop through each one.

Here is one example of how to create the loop

Loop through all worksheets in workbook

ProfoundlyOblivious
  • 1,455
  • 1
  • 6
  • 12
0

You can indeed loop through all the sheets, referencing them by their index rather than name. You can set the loop to exclude the sheet you are copying from and then paste the column into the rest of them.

Here is some code that should do this:

Public Sub Testing()

Dim i As Long
Dim copyFrom As String
copyFrom = "SHEET NAME YOU'RE COPYING FROM"

For i = 1 To Sheets.Count
    If Sheets(i).Name <> copyFrom Then
        Sheets(copyFrom).Columns(1).Copy Destination:=Sheets(i).Columns(1) 'Column 1 = A
    End If
Next i

End Sub

As the code is, it will completely overwrite anything that might currently be in column A on the other sheets so if you want to keep that data you'll have to add a new column at the start before copying the column over. This can be done in VBA as well.

Taazar
  • 1,545
  • 18
  • 27