Copying Cells From A Workbook 1

[robcarr]

Dear All,

What I would like to do is check cell C2 in each worksheet in the workbook and copy the name of the worksheet and the value of cell C2 into a different workbook, data once gathered needs to be in a column like

worksheet Name Quantity
sheet1 4
sheet2 6


And so on until all the sheets in the workbook have been checked, value in C2 is a formula "=counta(a7:a4000), I need to get the formula result from each sheet, Could someone help with this or suggest the best way to do this, I can create a link to get the informatio, but i have to re-new the links daily as the file with the data in C2 is a daily file and I need to get the total daily.

Hope someone can help on this, I would be really grateful.

Thanks in advance

 

[BrainB]

Hope this helps. Copy and paste to a module.

'---------------------------------------
'- macro to transfer worksheet values
'- to a separate workbook
'- change the Frombook name etc. in the code below
'- run this macro from the summary sheet
'-----------------------------------------

Sub TRANSFER_VALUES()
    Dim ToSheet As Worksheet
    Dim FromBook As String
    Dim ToRow As Long
    '-----------------------
    FromBook = "Book1.xls"
    Set ToSheet = ActiveSheet
    ToRow = 2
    '- loop through worksheets
    For Each ws In Workbooks(FromBook).Worksheets
        ToSheet.Cells(ToRow, 1).Value = ws.Name
        ToSheet.Cells(ToRow, 2).Value = ws.Range("C2").Value
        ToRow = ToRow + 1
    Next
End Sub
'----------------------------------------------------

Regards
BrianB
** Let us know if you get something that works !
================================

 

[robcarr]

thanks for doing that worked a treat, only a few minor adjustments, what I would like to do is add a formula to the for each ws, what I want to do, is put the formula in the cell that it is being copyied before copying the data, as I have been asked to back date the data for 4 months, and only 1 month ago I added the formula, the formula for cell C2 is =counta(A7:a4000), I have played around with it but can not get it to add the formula in each sheet before copying the data to the new sheet.

any help greatly appreciated again.

Thanks