Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 26th, 2009, 05:14 AM
Registered User
 
Join Date: Oct 2009
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Angry Copy and Paste the value

Hi,

anyone can help ? i got this issue regarding copy from formulas and paste the value in new worksheet. i manage to copy and paste over but my middle three rows only show 0. any idea why is it like that?

Code:
With Sheets("Forecast")
        .Range(.Range("BL5:BL92"), .Range("BP5:BP94").End(xlUp)).Copy
    End With
    Sheets("Template").[A65536].End(xlUp)(1).PasteSpecial Paste:=xlValues
 
Old October 26th, 2009, 08:39 PM
Authorized User
 
Join Date: Nov 2007
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts
Default

waka,

Try (very fast).


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Code:
 
Option Explicit
Option Base 1
Sub Test()
Dim LR As Long, a As Long, b As Long
Dim MyArray1, MyArray2
MyArray1 = Sheets("Forecast").Range("BL5:BL92").Value
a = UBound(MyArray1)
MyArray2 = Sheets("Forecast").Range("BP5:BP94").Value
b = UBound(MyArray2)
With Sheets("Template")
  LR = .Range("A" & Rows.Count).End(xlUp).Offset(1).Row
  .Range("A" & LR & ":A" & LR + a - 1).Value = MyArray1
  .Range("A" & LR + a & ":A" & LR + a + b - 1).Value = MyArray2
End With
End Sub

Then run the "Test" macro.


Have a great day,
Stan
__________________
stanleydgromjr

Windows 8.1, Excel 2007.
 
Old October 26th, 2009, 09:26 PM
Registered User
 
Join Date: Oct 2009
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Thanks, i appreciate your hardwork to help me. but can i remove those zeros instead having them copied over.

here is my sample code to remove blank space.

Code:
With Source
lastrow = .Cells(.Columns.Count, "A").End(xlUp).Row
For X = 1 To lastrow
If IsNumeric(.Cells(X, "A").Value) And .Cells(X, "A").Value <> "" Then
If RowsWithNumbers Is Nothing Then
Set RowsWithNumbers = .Cells(X, "A")
Else
Set RowsWithNumbers = Union(RowsWithNumbers, .Cells(X, "A"))
End If
End If
Next
If Not RowsWithNumbers Is Nothing Then
RowsWithNumbers.EntireRow.copy Destination.Range("A2")
End If
End With
 
Old October 26th, 2009, 11:07 PM
Authorized User
 
Join Date: Nov 2007
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts
Default

waka,

I am having trouble understanding your latest request.

You have "Private Messages" blocked or turned off.

It would help if I could see your workbook.

If your workbook does not contain sensitive information (if it does, then a sample with the sensative information removed), you can try to upload it to www.box.net and provide a link to your workbook.
__________________
stanleydgromjr

Windows 8.1, Excel 2007.
 
Old October 26th, 2009, 11:48 PM
Registered User
 
Join Date: Oct 2009
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by stanleydgromjr View Post
waka,

I am having trouble understanding your latest request.

You have "Private Messages" blocked or turned off.

It would help if I could see your workbook.

If your workbook does not contain sensitive information (if it does, then a sample with the sensative information removed), you can try to upload it to www.box.net and provide a link to your workbook.
My private message has been disable by administrator. now then i realase it. i create a sample file for you to test. i manage to remove the blanks but i can't remove zeros. and i can't upload to the fourm.
 
Old October 26th, 2009, 11:51 PM
Registered User
 
Join Date: Oct 2009
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by stanleydgromjr View Post
waka,

I am having trouble understanding your latest request.

You have "Private Messages" blocked or turned off.

It would help if I could see your workbook.

If your workbook does not contain sensitive information (if it does, then a sample with the sensative information removed), you can try to upload it to www.box.net and provide a link to your workbook.
My private message has been disable by administrator. now then i realse it. i create a sample file for you to test. i manage to remove the blanks but i can't remove zeros. and i can't upload to the fourm.
 
Old October 27th, 2009, 04:35 PM
Authorized User
 
Join Date: Nov 2007
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts
Default Re: Copy and Paste the value

waka,

See my Private Message to you.


Have a great day,
Stan
__________________
stanleydgromjr

Windows 8.1, Excel 2007.
 
Old October 28th, 2009, 10:08 PM
Registered User
 
Join Date: Oct 2009
Posts: 8
Thanks: 1
Thanked 0 Times in 0 Posts
Exclamation

Thanks stanley,

I'm very appreciate for everything. I encounted some error. i sent you a private message take a look at it.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Disable Copy Paste sridevi HTML Code Clinic 5 August 11th, 2007 05:09 AM
copy paste !!!!!!! dpkbahuguna Beginning VB 6 1 March 30th, 2007 10:06 AM
copy paste problem paul20091968 Excel VBA 4 November 20th, 2006 10:48 AM
Copy, Paste dpkbahuguna Beginning VB 6 1 October 26th, 2006 10:30 AM
Copy and append paste canmex Access 2 October 7th, 2003 01:25 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.