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

 

[voodoo]

Try this

Sub CopyData()

Dim wb As Workbook
Dim ws As Worksheet
Dim ws2 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
Set wb = ActiveWorkbook
Set ws2 = wb.ActiveSheet

ws2.Range(ws2.Cells(1, 1), ws2.Cells(1, x)) = CellstoCopy

End Sub

 

[PaddyD]

looks suspiciously like a duplicate:

http://www.mrexcel.com/board/viewtopic.php?topic=20643&forum=2&1

 

[SAllen]

I'm sorry, I'm pretty knew to this forum. It's been about 5 years since I have done any VB with excel. this is a bit over my head. I'm told by the forum my code works, but I am unable to make it work on the spreadsheet I'm working in. I created the macro with the standard tools...macro...create macro from the menu bar. Is this incorrect? Is this different from the standare macro module?
Thanks much in advance!!!!

 

[voodoo]

It would be best to place you code in the code window for behind Sheet1 I suppose. But if you are using a module that is OK - either should be fine.

I had an immediate problem with the name of your macro "Copy", I could not use that word as a macro name because this word is a method name in VB.

Did you try with the change I posted above.

I ran this macro on my PC and it worked fine.

If it is not working you can try steping through the code. With the code window open press F8 and keep pressing F8 as it works through your code and check when and where it fails.

Your original code did not work for me because you did not reference the different workbooks, worksheets properly.

 

[SAllen]

Thanks Voodoo- It must be something in my original data sheet. I ran a the code on a dummy data sheet and it worked great! How can I get the date to run in columns instead of across the rows. Thanks Again so much!

 

[voodoo]

Probably a few ways to do it but here is one.

Sub CopyData()

Dim wb As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet

Dim CellstoCopy() As Variant
Dim x As Integer
Dim lngCount As Long

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
Set wb = ActiveWorkbook
Set ws2 = wb.ActiveSheet

For lngCount = LBound(CellstoCopy) To UBound(CellstoCopy)


ws2.Cells(lngCount + 1, 1) = CellstoCopy(lngCount)

Next

End Sub

 

[Mudface]

There are now 5 separate threads on this subject, including the original where I posted the code for you. Can you please not start any more and keep any problems to within this thread.

The code I gave you works fine when run from a standard module in the workbook containing the cells you want to copy. To create this press Alt-F11 to enter the VB editor in your 'source' workbook. Click on Insert- Module and copy and paste the code into that. Run the code either by pressing F5 within the VB editor or by clicking the 'Run Macro' button from the Excel command bar. The code creates a new workbook and enters the data into it on the first sheet.

Edit: To have the cells copied into a column, rather than across a row, change the original code to this: -

Sub CopyCells()

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(x, 1)) = WorksheetFunction.Transpose(CellstoCopy)

End Sub

This message was edited by Mudface on 2002-08-30 02:54

 

[SAllen]

Sorry Mudface and all who helped me. I wasn't quite sure how to respond to people who posted a message for me. I promise to do better!!!! Thank you all for all your timely help! You saved me hours of work per month!
SA

 

[SAllen]

Once again I need help tweeking this code. Is there a way to get the values of G13 to paste into Column A and the values of G32 to paste into Column B? The existing code pastes all values into Column A. Thanks in advance to all who have helped.

Sub CopyData()

Dim wb As Workbook
Dim ws As Worksheet
Dim ws2 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
Set wb = ActiveWorkbook
Set ws2 = wb.ActiveSheet

ws2.Range(ws2.Cells(1, 1), ws2.Cells(x, 1)) = WorksheetFunction.Transpose(CellstoCopy)

End Sub