Gather data and placing into another sheet

[Marclem]

Hi

I have the following macro script which is running as expected but I would like to add two more values to the export sheet named NAV Export. I have attached the spreadsheet which you can see that at the moment it is only giving me data from FUND 1 GENADMIN showed under the NAV Export sheet after I added a row named TASK on top of each Fund (see row 34, 57, 80, 103..). Before it would give me data once I entered any dollar amount under each month per different Fund sections.

1 - I would like if possible to gather the data entered on any month for any particular fund, right now it is only looping thru the 1st Fund named GenAdmin
2 - I would like to be able to gather any TASK (see row 34, 57, 80, 103..) entered data to the proper fund into column K of the NAV Export sheet
3 - I would like to be able to gather data entered under row 53 (TOTAL INDIRECT EXPENSES (Recovery) that shows up on the other Funds as well (row 53, 76, 99, 122...) to be shown together with the other account number values (Worksheets("Nav Export").Range("E" & ExportRow).Value = AccountNumber)

I have researched online and tried a lot of different changes but I am not visual expert and request your help. Thank you

Sub Test_Macro()
'
' Test_Macro Macro
'

Dim Start As Range
Dim FundName
Dim ExportRow
'Dim ExportValue
Dim AccountNumber
Dim Month
Dim RC
Dim BudgetName
Dim Description
Dim Task

Sheets("MID YEAR REVIEW").Select

'Start = Range("B35")
RC = Range("C5").Value
Description = Range("C6").Value
BudgetName = Range("C7").Value
ExportRow = 1

If RC <> "" Then
'Clear our Export Tab
Worksheets("Nav Export").Range("A:Y").ClearContents

Range("B35").Select
Set Start = Range(ActiveCell.Address)
'Start.Select

While ActiveCell.Value <> ""
FundName = ActiveCell.Value
ActiveCell.Offset(1, 3).Select
For x = 1 To 15
AccountNumber = Range("A" & ActiveCell.Row).Value
For y = 1 To 12
If ActiveCell.Value <> "" And IsNumeric(ActiveCell) Then
Month = ActiveCell.Offset((x * -1), 0).Value
Worksheets("Nav Export").Range("A" & ExportRow).Value = Month
Worksheets("Nav Export").Range("C" & ExportRow).Value = RC & "16MID"
Worksheets("Nav Export").Range("D" & ExportRow).Value = "G/L Account"
Worksheets("Nav Export").Range("E" & ExportRow).Value = AccountNumber
Worksheets("Nav Export").Range("F" & ExportRow).Value = FundName
Worksheets("Nav Export").Range("I" & ExportRow).Value = RC
Worksheets("Nav Export").Range("Q" & ExportRow).Value = Description
Worksheets("Nav Export").Range("R" & ExportRow).Value = ActiveCell.Value
Worksheets("Nav Export").Range("R" & ExportRow).NumberFormat = "General"
Worksheets("Nav Export").Range("V" & ExportRow).Value = "G/L Account"
If BudgetName <> "" Then
Worksheets("Nav Export").Range("S" & ExportRow).Value = BudgetName
Else
Worksheets("Nav Export").Range("S" & ExportRow).Value = "MIDYEAR"
End If
ExportRow = ExportRow + 1
End If
ActiveCell.Offset(0, 1).Select
Next
ActiveCell.Offset(1, -12).Select
Next

ActiveCell.Offset(6, -3).Select

Wend
Range("C10").Select
MsgBox ("Finished!")
Else
MsgBox ("You must enter a Responsibility Center code to continue")
End If
End Sub

 

[Andrzejek]

A couple of point:
1. You are in a wrong Forum. This is VB5-VB6, you want to be in forum707
2. Almost all of your Dim statement declare Variant type. Is that want you really want?
3. Please use TGML tags to show your code.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Marclem]

Hi Andy,

Sorry, I have created under the correct forum the post.

Thank you!