• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code to extract data from multiple worksheets, into a single list

RobSA

RobSA

Member
Hi Folks,

I have a workbook with over 300 pages.

the pages however have data in a common cell which I would like to extract, into a single list.

thats the first challenge.

The second is to inquire whether this list would be dynamic, as it is possible that the data in the individual worksheets may change.
 
Ok so now I have worked on this excercise, my challenges are as follows:

The value that have been garnered from the various worksheets, does not display the decimal value and they are rounded off to reflect as 4.000 for example instead of 4.235.

My work so far

Code: 
Dim erow, MyValue As Long
For Each ws In Sheets

If ws.Range("M51").Value > 0 Then

myCounter = 1
ws.Select
ws.Range("M51").Select

MyValue = ws.Range("M51").Value

Worksheets("Sheet4").Select

erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ActiveSheet.Cells(erow, 1) = MyValue

'nextValue = MsgBox("Value found in " & ws.Name & Chr(10) & "Continue?", vbInformation + vbYesNo, ws.Name & "M51 =" & ws.Range("M51").Value)

Select Case nextValue
Case Is = vbYes
Case Is = vbNo
Exit Sub

End Select
End If

Next ws

If myCounter = 0 Then
MsgBox "Non of these sheets contains a " & Chr(10) & "value greater than 6 in celll M51", vbInformation, "Not Found"
End If


End Sub
 
RobSA
the decimal value and they are rounded off to reflect as 4.000 for example instead of 4.235.
Have You checked - what values are in cells in Your 'Activesheet'?
... if those cells are format as 'General', would it look 'better'?
 
HI Vletm,

I have looked at the values.

The values in the varies worksheets all are a result of a formula (Pythagoras) and the results of this formula have values that do not only end with .000 but with other variations.

It is these exact values I wish to appear in my "Sheet4".

It may be that the value being copied should be pasted using the "special paste" rather than just a copy. Additionally, the option of using round as a function may also influence this positively. These are only guesses at the moment

I hope this gives better perspective
 
RobSA
You try take cell's M51-value from many sheets.
Have You checked - what are those values?
... could You even show some of those? ... eg write to cell N51 =M51 and leave that cell without any formatting.
Your code do not 'copy' ... it looks take care about value.
Do not use Round until the end...
Could You upload a sample file here?
... then no need to guess - what is going on there?
 
I have checked the option of copying the number to another cell as suggested.

If I copy and paste the result is 0.000.
If I copy and paste values the result is 118.1158
If I copy paste using formula the result is also 0.000

The result I would like to see is 118.1158 rounded to the third decimal.

What is happening currently with the code is the number of 118.1158 is copying and pasting as 118.000.
 
RobSA
As I wrote ...
Your code do not 'copy' ... it looks take care about value.
Do not use Round until the end...
Could You upload a sample file here?
... then no need to guess - what is going on there?

If I copy and paste the result is 0.000.
If I copy and paste values the result is 118.1158
If I copy paste using formula the result is also 0.000
Those seems to work as those should work!
 
Test whether this gives results closer to what you want, if so we can add the other bits later.
The format of the cells displays to 3 decimal places but the information (value) in the cell is still full accuracy:
Code: 
For Each ws In Sheets
  If ws.Range("M51").Value > 0 Then
    myCounter = 1
    With Worksheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Offset(1)
      .Value = ws.Range("M51").Value
      .NumberFormat = "0.000"
    End With
  End If
Next ws
If myCounter = 0 Then MsgBox "Non of these sheets contains a " & Chr(10) & "value greater than 0 in celll M51", vbInformation, "Not Found"
 
p45cal,

Thanks very much. This works like a dream.

Without asking to much, what was I doing wrong that the number was not reading correctly?
 
I can only guess that the cells you were copying into had an inappropriate format applied to them.
 
You must log in or register to reply here.
Back
Top