+ Reply to Thread
Results 1 to 3 of 3

Formatting a worksheet of raw data tables with same format automatically

  1. #1
    Registered User
    Join Date
    08-07-2014
    Location
    London:England
    MS-Off Ver
    2010
    Posts
    7

    Formatting a worksheet of raw data tables with same format automatically

    Here is my problem,

    I have to format a lot of raw data into neat tables, problem is that although the format is the same for each table the tables vary in height.

    See the attachment, first worksheet is the format the raw data arrives in, second is the ideal format. When this data gets dropped in a few days I will have a raw data sheet containing 500 tables rather than 4 so if any of you clever people want a good challenge which would help me out.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Formatting a worksheet of raw data tables with same format automatically

    I sure would wonna give this a try, but have some questions.
    Looking at the example I see there is actually only 1 row of data which gets repeated several times.
    Is this just for the example or will the actual raw data also be like this?

    From the example I take it that it is just a matter of splitting up the data row into table per question.
    Is that what you intend?
    What is that you actually want to calculate in the tables?
    Will the raw data always be in the same format and in the same cells?
    How do you get the raw data? Do you import it from a file or otherwise?
    If from a file, can you upload a sample. Just some 10 rows would do.
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  3. #3
    Registered User
    Join Date
    08-07-2014
    Location
    London:England
    MS-Off Ver
    2010
    Posts
    7

    Re: Formatting a worksheet of raw data tables with same format automatically

    Hi,

    For anyone who is interested I use these 5 Subs running them in the order listed to get the desired effect. I'm sure there is a quicker way since I am fairly new to this.



    Sub Movetotal()


    With Worksheets("Main Batch_USA Test 1")

    Dim i As Integer
    Dim j As Integer

    For i = 1 To 10000
    For j = 1 To 4

    If .Cells(i, j).Value = "Total" Then
    'MsgBox ("Row: " + Str(i) + " Column: " + Str(j))
    .Cells(i + 1, j).Value = "Total"
    .Cells(i, j).Value = ""
    i = i + 1
    End If

    Next j
    Next i
    End With
    End Sub

    Sub Movebase()


    With Worksheets("Main Batch_USA Test 1")

    Dim i As Integer
    Dim j As Integer

    For i = 1 To 10000
    For j = 1 To 255

    If .Cells(i, j).Value = "Base" Then
    'MsgBox ("Row: " + Str(i) + " Column: " + Str(j))
    .Cells(i, j + 1).Value = "Base"
    .Cells(i, j).Value = ""
    i = i + 1
    End If

    Next j
    Next i
    End With
    End Sub


    Sub Colour_Blue()

    Application.ScreenUpdating = False
    Dim lngLstCol As Long, lngLstRow As Long

    lngLstRow = ActiveSheet.UsedRange.Rows.Count
    lngLstCol = ActiveSheet.UsedRange.Columns.Count

    For Each rngCell In Range("D2:D" & lngLstRow)
    If rngCell.Value > "" Then
    r = rngCell.Row
    c = rngCell.Column
    Range(Cells(r, c), Cells(r, lngLstCol)).Select
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent5
    .TintAndShade = -0.249977111117893
    .PatternTintAndShade = 0

    End With
    End If
    Next

    Application.ScreenUpdating = True

    End Sub


    Sub Colour_LightBlue()

    Application.ScreenUpdating = False
    Dim lngLstCol As Long, lngLstRow As Long

    lngLstRow = ActiveSheet.UsedRange.Rows.Count
    lngLstCol = ActiveSheet.UsedRange.Columns.Count

    For Each rngCell In Range("C2:C" & lngLstRow)
    If rngCell.Value > "" Then
    r = rngCell.Row
    c = rngCell.Column
    Range(Cells(r, c), Cells(r, lngLstCol)).Select
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent5
    .TintAndShade = 0.399975585192419
    .PatternTintAndShade = 0

    End With
    End If
    Next

    Application.ScreenUpdating = True

    End Sub

    Sub Colour_White()

    Application.ScreenUpdating = False
    Dim lngLstCol As Long, lngLstRow As Long

    lngLstRow = ActiveSheet.UsedRange.Rows.Count
    lngLstCol = ActiveSheet.UsedRange.Columns.Count

    For Each rngCell In Range("B2:B" & lngLstRow)
    If rngCell.Value > "" Then
    r = rngCell.Row
    c = rngCell.Column
    Range(Cells(r, c), Cells(r, lngLstCol)).Select
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
    .PatternTintAndShade = 0

    End With
    End If
    Next

    Application.ScreenUpdating = True

    End Sub

    Sub TheWall()

    Application.ScreenUpdating = False
    Dim lngLstCol As Long, lngLstRow As Long

    lngLstRow = ActiveSheet.UsedRange.Rows.Count
    lngLstCol = ActiveSheet.UsedRange.Columns.Count

    For Each rngCell In Range("B2:D" & lngLstRow)
    If rngCell.Value > "" Then
    r = rngCell.Row
    c = rngCell.Column
    Range(Cells(r, c), Cells(r, lngLstCol)).Select
    With Selection.Borders
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    End If
    Next

    Application.ScreenUpdating = True

    End Sub

+ 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. Replies: 0
    Last Post: 02-05-2014, 02:49 PM
  2. Copying pivot tables as data tables with formatting
    By wolis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-17-2012, 12:57 AM
  3. Updating pivot tables automatically when leaving worksheet. Specific occurances only
    By DMBeer41 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-01-2011, 11:51 AM
  4. Automatically create tables if other cells contain data
    By rrbest in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-27-2010, 01:27 PM
  5. Formatting an automatically generated worksheet with varying lines of data
    By msteckbeck in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-05-2008, 07:08 PM

Tags for this Thread

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