+ Reply to Thread
Results 1 to 2 of 2

how to split the same sheet for different companies

Hybrid View

  1. #1
    Registered User
    Join Date
    11-28-2016
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    20

    how to split the same sheet for different companies

    Hi, I have one excel recording every company's sales and shipments.
    Some of the prices are linked to the data in "LME" sheet supposingly but I only copied the numbers into the file I attached here so no formula showing in this file.

    I need to send this excel to each customer with their own records.
    I tried to create a pivot but there are merged cells so didn't work well.

    Is there a way to copy all rows where a certain customer is showing, together with all formulas and formats and the whole sheet of "LME" to a new excel, saved at PC's "documents" folder, naming the file as "company name - Shipping Record", say, "XYZ - Shipping Record"?

    Now what I am doing is to copy all data to a new excel, delete all other customers and save it to "documents" folder manually.
    This is quite troublesome cos there are more than a thousand rows in my original file.

    I am wondering if VBA can do it easily?
    Thanks a lot!

    Daniel
    Attached Files Attached Files

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,877

    Re: how to split the same sheet for different companies

    Try this macro. Replace "Your Username" (in red) with your computer username.
    Sub CreateShippingRecords()
        Application.ScreenUpdating = False
        Dim LastRow As Long, Rng As Range, RngList As Object, srcWS As Worksheet, srcWB As Workbook, item As Variant
        Set srcWB = ThisWorkbook
        Set srcWS = ThisWorkbook.Sheets("record")
        LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Set RngList = CreateObject("Scripting.Dictionary")
        For Each Rng In srcWS.Range("B2", srcWS.Range("B" & srcWS.Rows.Count).End(xlUp))
            If Not RngList.Exists(Rng.Value) Then
                RngList.Add Rng.Value, Nothing
            End If
        Next
        For Each item In RngList
            With srcWS.Range("A1").CurrentRegion
                .AutoFilter 2, item
                .SpecialCells(xlCellTypeVisible).Copy
                Workbooks.Add
                Sheets("Sheet1").Name = "record"
                Sheets("record").PasteSpecial
                .AutoFilter
                srcWB.Sheets("LME").Copy after:=Sheets(1)
                ActiveWorkbook.SaveAs Filename:="C:\Users\Your Username\Documents\" & item & " - Shipping Record.xlsx", FileFormat:=51
                ActiveWorkbook.Close False
            End With
        Next item
        Application.ScreenUpdating = True
    End Sub
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

+ 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. [SOLVED] Possible to calculate one log sheet for two different companies
    By Roampie in forum Excel General
    Replies: 3
    Last Post: 03-19-2018, 07:08 AM
  2. [SOLVED] Companies House DISSOLVED Companies.
    By MacStokes in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-24-2017, 02:10 PM
  3. Companies House API - search for companies, populate spreadsheet.
    By damnation in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-19-2016, 10:52 AM
  4. Replies: 14
    Last Post: 07-28-2013, 10:44 AM
  5. Excel macro split data from a:a colu from sheet 1,2 into one wb with two sheet
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-21-2013, 04:26 AM
  6. Split Data Unique Value From 2 Sheet of A column and Move to 2 Sheet with Splited Values
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2013, 04:21 AM
  7. Excel macro split data from a:a colu from sheet 1,2 into one wb with two sheet
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2013, 02: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