If those 3 macros work fine when executed individually, then you should
be
able to "gang them together" by just removing the first and second End
Sub
statements as well as the Sub Macro2() and Sub RemoveNegativeRows()
statements... this should allow the code to flow continuously from
beginning
to end.
Rick
Below is all the code for the three actions (yours included).
Sub Macro1()
'delete old data
Range("A1:I33").Select
'Range("I33").Activate
Range("a1").Activate
Selection.ClearContents
Selection.QueryTable.Delete
'enter new data
With
ActiveSheet.QueryTables.Add(Connection:=Array("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=C:\dblink.mdb;Mode=Share Deny Write;Extended
Properties=""", """;Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDB
atabase Password="""";Jet OLEDB:Engine Type=5;Jet
OLEDB
atab", "ase Locking Mode=0;Jet OLEDB:Global Partial Bulk
Ops=2;Jet
OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";"
_
, "jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDB
on't Copy Locale on Compact=False;Jet
OLEDB:Co",
"mpact Without Replica Repair=False;Jet OLEDB:SFP=False"),
Destination:=Range("a1"))
.CommandType = xlCmdTable
.CommandText = Array("FASBTableTemp")
.Name = "dblink_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "C:\dblink.mdb"
.Refresh BackgroundQuery:=True
End With
Range("a1").Select
Range("a1").Activate
' Macro2 this doesn't work properly
End Sub
Sub Macro2()
'add next year
Range("A1").Select
Range("f2").Select
Range("f3").Select
ActiveCell.FormulaR1C1 = "=+R[-1]C+1"
Range("G3").Select
ActiveCell.FormulaR1C1 = "=+RC[-2]-RC[-1]"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=+RC[-2]-RC[-1]"
Selection.AutoFill Destination:=Range("I2:I30"), Type:=xlFillDefault
Range("I2:I30").Select
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H30"), Type:=xlFillDefault
Range("H2:H30").Select
Range("G3").Select
ActiveCell.FormulaR1C1 = "=+R[-1]C[2]"
Range("G3").Select
Selection.AutoFill Destination:=Range("G3:G30"), Type:=xlFillDefault
Range("G3:G30").Select
Columns("G:j").Select
Selection.Style = "Currency"
Columns("G:j").EntireColumn.AutoFit
Columns("G:j").Select
Selection.Style = "Currency"
Columns("G:j").EntireColumn.AutoFit
'fill in years automatically
ActiveCell.Offset(2, -1).Range("A1").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A28"),
Type:=xlFillDefault
ActiveCell.Range("A1:A28").Select
'find first endyramt <0 & change recamt to correct number
Set MYRANGE = Range("i2:i" & Cells(Rows.Count, "i").End(xlUp).Row)
For Each c In MYRANGE
If c.Value <> "" And c.Value <= 0 Then
c.Offset(0, -1).Value = c.Offset(0, -2).Value ' Do what you
want
Exit Sub
End If
Next
'RemoveNegativeRows this doesn't work properly
End Sub
Sub RemoveNegativeRows()
Dim X As Long
Dim LastCell As Long
With Worksheets("FASBworksheet")
LastCell = .Cells(Rows.Count, "I").End(xlUp).Row
For X = LastCell To 1 Step -1
If .Cells(X, "I").Value >= 0 Then Exit For
.Cells(X, "I").EntireRow.Delete
Next
End With
End Sub
--
Burt
:
It is kind of hard to say what might be wrong without seeing the code
from
"macro2". It could be a timing problem or it could be a logic
construction
blocking the continuation of code or any number of other things. One
thing
you might try is constructing a fourth macro which does nothing be
call
each
of the other macros one at a time (although this could still be
subject
to a
timing problem). If this fourth macro idea does work, or is not an
acceptable solution, you will have to post your "macro2" code so we
can
look
at it.
Rick
The other issue is this--I want the spreadsheet to import data,
which
is what "macro1" does. Then I run "macro2", and then the one you
supplied. What I would like is to combine all the macros into one,
or try to "call" the second and then the third. When I place your
addition into "macro2" nothing happens. Am I missing a step, or
is there a way to stop/start? Appreciate your help. By the way, if
all
this could be done while the user remains in Access, and just
imports
the results of the spreadsheet, that would be really perfect!
Thanks