Help Oh Great Ones!

[SAllen]

when I run the macro below the new workbook is created but no data shows?
Can anyone help?

Sub Copy()


Dim ws As Worksheet
Dim CellstoCopy() As Variant
Dim x As Integer

For Each ws In ThisWorkbook.Worksheets
ReDim Preserve CellstoCopy(x + 1)
CellstoCopy(x) = ws.[g13]
CellstoCopy(x + 1) = ws.[g32]
x = x + 2
Next ws

Workbooks.Add
Range(Cells(1, 1), Cells(1, x)) = CellstoCopy


End Sub

 

[Mudface]

This seems to work OK (no need to apologise, it just gets a bit confusing if your question gets split across a number of separate threads, as people aren't sure where you're up to ). No need to set references to an activeworkbook or worksheet with Workbooks.Add, the first worksheet of the new workbook will be active anyway: -

Sub CopyData()

Dim ws As Worksheet
Dim CellstoCopy() As Variant
Dim x As Integer

For Each ws In ThisWorkbook.Worksheets
    ReDim Preserve CellstoCopy(1 To 2, x + 1)
    CellstoCopy(1, x) = ws.[g13]
    CellstoCopy(2, x) = ws.[g32]
    x = x + 1
Next ws

Workbooks.Add
Range(Cells(1, 1), Cells(x, 2)) = WorksheetFunction.Transpose(CellstoCopy)

End Sub