hello,

okay, i have written a macro that will add new worksheets within the
workbook and then copy and paste data from worksheet1 to worksheet2.
is it possible to insert data in a range("A1") what will remain a
constant? for example, all i care about copying ranges C and E from
sheet1("FTREGIFUNDSMOVE") and pasting them to
sheet2("SEIACHDisbursement") in the specified ranges. This works
great. However, in range A, it needs to be 43700410 and range D will
be 220. Is there a way that I can write this information by a macro
instead of going to the worksheet and copy and paste it myself by
dragging arrow down? any suggestions would be appreciated or if i
just need to start over again.

thanks jung

here is the macro(s).
Sub FundsMovementSEIDriver()

'This macro is for the SEI ACH Disbursements for the FundsMovementSEI
Driver
Application.ScreenUpdating = False


ActiveSheet.Name = "SEIACHDisbursement"
Range("A1").Value = "FromAcct" 'will always be 47300410 -how can i
copy and paste to this worksheet?
Range("B1").Value = "FromIncome" 'blank
Range("C1").Value = "FromPrincipal"
Range("D1").Value = "DisbursementCode" 'will always be 220 for
disbursements
Range("E1").Value = "DisbursementExplanation1" '="INT" & [DUEDATE] &
"371"
Range("F1").Value = "DisbursementExplanation2" 'blank
Range("G1").Value = "DisbursementExplanation3" 'blank
Range("H1").Value = "DisbursementExplanation4" 'blank
Range("I1").Value = "DisbursementExplanation5" 'blank
Range("J1").Value = "Taxid" 'specific to the database
Range("K1").Value = "ToENeeded" 'blank
Range("L1").Value = "CUSIP" 'blank


'copy and paste specific cells from ftregi_funds_move worksheet
'SEIACHDisbursements have only the subtotal of each Due Date
'Find Range("A1") = Subtotal and paste to FromPrincipal column

Worksheets("FTREGIFUNDSMOVE").Range("E2:E100").Cop y _
Destination:=Worksheets("SEIACHDisbursement").Rang e("C2:C100")

Worksheets("FTREGIFUNDSMOVE").Range("B2:B100").Cop y _
Destination:=Worksheets("SEIACHDisbursement").Rang e("E2:E100")

'Fill in Range("E1") as (="INT" & [DUEDATE] & "371")

Application.ScreenUpdating = True

End Sub


Sub AddNewWorksheets()

Worksheets.Add
Sheets.Add.Name = "SEITransfer"
Sheets.Add.Name = "SEIWire"
Sheets.Add.Name = "SEIACHReceipt"
Sheets.Add.Name = "SEIACHDisbursement"

'Deletes other worksheets
Application.DisplayAlerts = False
Sheets("Sheet1").Delete
Application.DisplayAlerts = True

'Application.DisplayAlerts = False
'Sheets("Sheet6").Delete
'Application.DisplayAlerts = True

End Sub

Sub DeleteRows()

Application.ScreenUpdating = False

Dim MaxRow As Long
Dim i As Long

MaxRow = Range("D65536").End(xlUp).Row

For i = MaxRow To 1 Step -1

valcel = Range("D" & i).Value

If valcel < " SUTOTAL" And valcel < "0" Then
Range("D" & i).EntireRow.Delete
End If
Next 'i

Application.StatusBar = False
Application.ScreenUpdating = True

End Sub

Worksheets("FTREGIFUNDSMOVE").Range("E2:E100").Cop y _
Destination:=Worksheets("SEIACHDisbursement").Rang e("C2:C100")

Worksheets("FTREGIFUNDSMOVE").Range("B2:B100").Cop y _
Destination:=Worksheets("SEIACHDisbursement").Rang e("E2:E100")

'Fill in Range("E1") as (="INT" & [DUEDATE] & "371")
Worksheets("SEIACHDisbursement").Range("A2:A100"). Value = _
"'43700410"
Worksheets("SEIACHDisbursement").Range("D2:D100"). Value = _
220

--
Regards,
Tom Ogilvy


JMCN wrote in message
om...
hello,

