Link to home
Create AccountLog in
Avatar of gvilbis
gvilbis

asked on

I have a macro which copy incorrectly the field from one cell to another, excel 2007

on one file i have sum function on it and its not copy correctly the content inside it to another file

the value in original file cell is like:
=SUM(FE89,,FS89,GG89,,GU89,HI89,HW89)

in the copy file cell i am getting like:
=SUM(#REF!,,#REF!,#REF!,,#REF!,#REF!,G3)

the code of copy in the macro is as follow:

 If wksDest.Range("IG" & rng.Row) <> "c" Then 'check if line is completed if yes move to next
               wksDest.Range(Cells(rng.Row, 1), Cells(rng.Row, 9)).Copy
               wksSource.Activate
'               lastRow = Range("A" & rng2.Rows.Count).End(xlUp).Row
               lastRow = Range("A" & Rows.Count).End(xlUp).Row
               Cells(lastRow + 1, 1).PasteSpecial xlPasteAll, Paste:=xlPasteCommens


what is wrong in the copy and paste command?

thanks in advance
Avatar of SiddharthRout
SiddharthRout
Flag of India image

1)

xlPasteCommens

should be

xlPasteComments

2)

Don't see anything wrong with the code. Can comment more once I test the file...

Sid
Also, you should be pasting formulas and not comments?

Sid
Try this

Replace this line

Cells(lastRow + 1, 1).PasteSpecial xlPasteAll, Paste:=xlPasteCommens

with

Cells(lastRow + 1, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Open in new window


Sid
Avatar of gvilbis
gvilbis

ASKER

i try the following line but still have the problem:

 Cells(lastRow + 1, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
              False, Transpose:=False
What error are you getting?

Sid
Avatar of gvilbis

ASKER

No error but still when i am looking in the cell its stil showing

=SUM(#REF!,,#REF!,#REF!,,#REF!,#REF!,G3)

Avatar of gvilbis

ASKER

is some one can advice?

Thanks
Can I see your file for a faster resolution?

Sid
Avatar of gvilbis

ASKER

Hi Sid

attach two files 1. reginac 2.MasterBook
reginac copy columns R & S (Total Shipped, OrderQTY-Shipped QTY from Masterbook (columns IH & II) and as you can see its not copy the formula correctly.
the password for openning MasterBook is:White1022
to see the code in reginac file is:1234
the copy command is in function "Update FromMaster" and "Partial UpdateFrom"

the line code in "Update FromMaster" is:
Cells(lastRow + 1, 1).PasteSpecial xlPasteFormulasAndNumberFormats, Operation:=xlNone, SkipBlanks:= _
              False, Transpose:=False

the line code in "PartialUpdate FromMaster" is:
Cells(rng.Row, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
              False, Transpose:=False

please advice
thanks
gvilbis

  Master-Book.xlsm reginac-cdfny.com.xlsm
I guess you are talking about

              wksDest.Range(Cells(rng.Row, 242), Cells(rng.Row, 285)).Copy
               wksSource.Activate
'               lastRow = Range("A" & rng2.Rows.Count).End(xlUp).Row
               Cells(lastRow + 1, 18).PasteSpecial xlPasteAll
               Cells(lastRow + 1, 18).PasteSpecial xlPasteComments

The error is because there are not enough rows towards the left of Column R in that sheet for example in Master Sheet

the formula in Col IF (Column 242) is

=SUM(FE2,,FS2,GG2,,GU2,HI2,HW2)

Now Column FE2 is Column Number 161 so the very first column is 81 columns toward the left of Column IH.

How ever when you paste in Column R (which is Col 18), you don't have more than 17 columns in the Left and hence Excel errors out :)

Hope this makes sense...

Sid
The error is because there are not enough rows towards the left of Column R in that sheet for example in Master Sheet

I meant Columns and not Rows....

Sid
Avatar of gvilbis

ASKER

and if i will copy by value it will work, right?
ASKER CERTIFIED SOLUTION
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account