Disappearing data

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

Guest

Hi

I have set up a spreadsheet so that I paste information in one format onto cells A1:A8 on Sheet 2 and then use a macro to reformat it and display it on sheet 1. Essentially the macro takes the data from a column format to a row format using "Transpose". The macro also selects the row once transposed and then copies and Paste Special Values (so as to break the link to sheet 2). Then new data is input into Sheet 2 A1:A8 which then goes onto the next row on Sheet 1 (I have used relative reference on the macro).

This was working fine for sometime but then a problem arose which meant I wanted to rework some of the data and the macro. To do this I need to delete any info from Sheet 2 - trouble is when I do that ALL the data in Sheet 1 disappears??!!

I could understand that a small amount might be effected - say one row, but I have got up to 360 rows of info and it is all disappearing??!!

HELP.

Thanks Ken
 
Ken" wrote...
Hi

I have set up a spreadsheet so that I paste information in one format onto
Click to expand...
cells A1:A8 on Sheet 2 and then use a macro to reformat it and display it on
sheet 1. Essentially the macro takes the data from a column format to a row
format using "Transpose". The macro also selects the row once transposed and
then copies and Paste Special Values (so as to break the link to sheet 2).
Then new data is input into Sheet 2 A1:A8 which then goes onto the next row
on Sheet 1 (I have used relative reference on the macro).
This was working fine for sometime but then a problem arose which meant I
Click to expand...
wanted to rework some of the data and the macro. To do this I need to delete
any info from Sheet 2 - trouble is when I do that ALL the data in Sheet 1
disappears??!!
I could understand that a small amount might be effected - say one row,
Click to expand...
but I have got up to 360 rows of info and it is all disappearing??!!
HELP.
Click to expand...

Ken,

You will need to provide more detail. Please show the macro and outline
what changes you want or need to make.

Regards,
Kevin
 
Hi Kevi

The macro recorded the following steps (macro was relative reference

- start with in cells A1:H1 on sheet
- Transpos
- select cells A1:A8 on sheet 2 (F4
- Cntr Shift Ente
- Copy cells A1:H
- Paste Special Values to same cells A1:H

Repeat using cells A2:H2 on sheet 1 from newly input data in cells A1:A8 on sheet 2

Continue ad in finitum

Regards Ke
 
The macro recorded the following steps (macro was relative reference)
- start with in cells A1:H1 on sheet 1
- Transpose
- select cells A1:A8 on sheet 2 (F4)
- Cntr Shift Enter
- Copy cells A1:H1
- Paste Special Values to same cells A1:H1

Repeat using cells A2:H2 on sheet 1 from newly input data in cells A1:A8 on sheet 2.

Continue ad in finitum.
Click to expand...

Hi Ken,

Ken please post your code. From your description above, I am not sure which
sheets you are referring to where.

I suspect that the answer will be quick and easy. From your problem
definition, it seems that you have linked the formulas and you need to have
values. Not a big deal. When you show the code, it ought to "jump out".

Thank you.

Best regards,
Kevin
 
"ken"wrote...
ActiveCell.Range("A1:H1").Select
Selection.FormulaArray = "=TRANSPOSE(Ark2!R1C1:R8C1)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
Click to expand...

Ken,

Sheet 1 = Active Sheet?
Sheet 2 = Ark2?

A little more description would be helpful.

Try the following

ActiveCell.Range("A1:H1").Select
Selection.FormulaArray = "=TRANSPOSE(Ark2!R1C1:R8C1)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, transpose:=False

'\ I am not sure why you are pasting twice? When you paste the second time
'\ you are creating live links? And that is why when you delete info from
'\ Sheet2, stuff on Sheet 1 dissappears?

'\ ActiveSheet.Paste this line has been commented out

Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

On an aside note--this process would go much more quickly if you described
your problem carefully and provided the code. Terse statements help no one,
and no one is being paid to assist. So it is bettter to provide a detailed
description along with the code so we can see what you are doing.

Hope that helps.

Best regards,
Kevin
 
Back
Top