Copying Data in Different WS's

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am looking to augment information in one worksheet with data from another WS. The information in the source worksheet is column oriented and the target cells are row oriented. Is there a way to automate this process. There are several thousand cell involved. Thanks!
 
Hi
as you don't provide much info about the structure of your data one
simple suggestion is:
- Start the macro recorder
- copy the source range
- select the other worksheet and the target range
- goto 'Edit - Paste Special'
- choose 'Transpose'
- stop the macro recording

You may use this macro for further copy/paste procedures
 
Thanks but I tried as best I could to do as you suggested. What I'm observing is the target worksheet after the paste special now contains data from the source worksheet; however, the source data which were formulas holding cell references now has the source cell locations substituted in the formulus. Let me give you more information about the structure of my data

The source data contains a simple formula, ='CF as Presented'!C19+'Pinedale Crest CF as Presented'!C19, and as I mentioned the source data is column oriented. What I'd like to have is this same formula, or the source worksheet referenced cell, ='Pinedale Combined CF as Present'!C19, which I am copying to appear in a different but currently existing worksheet; however, the cells need to appear in row orientation as opposed to a column orientation. Hope this helps and again thanks so much for your help!!!!
 
Hi
try also choosing 'Values' during the insertion. BBut this will only
copy the values!
For a formula approach (which is also possible) you may provide the
following info:
1. Some example rows (plain text please) of your current sheet (4-6
rows are sufficient)
2. An example how you would like to have it in a row oriented fashion
 
Correct--values will not achieve desired results without reference back to cell address including worksheet name

In cell c19 of the worksheet 'Pinedale Combined CF as Present' is, ='CF as Presented'C19+'Pinedale Crest CF as Presented'!C19, in cell c20 of the same worksheet is, ='CF as Presented'!C20+'Pinedale Crest CF as Presented'!C20, in cell c21 of the same worksheet is, ='CF as Presented'!C21+'Pinedale Crest CF as Presented'!C21.

I would like to copy either these exact formulas or the source worksheet and cell reference (either of which will achieve the desire result) to cells AK64, AL64, and AM64 of the target worksheet

Hope this is a big enought sample of the many thousands of cells needing to be copied. Thanks so much trying to help!!!

----- Frank Kabel wrote: ----

H
try also choosing 'Values' during the insertion. BBut this will onl
copy the values
For a formula approach (which is also possible) you may provide th
following info
1. Some example rows (plain text please) of your current sheet (4-
rows are sufficient
2. An example how you would like to have it in a row oriented fashio



-
Regard
Frank Kabe
Frankfurt, German

frankll wrote
 
Hi
try the following formula in AK64
=OFFSET('Pinedale Combined CF as
Present'!$C$19,COLUMN()-37+(ROW()-64)*4,0)
copy this for cell AL64, AM64
and copy down: Assumption C22,C23,C24 will go to the next row
(AK65,AL65,AM65). If this is not correct you may change the above
formula accordingly (or post back, what should happen with the other
data)
 
Thanks Frank; however, I don't think I've communicated clearly. Cell c19 would got to cell AK64, cell c20 would go to AL64, and cell c21 and following would go to cell AM64 with source and target in different worksheets. Thanks again! Hope this helps

I am not an EXCEL expert by any means and have never used the offset feature; however, it appeared to change the data in the 'Pinedale Combined CF as Present' worksheet. What I am trying to do is move column information found in C19, C20, C21 ... from 'Pinedale Combined CF as Present' into row information in another worksheet in cells AK64, AL64, AM64 ...

Please don't give up on me! <:-) Thanks!!!!!!!!

----- Frank Kabel wrote: ----

H
try the following formula in AK6
=OFFSET('Pinedale Combined CF a
Present'!$C$19,COLUMN()-37+(ROW()-64)*4,0
copy this for cell AL64, AM6
and copy down: Assumption C22,C23,C24 will go to the next ro
(AK65,AL65,AM65). If this is not correct you may change the abov
formula accordingly (or post back, what should happen with the othe
data

-
Regard
Frank Kabe
Frankfurt, German

frankll wrote
 
Hi
o.k.
than use the following formula in AK64 (on your separate sheet)
=OFFSET('Pinedale Combined CF as Present'!$C$19,COLUMN()-37,0)
and copy to the right. This should do
 
No this doesn't work either. I read the user guide help relating to OFFSET and this does not appear to be what I am trying to do. Thanks for trying

----- Frank Kabel wrote: ----

H
o.k
than use the following formula in AK64 (on your separate sheet
=OFFSET('Pinedale Combined CF as Present'!$C$19,COLUMN()-37,0
and copy to the right. This should d

-
Regard
Frank Kabe
Frankfurt, German

frankll wrote
 
Hi
what problem did you encounter. The formula should copy the cell from
C19 (on your other sheet) to AK64. If you like you can email me your
spreadsheet and I'll have a look at it:
frank[dot]kabel[at]freenet[dot]de
 
Back
Top