Take a look at the attached workbook. I believe it does what you want. Click the button and a tab will be created for each report and its contents copied to it. If you have any questions let me know. Regards, Tom
On Thu, Dec 18, 2008 at 4:53 PM, guggd2868 <guggd2...@hotmail.com> wrote:
>
> Hi all
>
> I have searched online extensively for help on this, and found some
> solutions but not enough.
>
> What I ultimately need to do is take one excel spread sheet that has
> 12 tables arranged vertically. The tables are split up by headers;
> one table ends when the next table's header is. I want to take each
> table and put it on a seperate tab in a different excel file. Here is
> the hard part: the tables change size each day.
>
> My approach is to make a button activated macro that takes the tables
> and puts them into the tabs. I start by dim variables for the name of
> each table header, which is the text in column A for each table. Then
> I make those variables equal to the text.
>
> Next I've dim 3 variables as objects. The first will be the current
> table's header cell. The second will be the next header table's
> header cell. Then I want the third to be the bottom right cell in the
> current tables header. Then I figure I'll just select the region from
> variable 1 to 3, copy, and paste to tab in new sheet.
>
> Then I'll just have that repeat for each table.
>
> I don't seem to be defining my 3 other variable right. Here is my
> code. If anyone has any tips or can tell me what I'm doing wrong, I'd
> surely appreciate it.
>
> Private Sub CommandButton1_Click()
>
> Dim MyCom As String
> Dim EA As String
> Dim USDA As String
> Dim GovtK As String
> Dim ThreeARM As String
> Dim FARM As String
> Dim SARM As String
> Dim TenARM As String
> Dim ThreeARMIO As String
> Dim FARMIO As String
> Dim SARMIO As String
> Dim TenARMIO As String
> Dim Current As Object
> Dim After As Object
> Dim Foot As Object
>
>
> MyCom = "Conf 30 FNMA MyCommunity 97 Plus"
> EA = "Conf 30 Exp Appr'l Level 1"
> USDA = "Conf 30 Rural Housing"
> GovtK = "Gov 30 (203k and Indian Housing)"
> ThreeARM = "Conf ARM 3/1 - 1y LIB"
> FARM = "Conf ARM 5/1 w/5-2-5 Caps - 1y LIB"
> SARM = "Conf ARM 7/1 - 1y LIB"
> TenARM = "Conf ARM 10/1 - 1y LIB"
> ThreeARMIO = "Conf ARM 3/1 w/10y IO - 1y LIB"
> FARMIO = "Conf ARM 5/1 w/5/2/5 Caps w/10y IO - 1y LIB"
> SARMIO = "Conf ARM 7/1 w/10y IO - 1y LIB"
> TenARMIO = "Conf ARM 10/1 Interest Only - 1yLIB"
>
>
> Windows("Inv_Countrywide_Buffer.xls").Activate
> With Worksheets("Inv_Countrywide_Buffer").Range("a1:a300")
> Set Current = Cells.Find(EA, , , , xlByRows, xlNext)
> Set After = Cells.Find(MyCom, , , , xlByRows, xlNext)
> Range("After").Cells.Activate
> Set Foot = Range(ActiveCell.Offset(-1, 4))
> Range("Current:Foot").Select
> Selection.Copy
> Windows("Inv_Countrywide_Input").Activate
> Worksheets("Expanded Approval").Activate
> Cells(1).Activate
> Paste
> End With
>
> End Sub
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~----------~----~----~----~------~----~------~--~---
SplitTables.xls
Description: MS-Excel spreadsheet
