More help needed on updating summary spreadsheet with data from files

Posted by Alexia on January 04, 2002 1:52 PM

I am writing a macro that will look for new files and extract certain information and place it in a summary workbook. Dank was extremely helpful with providing me with the code to do this.

I find myself stumped, however on how to write for Excel to pull in the values from the new files into the Summary.

For example, I want the values in H9, H15, A22, and H42 on each new file to go into columns A,B,C,& D, respectively. The row would be the next available one(each row constitutes a file).

I was provided with some direction by Scott and Tim but am quite frankly stumped.

Any help would be greatly appreciated.

P.S. I run the macro from within the summary.

Thanks

Posted by Scott on January 04, 2002 2:37 PM

Can you do something like this:

ActiveWindow.ActivateNext
ActiveWindow.ActivateNext
Range("H9").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ActivateNext
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Posted by alexia on January 04, 2002 4:40 PM

Thanks Scott, I think this will work brilliantly once I have worked out the kinks. I do have one followup question though:

How would I return to the same workbook to retrieve the second value (H15) to put in column B of summary spreadsheet. Keep in mind, I would not know the name of the spreadsheet from which I am extracting data because it would open based on its "new" status, not name. I would, however, always know the name of the summary, so I have no problem hard-wiring that.

Thanks :-)

: I am writing a macro that will look for new files and extract certain information and place it in a summary workbook. Dank was extremely helpful with providing me with the code to do this.

Posted by Scott on January 05, 2002 4:26 PM

I think the simple answer would be to run the same code, and just change the copy and paste location cells. Change "Range("H9").Select" to "Range("H15").Select", and then change "Range("A1").Select" to "Range("B1").Select".

Scott, I think this will work brilliantly once I have worked out the kinks. I do have one followup question though: : Can you do something like this