okay, i have written a macro that will add new worksheets within the
workbook and then copy and paste data from worksheet1 to worksheet2.
is it possible to insert data in a range("A1") what will remain a
constant? for example, all i care about copying ranges C and E from
sheet1("FTREGIFUNDSMOVE") and pasting them to
sheet2("SEIACHDisbursement") in the specified ranges. This works
great. However, in range A, it needs to be 43700410 and range D will
be 220. Is there a way that I can write this information by a macro
instead of going to the worksheet and copy and paste it myself by
dragging arrow down? any suggestions would be appreciated or if i
just need to start over again.

thanks jung

here is the macro(s).
Sub FundsMovementSEIDriver()

'This macro is for the SEI ACH Disbursements for the FundsMovementSEI
Driver
Application.ScreenUpdating = False


ActiveSheet.Name = "SEIACHDisbursement"
Range("A1").Value = "FromAcct" 'will always be 47300410 -how can i
copy and paste to this worksheet?
Range("B1").Value = "FromIncome" 'blank
Range("C1").Value = "FromPrincipal"
Range("D1").Value = "DisbursementCode" 'will always be 220 for
disbursements
Range("E1").Value = "DisbursementExplanation1" '="INT" & [DUEDATE] &
"371"
Range("F1").Value = "DisbursementExplanation2" 'blank
Range("G1").Value = "DisbursementExplanation3" 'blank
Range("H1").Value = "DisbursementExplanation4" 'blank
Range("I1").Value = "DisbursementExplanation5" 'blank
Range("J1").Value = "Taxid" 'specific to the database
Range("K1").Value = "ToENeeded" 'blank
Range("L1").Value = "CUSIP" 'blank


'copy and paste specific cells from ftregi_funds_move worksheet
'SEIACHDisbursements have only the subtotal of each Due Date
'Find Range("A1") = Subtotal and paste to FromPrincipal column

Worksheets("FTREGIFUNDSMOVE").Range("E2:E100").Cop y _
Destination:=Worksheets("SEIACHDisbursement").Rang e("C2:C100")

Worksheets("FTREGIFUNDSMOVE").Range("B2:B100").Cop y _
Destination:=Worksheets("SEIACHDisbursement").Rang e("E2:E100")

'Fill in Range("E1") as (="INT" & [DUEDATE] & "371")

Application.ScreenUpdating = True

End Sub


Sub AddNewWorksheets()

Worksheets.Add
Sheets.Add.Name = "SEITransfer"
Sheets.Add.Name = "SEIWire"
Sheets.Add.Name = "SEIACHReceipt"
Sheets.Add.Name = "SEIACHDisbursement"

'Deletes other worksheets
Application.DisplayAlerts = False
Sheets("Sheet1").Delete
Application.DisplayAlerts = True

'Application.DisplayAlerts = False
'Sheets("Sheet6").Delete
'Application.DisplayAlerts = True

End Sub

Sub DeleteRows()

Application.ScreenUpdating = False

Dim MaxRow As Long
Dim i As Long

MaxRow = Range("D65536").End(xlUp).Row

For i = MaxRow To 1 Step -1

valcel = Range("D" & i).Value

If valcel < " SUTOTAL" And valcel < "0" Then
Range("D" & i).EntireRow.Delete
End If
Next 'i

Application.StatusBar = False
Application.ScreenUpdating = True

End Sub

"Tom Ogilvy" wrote in message ...
Worksheets("FTREGIFUNDSMOVE").Range("E2:E100").Cop y _
Destination:=Worksheets("SEIACHDisbursement").Rang e("C2:C100")

Worksheets("FTREGIFUNDSMOVE").Range("B2:B100").Cop y _
Destination:=Worksheets("SEIACHDisbursement").Rang e("E2:E100")

'Fill in Range("E1") as (="INT" & [DUEDATE] & "371")
Worksheets("SEIACHDisbursement").Range("A2:A100"). Value = _
"'43700410"
Worksheets("SEIACHDisbursement").Range("D2:D100"). Value = _
220

--
Regards,
Tom Ogilvy


thanks again for your help tom:)

though i have one more question. i experimented with the following
line:

Worksheets("SEIDisbursement").Range("E2:E200").Val ue =
"=DENVERFUNDSMOVE!B1 & ' ' & INT & ' ' & 371"

but it does not work when i add the "INT and 371" data with the
"=DENVERFUNDSMOVE!B1 " ms excel tells me that the subscript is out of
range - runtime error 9.

is there a possibility to combine it all for the value?

thanks jung

i ended up using ms access instead of writing all of trying to write
all of the excel macros. thank you all of your help!!!
jung