Excel help

  freddy-firecracker 19:21 26 Jul 2007
Locked

Hi
I am looking for help with a spreadsheet query, i have approx 45-50 seperate s/sheets with financial figures and dates on them. I need to draw information from about 10 cells(the same cells on each spreadsheet) and put this information onto one spreadsheet with simple column headings, and the information underneath them.

What is the simplest way of doing this?

FF

  VoG II 19:40 26 Jul 2007

What is the range on each sheet with the data that you wish to copy?

Does the summary sheet exist or must it be created?

Are there any sheets that you don't want to copy data from?

What column headings do you want?

The simplest way is a macro but the above information is needed first.

  freddy-firecracker 22:09 26 Jul 2007

VoG

The data is held between A1:P50 and the cells with the info are scattered amongst them (although it is in the same cell on each spreadsheet).The data is only on sheet 1 of each individual spreadsheet. The summary sheet does not exist yet but the column headings will just be named according to the info which is in the cells of each sheet being copied.

Hope this makes sense.

  VoG II 22:21 26 Jul 2007

10 cells does not equate to A1:P50.

You should easily be able to consolidate those sheets into a single workbook. Then run this:

Sub Transfer()
Dim Osh As Worksheet, sh As Worksheet, j As Integer, Headers
Headers = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J")
Application.ScreenUpdating = False
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Summary"
Set Osh = Sheets("Summary")
Osh.Range("A1:J1").Value = Headers
j = 1
For Each sh In Worksheets
If sh.Name <> Osh.Name Then
j = j + 1
sh.Range("A1:P50").Copy Destination:=Osh.Range("A" & j)
j = j + 50
End If
Next sh
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

  daba 23:44 26 Jul 2007

I think freddy actually has 45-50 workbooks, not worksheets.

He says "The data is only on sheet 1 of each individual spreadsheet". (Sounds to me like weekly accounts, but I may be wrong).

I believe he wants to collect the data from the same 10 cells on Sheet1 of all the other 50 workbooks into another workbook. Hopefully his filenaming will allow some iteration.

Is he going to meed oodles of PC memory to have 500 live external references ?

  freddy-firecracker 20:52 27 Jul 2007

It is indeed workbooks, apologies for the confusion. The cell range is A1:P50. The cells i need copied are A6,A7,B26,C26,F33,G34, etc, etc. The same cells in each workbook. Each workbook is saved under a unique name with the info only on sheet one. Although i said it was 10 cells to be copied it is more like 15-20. Is this still possible???

  VoG II 22:06 27 Jul 2007

It is indeed possible but much easier if you can create a single workbook containing all the sheets. We then don't even need to know the name of the sheets. Looping through 50 workbooks (whose names would need to be known) fills me with awe and as daba said could cause memory problems.

Right click a sheet tab, Move or copy sheet and follow the prompts.

  VoG II 19:39 28 Jul 2007

This modified code will work with a range of discrete cell references on sheets contained in the same workbook. You didn't provide a full list of cell addresses so I've made some up - modify the code to suit.

Sub Transfer()
Dim Osh As Worksheet, sh As Worksheet, j As Integer, Headers
Dim MyArray(1 To 10)
Headers = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J")
Application.ScreenUpdating = False
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Summary"
Set Osh = Sheets("Summary")
Osh.Range("A1:J1").Value = Headers
j = 1
For Each sh In Worksheets
If sh.Name <> Osh.Name Then
j = j + 1
With sh
MyArray(1) = .Range("A6").Value
MyArray(2) = .Range("A7").Value
MyArray(3) = .Range("B26").Value
MyArray(4) = .Range("C26").Value
MyArray(5) = .Range("F34").Value
MyArray(6) = .Range("G34").Value
MyArray(7) = .Range("H34").Value
MyArray(8) = .Range("I34").Value
MyArray(9) = .Range("K42").Value
MyArray(10) = .Range("L42").Value
End With
Osh.Range("A" & j & ":J" & j).Value = MyArray
End If
Next sh
Application.ScreenUpdating = True
End Sub

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Best of the Grad Shows 2017: University of the West of England (UWE)

Best value Mac: Which is the best £1249 Mac to buy

Les meilleures GoPro 2017