+ Reply to Thread
Results 1 to 6 of 6

Macro Code to keep Formulas in Split Workbooks

  1. #1
    Registered User
    Join Date
    02-03-2014
    Location
    Manchester, England
    MS-Off Ver
    Excel 2010
    Posts
    14

    Macro Code to keep Formulas in Split Workbooks

    Hi,

    I have a macro which breaks up a master spreadhseet based on the value of column A. When run the macro splits the master sheet into new files based on column A. The issue I have is that the master sheet contains formulas which when the macro is run the split files contain only the values.

    Please can you advise if there is any code which will ensure the formulas are kept, the cell range of the sheet is A to AF. Also, is it possible to have the output files expand the column widths to fit the data held in the file?

    For information, there is a code line which reads "ActiveSheet.Cells(2, 2).Value" which is the only reference I can see to possibly pasting the values of the master sheet.

    Regards

    Phil

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Macro Code to keep Formulas in Split Workbooks

    I would suggest that you post your code so that we can see where the problem might be. It sounds as though perhaps you need to change references from .value to .formula or to use copy and paste rather than assigning values. You can change columnwidths using .columnwidth, e.g. Range("A1").columnwidth = 15

  3. #3
    Registered User
    Join Date
    02-03-2014
    Location
    Manchester, England
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Macro Code to keep Formulas in Split Workbooks

    Hi Raguldy,

    Please see below the full code:

    Sub PagesByManager()
    Dim rRange As Range, rCell As Range
    Dim wSheet As Worksheet
    Dim wSheetStart As Worksheet
    Dim strText As String
    '///Start-----Send email code amendment-----------
    'ModuleSendEmail created for this code amendment
    Dim mailfrom As String
    Dim strfilename As String
    Dim linemanager As String

    mailfrom = "ENTER EMAIL ADDRESS "
    '-------------Send email code amendment-----End\\\

    Set wSheetStart = ActiveSheet
    wSheetStart.AutoFilterMode = False
    'Set a range variable to the correct item column
    Set rRange = Range("A1", Range("A" & Rows.Count).End(xlUp))

    'Delete any sheet called "UniqueList"
    'Turn off run time errors & delete alert
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("UniqueList").Delete

    'Add a sheet called "UniqueList"
    Worksheets.Add().Name = "UniqueList"

    'Filter the Set range so only a unique list is created
    With Worksheets("UniqueList")
    rRange.AdvancedFilter xlFilterCopy, , _
    Worksheets("UniqueList").Range("A1"), True

    'Set a range variable to the unique list, less the heading.
    Set rRange = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    End With

    On Error Resume Next
    With wSheetStart
    For Each rCell In rRange
    strText = rCell
    .Range("A1").AutoFilter 1, strText
    Worksheets(strText).Delete
    'Add a sheet named as content of rCell
    Worksheets.Add().Name = strText
    'Copy the visible filtered range _
    (default of Copy Method) and leave hidden rows
    .UsedRange.Copy Destination:=ActiveSheet.Range("A1")
    ActiveSheet.Cells.Columns.AutoFit
    '###Start: Save worksheet as individual workbook
    ActiveSheet.Move
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & strText & Format(".") & "xlsm", Password:="Pmixeho2", FileFormat:=52
    '///Start-----Send email code amendment-----------
    strfilename = ThisWorkbook.Path & "\" & strText & "." & "xlsm"
    Range("A1:AF1").Columns.AutoFit
    linemanager = ActiveSheet.Cells(2, 2).Value

    '-------------Send email code amendment-----End\\\
    ActiveWorkbook.Close False
    '///Start-----Send email code amendment-----------
    SendMail mailfrom, linemanager, "", "ENTER EMAIL SUBJECT - " & strText, "<p> <font face=""Futura Lt Bt"">ENETR EMAIL TEXT", strfilename 'Send the email to line manager
    Kill ThisWorkbook.Path & "\" & strText & "." & "xlsm" 'Remove this line if you do not want the .xlsm file to be deleted
    '-------------Send email code amendment-----End\\\
    '###End: Save worksheet as individual workbook
    Next rCell
    End With

    With wSheetStart
    .AutoFilterMode = False
    .Activate
    End With

    On Error GoTo 0
    Application.DisplayAlerts = True
    End Sub


    Many thaks for your help

    Phil

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Macro Code to keep Formulas in Split Workbooks

    Try changing:

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-03-2014
    Location
    Manchester, England
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Macro Code to keep Formulas in Split Workbooks

    Hi Ragulduy,

    This is great, just what I need. Please can you advise if there is further code to add the autofilter, expand the cells and freeze the panes at cell g2?

    Regards

    Phil

  6. #6
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Macro Code to keep Formulas in Split Workbooks

    something like:
    Please Login or Register  to view this content.
    would split your window and freeze the panes at G2.

    you can, in essence, use:
    Please Login or Register  to view this content.
    and the arguments would be the field to filter by followed by the value, for example:
    Please Login or Register  to view this content.
    I'm not sure what you mean by expand the cells.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to split workbook into multiple workbooks by client name
    By avalerion in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-22-2013, 05:36 AM
  2. Need a macro code to split worksheets into workbooks with contents breakup.
    By Adish in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-23-2013, 02:46 AM
  3. [SOLVED] Split Macro modification to Split into new Workbooks instead of sheets within one workbook
    By DLSmith in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-13-2012, 08:11 PM
  4. Macro to Split Workbook and Store New Workbooks in New Folders
    By TenaciousDave in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-09-2010, 12:33 PM
  5. [SOLVED] Macro to Split Workbook into separate Workbooks
    By jmurray in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-14-2009, 04:44 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1