Hi all,
I am looking to create separate workbooks based on the master workbook attached for each of the company's listed in the rate card tab.
So each new workbook would contain 3 tabs - Instructions, Cover Page & Rate Card, however, only columns A:H would appear in the Rate Card tab with Columns G:H being the two columns that related to the respective company. Columns A:F in the Rate Card will always be static in each file. (An example of how each Rate Card tab will look in each new workbook are shown in worksheets labelled Company A, B, & C)
This file is only a sample of the original. The original has 300 columns now and each company/region always has two data columns being G:H in the Rate card tab.
Can anybody help me tweak the code below (kindly provided by another Excel Forum member) to create these new workbooks with their respective data sets and at the same time tweak the destination folder of where each file is saved? I would like to be able to set the destination folder where the files will be saved by inputting the destination at will i.e. C:\Users\master\Documents\SAP or in 3 months time it could be C:\Users\master\Documents\Old SAP Cards, etc.
Example file:
Rate Cards.xlsm
Here is the current code:
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("Rate Card")
last = .Cells(5, .Columns.Count).End(xlToLeft).Column
End With
For j = 7 To last
wbk.Sheets(Array("Instructions", "Cover Page", "Rate Card")).Copy
Set wbknew = ActiveWorkbook
With wbknew
fname = .Sheets("Rate Card").Cells(2, j) & ".xlsx"
For i = last To 7 Step -1
If i <> j Then .Sheets("Rate Card").Columns(i).Delete
Next i
.SaveAs fname 'saving in the default folder
.Close False
End With
Next j
End Sub
Looking forward to your responses and have a great New Years Eve and 2014.
Bookmarks