Copy Worksheets 1,2 and defined part of 3 to new workbook and save with defined name

Hi all,

I have a current workbookCopy of ASP Test RC File.xlsx with 3 worksheets.

Worksheet RC is a summary of all companies rates (Columns G:J). I've shortened this to keep the file small but in reality there are actually another 140 columns after Column G.

What I am looking to do is create an individual file for each Company i.e. each column from G onwards yet keep Columns A:F as these will be the same for all.

Furthermore, I would like each new file contain the Instructions and Cover Page tabs (Sheet 1 & 2).

Each new file should be renamed according to the name listed in G5, G6, G7, etc...

Is there a way to do this with VBA to save me manually having to create 140+ plus files and copying and pasting sheets 1 & 2 into each file?

Any assistance would be greatly appreciated.

Kind regards,

A macro similar to this shall do the work for you:

Sub test()
Dim wbk As Workbook, wbknew As Workbook, last As Long, i As Long, j As Long, fname As String
Application.ScreenUpdating = False
Set wbk = ThisWorkbook
With wbk.Sheets("RC")
   last = .Cells(5, .Columns.Count).End(xlToLeft).Column
End With
For j = 7 To last
  wbk.Sheets(Array("Instructions", "Cover Page", "RC")).Copy
  Set wbknew = ActiveWorkbook
  With wbknew
    fname = .Sheets("RC").Cells(5, j) & ".xlsx"
    For i = last To 7 Step -1
      If i <> j Then .Sheets("RC").Columns(i).Delete
    Next i
    .SaveAs fname 'saving in the default folder
    .Close False
  End With
Next j
End Sub

Provided company names are unique (in the sample file you have TWO Companies C) and are valid as filename - for instance do not have characters like ? * < > [ ] : |

Hi Kaper,

Appreciate the response and the code and hoping you have the time to help me tweak it a bit.

The filename for each new file will actually be based upon a combination of two cells - "G5" - "G7" and then needs to be saved to a specific folder in C:\Users\master\Documents\SAP.

I think the code will work fine it's just that I am running into to duplicate file name issues due to non-unique Company names i.e. Cell G5,6,7,8 are all for Company A, however, cells G7,8,9,10 have different names so by including these in the naming convention it should negate the issue I seem to be having.

Looking forward to your response.

Kind regards.