Copying formulas from a set of columns

[Sara90]

I have a workbook open with 244 tabs of data. It is all laid out exactly the same. On the first data set I came up with a function that applied to L-P and started in row 17. I need to figure out a way to copy and paste columns L-P into columns L-P on every other worksheet. I am new to this and have no clue what to do! Please help!?

 

[Debraj]

Hi Sara,

welcome to the forum...

You can do a simple trick.
* Select Column L-P,
* Copy it.
* Right click on the sheet name, (bottom right)
* Select "Select All Sheets"
* Press "Ctrl + V"

but WAIT..
* IS your formula is absolute.
* IS it created such a way that, will reflect accurately in all other sheet.

So.. Pleas create a backup, before proceed. and..

and DONT FORGET

1. to Right Click again on the sheet name and "Ungroup Sheet", before next work and
2. If its works.. then click LIKE ...

 

[Sara90]

it didn't work...

 

[Debraj]

can you please upload a sample file.. try to remove secured information, may be only 2/3 sheet is enough, and you can change the figures / data, but let the formula there..

Lets see, if I can help you..

 

[Sara90]

The #-cons tabs are the ones that need to have copied into. 244- cons is the one with the formula for L-P

 

[Luke M]

Here's a quick way. Right-click on sheet tab, view code. From Menu, go to Insert - Module. Paste this into the code block that appears. From Menu bar, click Run - Run Macro (VCR-style play button). Close the Visual Basic Editor (VBE) and your workbook should be ready to go.

Sub CopyFormulas()
Dim ws As Worksheet
Dim copyRange As Range
Set copyRange = Worksheets("244-Cons").Range("L17:P304")

Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    If UCase(Right(ws.Name, 4)) = "CONS" Then
        copyRange.Copy ws.Range("L17:P304")
    End If
Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

 

[Sara90]

Thank you so much!! I would have never figured that out on my own.

 

[Debraj]

As Luke already solved.. still .. another approach..

1. Click SheetName > 244-Cons
2. Select Column L to P
3. Press Ctrl C to Copy.
4. Click Sheet name "1-Cons" . then press Ctrl Key, and click sheet name "2-Cons"
5. If you have more sheets then, you can 1st Click "1-Cons" and then you can click on sheet Name "244-Cons" with Shift Key
6. After selection, it will bit little bit grayed out, to show, all sheets are selected.
7. In the active sheet, select Column L to.
8. And press Ctrl + V to paste.
9. Dont forget, to UNGROUP sheet after completion.