Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old October 26th, 2009, 06: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
Reply With Quote
  #2 (permalink)  
Old October 26th, 2009, 09:39 PM
Authorized User
Points: 193, Level: 3
Points: 193, Level: 3 Points: 193, Level: 3 Points: 193, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2007
Location: North East Pennsylvania, USA.
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.
Reply With Quote
  #3 (permalink)  
Old October 26th, 2009, 10: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
Reply With Quote
  #4 (permalink)  
Old October 27th, 2009, 12:07 AM
Authorized User
Points: 193, Level: 3
Points: 193, Level: 3 Points: 193, Level: 3 Points: 193, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2007
Location: North East Pennsylvania, USA.
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.
Reply With Quote
  #5 (permalink)  
Old October 27th, 2009, 12:48 AM
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.
Reply With Quote
  #6 (permalink)  
Old October 27th, 2009, 12:51 AM
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.
Reply With Quote
  #7 (permalink)  
Old October 27th, 2009, 05:35 PM
Authorized User
Points: 193, Level: 3
Points: 193, Level: 3 Points: 193, Level: 3 Points: 193, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2007
Location: North East Pennsylvania, USA.
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.
Reply With Quote
  #8 (permalink)  
Old October 28th, 2009, 11: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.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Disable Copy Paste sridevi HTML Code Clinic 5 August 11th, 2007 06:09 AM
copy paste !!!!!!! dpkbahuguna Beginning VB 6 1 March 30th, 2007 11: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 11:30 AM
Copy and append paste canmex Access 2 October 7th, 2003 02:25 PM



All times are GMT -4. The time now is 09:13 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.