+ Reply to Thread
Results 1 to 3 of 3

Macro to copy cell width to new sheets (ie copy original sheet format)

Hybrid View

  1. #1
    Registered User
    Join Date
    08-02-2012
    Location
    Cambridge, UK
    MS-Off Ver
    Office 2010 (Excel)
    Posts
    50

    Macro to copy cell width to new sheets (ie copy original sheet format)

    I have a big old spreadsheet that i have that is one big old bit of data with lots of cells.
    These cells then get split by country and then customer name, then this auto exports into lots of seperate spreadsheets that i can email to the relevant people. This is great, but when it exports, it doesnt keep the cell widths in the new sheet. Is there something i can add to my macro that will keep the format of the cell width in the new sheets it auto creates?

    this is my current code:
    Sub macro_1()
    Dim count, ws1, ws2
    Set ws1 = Sheets("Sales")
    count = 2
    Do Until ws1.Range("K" & count) = ""
        If Not ws1.Range("K" & count) = ws1.Range("K" & count - 1) _
            Or Not ws1.Range("T" & count) = ws1.Range("T" & count - 1) Then
            Set ws2 = Sheets.Add(After:=Sheets(Sheets.count))
            ws2.Name = Left(ws1.Range("T" & count), 15) & " - " & Left(ws1.Range("K" & count), 10)
            ws1.Rows(1).Copy ws2.Rows(1)
        End If
        ws1.Rows(count).Copy ws2.Rows(Range("A" & Rows.count).End(xlUp).Row + 1)
        count = count + 1
    Loop
    End Sub
    
    Sub Copy_Sheets_As_New_Workbook()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each ws In ActiveWorkbook.Worksheets
    ws.Copy
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & ws.Name
    ActiveWorkbook.Close
    Next ws
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub
    
    Sub UnionMacro()
    Call macro_1
    Call Copy_Sheets_As_New_Workbook
    End Sub
    Also, if someone knows how to make it so that the sheet doesnt have to be called sales, as it is just a one tab spreadsheet anyway, and it doesnt matter what it is called thereby replacing or removing the
    Set ws1 = Sheets("Sales")
    3rd line completely from the macro, i would be really grateful.

    thanks,

    (but the main thing is to get the cell width/ format to carry over)

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

    Re: Macro to copy cell width to new sheets (ie copy original sheet format)

    I'd imagine adding
    ws1.rows(1).copy
    ws2.rows(1).pastespecial xlpastecolumnwidths
    towards the end would do the trick. Also for the other bit would be something like:
    set ws1 = Sheets(activesheet.index+1)

  3. #3
    Registered User
    Join Date
    08-02-2012
    Location
    Cambridge, UK
    MS-Off Ver
    Office 2010 (Excel)
    Posts
    50

    Re: Macro to copy cell width to new sheets (ie copy original sheet format)

    SUPER DUPER! Thank you!

+ 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. Copy several columns width to another 2 sheets
    By ruliansyah in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-07-2013, 02:22 AM
  2. Macro to copy Dyanmic lists then copy sheet and clear the original data
    By twiggywales in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-30-2012, 01:06 PM
  3. [SOLVED] Copy formulas from one spreadsheet to another keeping the original sheet cell reference
    By Sheepdog in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-08-2012, 02:41 PM
  4. Replies: 0
    Last Post: 06-24-2012, 11:23 AM
  5. Replies: 0
    Last Post: 08-16-2011, 04:20 PM

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