MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

total newbie question

  Asked By: Trupti    Date: Nov 27    Category: MS Office    Views: 892

I am a newbie to Excel. I'm muddling my way thru a project
and I have a question. I know nothing about the program other than
what I can intuitively figure out. I have about 8 wookbooks and I
would like to pull a name list (column A) and list of totals (column
H) and put them all into a new work book. Some of the workbooks have
more than one sheet. Is this possible?



7 Answers Found

Answer #1    Answered By: Rayner Fischer     Answered On: Nov 27

I often cut-n-paste lists from one WorkSheet to another. Select the
list and make a note of how many cells you're selecting; then go to
the destination Sheet and select the same number of cells and paste
(or it's equivalent Ctrl-V). With the "totals" in Column H, you might
need to use "Paste Special." You'll need to do this if the totals  are
referencing other data in which case the cells actually would contain
formulas like "=SUM(B1:G1)." By using Paste Special you'll have the
option to "Paste Values." ...Of course if this list  is in fact a
total of other values AND you want this list to dynamically add up
these values you'll have to be more careful.

Answer #2    Answered By: Adalie Fischer     Answered On: Nov 27

You shouldn't need to match the paste area to the shape of the copy area.
Just select the top left cell of the destination area, and Excel will paste
whatever you Ctrl-C'd.

Answer #3    Answered By: Ricardo Smith     Answered On: Nov 27

I think I understand your question, but let me summarize.
1) You have 8 workbooks.
2) Each workbook has one or more sheets.
3) You want to loop through each workbook and extract the info from
column A and H from each sheet  in the workbook and put  the info
into another workbook.

that sound right?

It's VERY do-able.
If that sounds accurate, let me know and I can help you with it.

Answer #4    Answered By: Nora Martin     Answered On: Nov 27

Are the 8 'source' workbook (filenames) known, or will they be different any
time you need to grab data from them?

Answer #5    Answered By: Kenny Moore     Answered On: Nov 27

Yes, the filenames are known, all different, but known. Thanks...

Answer #6    Answered By: Adalgisa Miller     Answered On: Nov 27

That's exactly what I want to do! Thanks!

Answer #7    Answered By: Girja Garg     Answered On: Nov 27

I'll give you enough to set you on the path.
(you'll learn better this way (grin))
First, there is a "collection" of worksheet names in every excel  file.
once you open a file, you can loop through all of the sheets in the file.
Another thing you should get used to is that the two terms:
ActiveWorkbook and ThisWorkbook do not necessarily mean the same thing.
ThisWorkbook is the name of the workbook that you're writing the code in.
ActiveWorkbook is the name of the workbook that currently has focus.
so.. say you write a macro/function/sub in a "master" or "reporting" workbook.
then ThisWorkbook.name is always equal to the name of the master file.
If, during the execution of the macro, you open another file, then while
that file is "active", then Activeworkbook.name is the name of THAT file.

given that criteria,then this code:
Option Explicit
Global MasterFileName, msg
Global RowLoc
Sub test()
MasterFileName = ThisWorkbook.Name
msg = ""
RowLoc = 1
Get_Data ("C:\temp\TestBook1.xls")
Get_Data ("C:\temp\TestBook2.xls")
MsgBox msg
End Sub
Function Get_Data(FullFileName)
Dim Shtno
Workbooks.Open Filename:=FullFileName
For Shtno = 1 To ActiveWorkbook.Sheets.Count
RowLoc = RowLoc + 1
Workbooks(MasterFileName).Sheets("Sheet1").Cells(RowLoc, 1) =
Workbooks(MasterFileName).Sheets("Sheet1").Cells(RowLoc, 2) =
msg = msg & ActiveWorkbook.Name & "." & Sheets(Shtno).Name & Chr(13)
Next Shtno
ActiveWorkbook.Close savechanges:=vbNo
End Function

will enter the file names and sheet  names in cells on Sheet1 of the "master"
now, it is a question  of copying the data from the cells on the files into the
"master" file...

give it a whirl and see what you come up with.

Let me know if you'd like more help.

Didn't find what you were looking for? Find more on total newbie question Or get search suggestion and latest updates.