Copy and Paste the value

waka · October 26th, 2009, 05:14 AM

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

stanleydgromjr · October 26th, 2009, 08:39 PM

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

waka · October 26th, 2009, 09:26 PM

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

stanleydgromjr · October 26th, 2009, 11:07 PM

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.

waka · October 26th, 2009, 11:48 PM

Quote:

Originally Posted by stanleydgromjr

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.

waka · October 26th, 2009, 11:51 PM

Quote:

Originally Posted by stanleydgromjr

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.

stanleydgromjr · October 27th, 2009, 04:35 PM

waka,

See my Private Message to you.

Have a great day,
Stan

waka · October 28th, 2009, 10:08 PM

Thanks stanley,

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