Help Oh Great Ones!

SAllen

New Member
Joined
Aug 28, 2002
Messages
11
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

voodoo

Board Regular
Joined
Apr 7, 2002
Messages
186
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
 

SAllen

New Member
Joined
Aug 28, 2002
Messages
11
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

Board Regular
Joined
Apr 7, 2002
Messages
186

ADVERTISEMENT

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

New Member
Joined
Aug 28, 2002
Messages
11
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

Board Regular
Joined
Apr 7, 2002
Messages
186

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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: -

Code:
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

New Member
Joined
Aug 28, 2002
Messages
11
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

New Member
Joined
Aug 28, 2002
Messages
11
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
 

Forum statistics

Threads
1,148,525
Messages
5,747,203
Members
424,068
Latest member
Salim khamis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top