-
May 2nd, 2012, 11:00 AM
#1
Thread Starter
New Member
-
May 2nd, 2012, 11:31 AM
#2
Re: New to VBA facing this error
Welcome to VBForums
Thread moved from the 'VB6' forum to the 'Office Development/VBA' forum (while VBA and VB6 have some similarities, they are not the same thing)
-
May 2nd, 2012, 12:15 PM
#3
Re: New to VBA facing this error
Welcome to the Forums!
I think the problem is you have both operations a bit mixed, try this: First do everything related to COPY, after that do everything related to PASTE. Example running it only once, you need to adjust this to run inside your While loop:
Code:
Public Sub test()
Dim cursheet As Worksheet, newsheet As Worksheet, oldBook As Workbook, newbook As Workbook
'COPY
Set oldBook = Application.ActiveWorkbook
Set cursheet = oldBook.Worksheets(1)
cursheet.Cells.Select
Application.Selection.Copy
'PASTE
Set newbook = Application.Workbooks.Add
Set newsheet = newbook.Worksheets.Add(after:=newbook.Worksheets(newbook.Worksheets.Count))
newsheet.Paste
End Sub
-
May 2nd, 2012, 12:28 PM
#4
Thread Starter
New Member
Re: New to VBA facing this error
When running the 'test', still getting the same error at the same point: cursheet.Cells.Select
-
May 2nd, 2012, 12:45 PM
#5
Re: New to VBA facing this error
Weird, that test() Macro works fine for me. I'm using Office 2003, what version are you using?
-
May 2nd, 2012, 12:47 PM
#6
Thread Starter
New Member
Re: New to VBA facing this error
I have office 2007 and 2010. The problem may be related to the later versions of office. I sent the macro to my son to run it and like you, he also did not have any error.
-
May 2nd, 2012, 01:10 PM
#7
Thread Starter
New Member
Re: New to VBA facing this error
Originally Posted by akaVB
I have office 2007 and 2010. The problem may be related to the later versions of office. I sent the macro to my son to run it and like you, he also did not have any error.
I had another person try this on his computer running Office 2007. He DID NOT have this problem. WEIRD!!
Need VBA Guru to step in please....
-
May 2nd, 2012, 01:16 PM
#8
Re: [Excel 2007] - New to VBA facing this error
Try changing this bit:
Code:
cursheet.Cells.Select
Application.Selection.Copy
..to this:
Code:
cursheet.Cells.Copy
-
May 2nd, 2012, 01:27 PM
#9
Thread Starter
New Member
Re: [Excel 2007] - New to VBA facing this error
Originally Posted by si_the_geek
Try changing this bit:
Code:
cursheet.Cells.Select
Application.Selection.Copy
..to this:
Code:
cursheet.Cells.Copy
Thank you. It worked. Really appreciate your help.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|