Copying Block of Formulas to All Sheets Excel VBA

Question

I have a workbook with many (variable total number) sheets which all have identical column headings and variable number of rows. On the master sheet ("Sheet1") there is a large block of formulas that I need to copy along with formatting to all other sheets. The sheets already have unique titles so it won't work to base it on sheet numbers.

I know it's easier to hardcode the formulas but that's a major task for another day (it's very, very many formulas).

I basically have to copy and paste the formula block from a completely separate workbook into "Sheet1", then copy it from that. Here's what I have so far:

Windows("Step Formula Base.xlsx").Activate
Columns("AK:BP").Select
Application.CutCopyMode = False
Selection.Copy
Windows("NA - Sourcing - HR-IT-SLS Candidate Data %28All Candidates%29.xlsx"). _
    Activate
 Range("AK1").Select
 ActiveSheet.Paste
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
Sheets.FillAcrossSheets_
    ws.Range ("AK:BP")

I'm not quite getting the FillAcrossSheets--thought it would be an easy fix but it breaks on that line. Any thoughts or help?

Thanks!

did you read the MSDN article on the FillAcrossSheets Method — Scott Holtzman
Yeah that's where I got the idea, but the problem is I want this to be a flexible code that works if the total number of sheets change. I also don't want to write in the titles of each sheet because they may change (so I can't fill in an array with each title specified). — TwoHeartedKale
If you select all sheets, it will do the same action to all of them so do that before you paste? No coding necessary. — findwindow
Amazing--spent so much time trying to solve this and it was the simplest possible answer. Yes, it worked to just select all sheets and paste the block into all initially. — TwoHeartedKale

TwoHeartedKale TwoHeartedKale · Accepted Answer · 2016-04-04T20:12:07

Here's the working code:

Windows("Step Formula Base.xlsx").Activate
Columns("AK:BP").Select
Application.CutCopyMode = False
Selection.Copy
Windows("NA - Sourcing - HR-IT-SLS Candidate Data %28All Candidates%29.xlsx"). _
    Activate
 Sheets.Select
 Range("AK1").Select
 ActiveSheet.Paste

Copying Block of Formulas to All Sheets Excel VBA

1 Answers