Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Gather data and placing into another sheet

Gather data and placing into another sheet

Gather data and placing into another sheet


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

Set Start = Range(ActiveCell.Address)

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
Worksheets("Nav Export").Range("S" & ExportRow).Value = "MIDYEAR"
End If
ExportRow = ExportRow + 1
End If
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(1, -12).Select

ActiveCell.Offset(6, -3).Select

MsgBox ("Finished!")
MsgBox ("You must enter a Responsibility Center code to continue")
End If
End Sub

RE: Gather data and placing into another sheet

A couple of point:
1. You are in a wrong Forum. This is VB5-VB6, you want to be in forum707: VBA Visual Basic for Applications (Microsoft)
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.

RE: Gather data and placing into another sheet

Hi Andy,

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

Thank you!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close