range selection using VBA

[Andreas1]

I am novice in VBA and I need your help!!!!

In sheet1 I have a list of data (row/col length is variable each time)
(see below example)

<table style="width: 452px; height: 415px;" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:5449;width:112pt" width="149"> <col style="width:48pt" span="3" width="64"> <tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:112pt" height="17" width="149">Sales report</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> </tr> <tr style="height:25.5pt" height="34"> <td class="xl24" style="height:25.5pt;width:112pt" height="34" width="149">table produced</td><td class="xl25" style="width:48pt" width="64">Jun 10</td> <td class="xl25" style="width:48pt" width="64">Jul 10</td> <td class="xl25" style="width:48pt" width="64">Aug 10</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;width:112pt" height="17" width="149">Jun 10</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas</td> <td class="xl26">100</td> <td class="xl27">100</td> <td class="xl27">100</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas 1</td> <td class="xl26">25</td> <td class="xl27">12</td> <td class="xl27">56</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas 2</td> <td class="xl26">25</td> <td class="xl27">23</td> <td class="xl27">67</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas 3</td> <td class="xl26">25</td> <td class="xl27">34</td> <td class="xl27">78</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas4</td> <td class="xl26">25</td> <td class="xl27">45</td> <td class="xl27">1</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">[empty line]
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;width:112pt" height="17" width="149">Jul 10
Andreas
</td> <td>
100
</td> <td>
100
</td> <td>
100
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas 1</td> <td class="xl27">13</td> <td class="xl26">30</td> <td class="xl27">98</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas 2</td> <td class="xl27">24</td> <td class="xl26">20</td> <td class="xl27">87</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas 3</td> <td class="xl27">35</td> <td class="xl26">15
</td> <td class="xl27">76</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas4</td> <td class="xl27">46</td> <td class="xl26">35</td> <td class="xl27">65</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">[empty line]</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;width:112pt" height="17" width="149">Aug 10
Andreas
</td> <td>
100
</td> <td>
100
</td> <td>
100
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas 1</td> <td class="xl27">32</td> <td class="xl27">98</td> <td class="xl26">10</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas 2</td> <td class="xl27">54</td> <td class="xl27">74</td> <td class="xl26">10</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas 3</td> <td class="xl27">65</td> <td class="xl27">25</td> <td class="xl26">70</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Andreas4</td> <td class="xl27">76</td> <td class="xl27">43</td> <td class="xl26">10</td> </tr> </tbody></table>[empty line]

As you see in each set of data there is a empty line. What I want is to gather data in a new spreadsheet (coule be sheet2) so to have one table with data of the respective months (data with red fonts).

<table border="0" cellpadding="0" cellspacing="0"><tbody><tr height="17"><tr style="height:25.5pt" height="34"><td class="xl24" style="height:25.5pt;width:112pt" height="34" width="149">
</td><td class="xl25" style="width:48pt" width="64">
</td><td class="xl25" style="width:48pt" width="64">
</td><td class="xl25" style="width:48pt" width="64">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl25" style="height:12.75pt;width:112pt" height="17" width="149">table produced
</td><td>Jun 10
</td><td>Jul 10
</td><td>Aug 10
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Andreas</td><td class="xl26">100</td><td class="xl27">100</td><td class="xl27">100</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Andreas 1</td><td class="xl26">25</td><td class="xl27">30
</td><td class="xl27">10</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Andreas 2</td><td class="xl26">25</td><td class="xl27">20</td><td class="xl27">10</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Andreas 3</td><td class="xl26">25</td><td class="xl27">15</td><td class="xl27">70</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17">Andreas4</td><td class="xl26">25</td><td class="xl27">35</td><td class="xl27">10</td></tr></tr></tbody></table>
I want the macro to be dynamic as I may have more columns / rows but the idea is the same...

Looking forward hearing any ideas!!
Thank you in advance

 

[Andreas1]

hello wigi!

I tried this code but now I get a different error message

Run time error '1004'
Application-defined or object defined error

 

[wigi]

On what line of code does the error occur?
Is your sheet named Sheet2, as mentioned above?

 

[Andreas1]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> in my table the first row has a title and the is the table (this was not in my example).
if i delete this row then the macro is running works and in sheet2 data are moved

However, it seems that not the correct data are moved.

What I tried to show in my example is that:
from the first table I want to keep data from the first column (so from the table Jun10 I want to keep data from column Jun10)

in the same way from the 2^nd table (marked Jul10 in my example) I want to keep data from Jul10 column

Instead of the above when using this macro it seems that data from column A (only column A) are selected and moved to sheet2 (but I want to have jun10 data for Jun10, Jul10 for Jul10 e,t,c)

Does this make any sense?


(if it is easier can I send you an e-mail with my example?)

 

[wigi]

Yes, you can upload a file on a free filesharing site and provide the link here.

There are also possibilities to show your exact file layout here.

Without the exact layout I am not able to provide further help.

Based on your layout in the first post, my code works in my tests.

 

[Andreas1]

i have upload the file... please find the link

http://www.4shared.com/folder/wNib6G_m/_online.html

in sheet1 i have the actual example i need to work with. the highlighted red fond columns is what I need to have

in sheet3 I have collected the data in the way they should look at the end

Thank you very much for all your time!!! hope we can resolve this

 

[villy]

Unfortunately I have a limited access here at work so I cannot see your posted example I will check it when I reach home..

 

[Andreas1]

thank you villy!!

 

[wigi]

I also cannot download the file from the customer's end but will do so in a couple of hours.

 

[Andreas1]

ok wigi...let me know if you have a problem to download the file

 

[wigi]

The sample data your provided in the opening post were misleading. If I change the code based on your file, it works without issue:

Sub reformatTable()

    Dim lRow As Long, lColumn As Long, rngCell As Range, lNextRow As Long, sDay As String

    With Sheets("Sheet2")
        
        'clear this sheet
        .UsedRange.ClearContents
        
        'copy the titles
        Range("B2", Range("B2").End(xlToRight)).Copy .Range("B1")
        
        'copy the data in a cumulative way
        For lRow = 3 To Range("A" & .Rows.Count).End(xlUp).Row
            If Len(Range("B" & lRow).Text) Then
                'transfer the person to sheet 2
                Set rngCell = .Columns(1).Find(Range("A" & lRow).Text, , xlValues, xlWhole)
                If rngCell Is Nothing Then
                    lNextRow = .Range("A" & .Rows.Count).End(xlUp).Offset(1).Row
                Else
                    lNextRow = rngCell.Row
                End If
                .Range("A" & lNextRow).Value = Range("A" & lRow).Text
                .Cells(lNextRow, lColumn) = Range("A" & lRow).Offset(, lColumn - 1).Value
            Else
                sDay = Range("A" & lRow).Text
                lColumn = .Rows(1).Find(sDay, , xlValues, xlWhole).Column
            End If
        Next

    End With

End Sub