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
-~----------~----~----~----~------~----~------~--~---
