Taking a value from a cell on each sheet and pasting as list in blank sheet

[matthewjapp]

Hi there,

Can anybody help with the following..

I have a workbook with about 230 sheets given to me by our finance team.

Sometimes there are more than 230 sheets, sometimes less...one thing is constant however, each of the sheets has a value in A1.

Is there an easy way of coding a macro to (assuming I keep 'Sheet 1' blank from A2:A230 as A1 has the header) to go through the other sheets and paste the value from A1 into the next spare line from A2 onwards until every sheet has been interrogated and I end up with a list (with the header in A1) of the 230 or so values in A1 across the other sheets in the workbook.

Thanks in advance to anyone who can help...

Matthew

 

[JackDanIce]

Hi,

I'm going to make the following assumptions:

- 'Sheet1' is the very left worksheet tab in the book and that the header value is already in cell A1

Try:

Sub EatinMePenguin ()
 
Dim i as Long
 
With Application
  .ScreenUpdating = False
  .Calculation = xlCalculationManual
End With
 
For i = 2 to Worksheets.Count
  Sheets("Sheet1").Range("A" & i) = Sheets(i).Range("A1")
Next i
 
With Application
  .ScreenUpdating = False
  .Calculation = xlCalculationAutomatic
End With
 
End Sub

 

[matthewjapp]

super, works a treat, thank you )

 

[JackDanIce]

One small typo, I made and forgot to update. In the last five lines of code should be, please change the code to:

With Application
  .ScreenUpdating = True
  .Calculation = xlCalculationAutomatic
End With
 
End Sub

Otherwise, glad it works!