excel 97 : automatically inserting a specific value for a range?

J

JMCN

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").Copy _
Destination:=Worksheets("SEIACHDisbursement").Range("C2:C100")

Worksheets("FTREGIFUNDSMOVE").Range("B2:B100").Copy _
Destination:=Worksheets("SEIACHDisbursement").Range("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
 
T

Tom Ogilvy

Worksheets("FTREGIFUNDSMOVE").Range("E2:E100").Copy _
Destination:=Worksheets("SEIACHDisbursement").Range("C2:C100")

Worksheets("FTREGIFUNDSMOVE").Range("B2:B100").Copy _
Destination:=Worksheets("SEIACHDisbursement").Range("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 said:
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").Copy _
Destination:=Worksheets("SEIACHDisbursement").Range("C2:C100")

Worksheets("FTREGIFUNDSMOVE").Range("B2:B100").Copy _
Destination:=Worksheets("SEIACHDisbursement").Range("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
Click to expand...
 
J

JMCN

Tom Ogilvy said:
Worksheets("FTREGIFUNDSMOVE").Range("E2:E100").Copy _
Destination:=Worksheets("SEIACHDisbursement").Range("C2:C100")

Worksheets("FTREGIFUNDSMOVE").Range("B2:B100").Copy _
Destination:=Worksheets("SEIACHDisbursement").Range("E2:E100")

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

thanks again for your help tom:)

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

Worksheets("SEIDisbursement").Range("E2:E200").Value =
"=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
 
J

JMCN

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code execution is correct but generates Error :The file you aretrying to open is in a different form 3
Object variable or with Block variable not set 9
Pasting a cell into bookmarked table from excel vba 1
Tried and true copy code lines fail me here 7
Copy row info but not in normal column sequence 6
MACRO Effeciency 3
Code copies between key word is in reverse order 5
Cell values to an array, then array transpose to another workbook 8

Top