Changing data orientation from one sheet to another

Hi,
On sheet1 the data is oriented vertically as
A1
A2
A3
I'd like on sheet2 the data be oriented horizontally
A1 to A1
A2 to B1
A3 to B3
Can it be done? if so, how?
TIA
Olga
Excell 07
0
4/20/2008 5:55:12 PM
excel.newusers 15348 articles. 2 followers. Follow

10 Replies
653 Views

Similar Articles

[PageSpeed] 56

Copy the data from sheet1

Select A1 of sheet2 and paste special>transpose.

Note:  cannot be done if you have more than 16384 rows of data in column A

Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?


Gord Dibben  MS Excel MVP

On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com> wrote:

>Hi,
>On sheet1 the data is oriented vertically as
>A1
>A2
>A3
>I'd like on sheet2 the data be oriented horizontally
>A1 to A1
>A2 to B1
>A3 to B3
>Can it be done? if so, how?
>TIA
>Olga
>Excell 07

0
Gord
4/20/2008 6:32:16 PM
Thank you Gord,
The data is coming from a SQL query and it is text and money values. If I 
copy A1 from sheet 1 and paste it as transpose on sheet2 works however, if I 
drag A1 horizontally to fulfill the rest of the cells then, the information 
is not coherent. Surly I'm doing some wrong.


"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias 
news:hr2n04d0r9ld2dan3q3oj6ejm6anqh5dhf@4ax.com...
> Copy the data from sheet1
>
> Select A1 of sheet2 and paste special>transpose.
>
> Note:  cannot be done if you have more than 16384 rows of data in column A
>
> Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?
>
>
> Gord Dibben  MS Excel MVP
>
> On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com> wrote:
>
>>Hi,
>>On sheet1 the data is oriented vertically as
>>A1
>>A2
>>A3
>>I'd like on sheet2 the data be oriented horizontally
>>A1 to A1
>>A2 to B1
>>A3 to B3
>>Can it be done? if so, how?
>>TIA
>>Olga
>>Excell 07
> 

0
4/20/2008 7:05:07 PM
"Surley I'm doing some wrong"

Yes, you are<g>

Re-read the instructions................I did not say to copy A1 from sheet1

Select the data range on sheet1 and copy.

Select A1 on sheet2 and Edit>Paste Special>Transpose>OK>Esc

No dragging needed.  You are paste/transposing the copied range, not just one
cell.


Gord

On Sun, 20 Apr 2008 21:05:07 +0200, "Olga" <noemail@hotmail.com> wrote:

>Thank you Gord,
>The data is coming from a SQL query and it is text and money values. If I 
>copy A1 from sheet 1 and paste it as transpose on sheet2 works however, if I 
>drag A1 horizontally to fulfill the rest of the cells then, the information 
>is not coherent. Surly I'm doing some wrong.
>
>
>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias 
>news:hr2n04d0r9ld2dan3q3oj6ejm6anqh5dhf@4ax.com...
>> Copy the data from sheet1
>>
>> Select A1 of sheet2 and paste special>transpose.
>>
>> Note:  cannot be done if you have more than 16384 rows of data in column A
>>
>> Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?
>>
>>
>> Gord Dibben  MS Excel MVP
>>
>> On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com> wrote:
>>
>>>Hi,
>>>On sheet1 the data is oriented vertically as
>>>A1
>>>A2
>>>A3
>>>I'd like on sheet2 the data be oriented horizontally
>>>A1 to A1
>>>A2 to B1
>>>A3 to B3
>>>Can it be done? if so, how?
>>>TIA
>>>Olga
>>>Excell 07
>> 

0
Gord
4/20/2008 8:54:15 PM
Thank you very much, it worked. But how would sheet 2 update itself when 
sheet 1 is updated refreshing the db connection?

"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias 
news:n2bn04hjl0f2ddtlnf1goupbjj1ttnc01k@4ax.com...
> "Surley I'm doing some wrong"
>
> Yes, you are<g>
>
> Re-read the instructions................I did not say to copy A1 from 
> sheet1
>
> Select the data range on sheet1 and copy.
>
> Select A1 on sheet2 and Edit>Paste Special>Transpose>OK>Esc
>
> No dragging needed.  You are paste/transposing the copied range, not just 
> one
> cell.
>
>
> Gord
>
> On Sun, 20 Apr 2008 21:05:07 +0200, "Olga" <noemail@hotmail.com> wrote:
>
>>Thank you Gord,
>>The data is coming from a SQL query and it is text and money values. If I
>>copy A1 from sheet 1 and paste it as transpose on sheet2 works however, if 
>>I
>>drag A1 horizontally to fulfill the rest of the cells then, the 
>>information
>>is not coherent. Surly I'm doing some wrong.
>>
>>
>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>news:hr2n04d0r9ld2dan3q3oj6ejm6anqh5dhf@4ax.com...
>>> Copy the data from sheet1
>>>
>>> Select A1 of sheet2 and paste special>transpose.
>>>
>>> Note:  cannot be done if you have more than 16384 rows of data in column 
>>> A
>>>
>>> Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?
>>>
>>>
>>> Gord Dibben  MS Excel MVP
>>>
>>> On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com> wrote:
>>>
>>>>Hi,
>>>>On sheet1 the data is oriented vertically as
>>>>A1
>>>>A2
>>>>A3
>>>>I'd like on sheet2 the data be oriented horizontally
>>>>A1 to A1
>>>>A2 to B1
>>>>A3 to B3
>>>>Can it be done? if so, how?
>>>>TIA
>>>>Olga
>>>>Excell 07
>>>
> 

0
4/20/2008 9:54:53 PM
Sheet2 will not update itself using the manual transpose method we just did.

We will have to link the cells to the source range then tranpose those links.

Or write a macro to do the job after each query.

What is your original source range that will be transposed?

Is it consistent or variable range?


Gord

On Sun, 20 Apr 2008 23:54:53 +0200, "Olga" <noemail@hotmail.com> wrote:

>Thank you very much, it worked. But how would sheet 2 update itself when 
>sheet 1 is updated refreshing the db connection?
>
>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias 
>news:n2bn04hjl0f2ddtlnf1goupbjj1ttnc01k@4ax.com...
>> "Surley I'm doing some wrong"
>>
>> Yes, you are<g>
>>
>> Re-read the instructions................I did not say to copy A1 from 
>> sheet1
>>
>> Select the data range on sheet1 and copy.
>>
>> Select A1 on sheet2 and Edit>Paste Special>Transpose>OK>Esc
>>
>> No dragging needed.  You are paste/transposing the copied range, not just 
>> one
>> cell.
>>
>>
>> Gord
>>
>> On Sun, 20 Apr 2008 21:05:07 +0200, "Olga" <noemail@hotmail.com> wrote:
>>
>>>Thank you Gord,
>>>The data is coming from a SQL query and it is text and money values. If I
>>>copy A1 from sheet 1 and paste it as transpose on sheet2 works however, if 
>>>I
>>>drag A1 horizontally to fulfill the rest of the cells then, the 
>>>information
>>>is not coherent. Surly I'm doing some wrong.
>>>
>>>
>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>news:hr2n04d0r9ld2dan3q3oj6ejm6anqh5dhf@4ax.com...
>>>> Copy the data from sheet1
>>>>
>>>> Select A1 of sheet2 and paste special>transpose.
>>>>
>>>> Note:  cannot be done if you have more than 16384 rows of data in column 
>>>> A
>>>>
>>>> Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?
>>>>
>>>>
>>>> Gord Dibben  MS Excel MVP
>>>>
>>>> On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com> wrote:
>>>>
>>>>>Hi,
>>>>>On sheet1 the data is oriented vertically as
>>>>>A1
>>>>>A2
>>>>>A3
>>>>>I'd like on sheet2 the data be oriented horizontally
>>>>>A1 to A1
>>>>>A2 to B1
>>>>>A3 to B3
>>>>>Can it be done? if so, how?
>>>>>TIA
>>>>>Olga
>>>>>Excell 07
>>>>
>> 

0
Gord
4/20/2008 10:58:17 PM
The original sourse range is composed of two columns containing labels(A1) 
and numbers (euros B1).
It's variable, every month a new row is added by the query.

"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias 
news:74in045cci7i669u7aegrsqj56b7ii3196@4ax.com...
> Sheet2 will not update itself using the manual transpose method we just 
> did.
>
> We will have to link the cells to the source range then tranpose those 
> links.
>
> Or write a macro to do the job after each query.
>
> What is your original source range that will be transposed?
>
> Is it consistent or variable range?
>
>
> Gord
>
> On Sun, 20 Apr 2008 23:54:53 +0200, "Olga" <noemail@hotmail.com> wrote:
>
>>Thank you very much, it worked. But how would sheet 2 update itself when
>>sheet 1 is updated refreshing the db connection?
>>
>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>news:n2bn04hjl0f2ddtlnf1goupbjj1ttnc01k@4ax.com...
>>> "Surley I'm doing some wrong"
>>>
>>> Yes, you are<g>
>>>
>>> Re-read the instructions................I did not say to copy A1 from
>>> sheet1
>>>
>>> Select the data range on sheet1 and copy.
>>>
>>> Select A1 on sheet2 and Edit>Paste Special>Transpose>OK>Esc
>>>
>>> No dragging needed.  You are paste/transposing the copied range, not 
>>> just
>>> one
>>> cell.
>>>
>>>
>>> Gord
>>>
>>> On Sun, 20 Apr 2008 21:05:07 +0200, "Olga" <noemail@hotmail.com> wrote:
>>>
>>>>Thank you Gord,
>>>>The data is coming from a SQL query and it is text and money values. If 
>>>>I
>>>>copy A1 from sheet 1 and paste it as transpose on sheet2 works however, 
>>>>if
>>>>I
>>>>drag A1 horizontally to fulfill the rest of the cells then, the
>>>>information
>>>>is not coherent. Surly I'm doing some wrong.
>>>>
>>>>
>>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>>news:hr2n04d0r9ld2dan3q3oj6ejm6anqh5dhf@4ax.com...
>>>>> Copy the data from sheet1
>>>>>
>>>>> Select A1 of sheet2 and paste special>transpose.
>>>>>
>>>>> Note:  cannot be done if you have more than 16384 rows of data in 
>>>>> column
>>>>> A
>>>>>
>>>>> Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?
>>>>>
>>>>>
>>>>> Gord Dibben  MS Excel MVP
>>>>>
>>>>> On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com> 
>>>>> wrote:
>>>>>
>>>>>>Hi,
>>>>>>On sheet1 the data is oriented vertically as
>>>>>>A1
>>>>>>A2
>>>>>>A3
>>>>>>I'd like on sheet2 the data be oriented horizontally
>>>>>>A1 to A1
>>>>>>A2 to B1
>>>>>>A3 to B3
>>>>>>Can it be done? if so, how?
>>>>>>TIA
>>>>>>Olga
>>>>>>Excell 07
>>>>>
>>>
> 

0
4/21/2008 6:15:14 AM
If once a month, automation should not really be required.

But, this macro can be run to copy the sheet1 data to a new sheet.

Sub select_transpose()
    Range(Range("A1:B1"), Cells(Rows.Count, Selection.Column).End(xlUp)).Copy
    Sheets.Add
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
    Range("A1").Select
End Sub


Gord

On Mon, 21 Apr 2008 08:15:14 +0200, "Olga" <noemail@hotmail.com> wrote:

>The original sourse range is composed of two columns containing labels(A1) 
>and numbers (euros B1).
>It's variable, every month a new row is added by the query.
>
>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias 
>news:74in045cci7i669u7aegrsqj56b7ii3196@4ax.com...
>> Sheet2 will not update itself using the manual transpose method we just 
>> did.
>>
>> We will have to link the cells to the source range then tranpose those 
>> links.
>>
>> Or write a macro to do the job after each query.
>>
>> What is your original source range that will be transposed?
>>
>> Is it consistent or variable range?
>>
>>
>> Gord
>>
>> On Sun, 20 Apr 2008 23:54:53 +0200, "Olga" <noemail@hotmail.com> wrote:
>>
>>>Thank you very much, it worked. But how would sheet 2 update itself when
>>>sheet 1 is updated refreshing the db connection?
>>>
>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>news:n2bn04hjl0f2ddtlnf1goupbjj1ttnc01k@4ax.com...
>>>> "Surley I'm doing some wrong"
>>>>
>>>> Yes, you are<g>
>>>>
>>>> Re-read the instructions................I did not say to copy A1 from
>>>> sheet1
>>>>
>>>> Select the data range on sheet1 and copy.
>>>>
>>>> Select A1 on sheet2 and Edit>Paste Special>Transpose>OK>Esc
>>>>
>>>> No dragging needed.  You are paste/transposing the copied range, not 
>>>> just
>>>> one
>>>> cell.
>>>>
>>>>
>>>> Gord
>>>>
>>>> On Sun, 20 Apr 2008 21:05:07 +0200, "Olga" <noemail@hotmail.com> wrote:
>>>>
>>>>>Thank you Gord,
>>>>>The data is coming from a SQL query and it is text and money values. If 
>>>>>I
>>>>>copy A1 from sheet 1 and paste it as transpose on sheet2 works however, 
>>>>>if
>>>>>I
>>>>>drag A1 horizontally to fulfill the rest of the cells then, the
>>>>>information
>>>>>is not coherent. Surly I'm doing some wrong.
>>>>>
>>>>>
>>>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>>>news:hr2n04d0r9ld2dan3q3oj6ejm6anqh5dhf@4ax.com...
>>>>>> Copy the data from sheet1
>>>>>>
>>>>>> Select A1 of sheet2 and paste special>transpose.
>>>>>>
>>>>>> Note:  cannot be done if you have more than 16384 rows of data in 
>>>>>> column
>>>>>> A
>>>>>>
>>>>>> Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?
>>>>>>
>>>>>>
>>>>>> Gord Dibben  MS Excel MVP
>>>>>>
>>>>>> On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com> 
>>>>>> wrote:
>>>>>>
>>>>>>>Hi,
>>>>>>>On sheet1 the data is oriented vertically as
>>>>>>>A1
>>>>>>>A2
>>>>>>>A3
>>>>>>>I'd like on sheet2 the data be oriented horizontally
>>>>>>>A1 to A1
>>>>>>>A2 to B1
>>>>>>>A3 to B3
>>>>>>>Can it be done? if so, how?
>>>>>>>TIA
>>>>>>>Olga
>>>>>>>Excell 07
>>>>>>
>>>>
>> 

0
Gord
4/22/2008 5:08:06 PM
Hi Gord,
apparently I need to do my transformation manually or by recording  a macro 
because otherwise, my cells options (borders, alignments, colors, etc) get 
mist up when actualizing the data/ query. Using the below test micro, only 
the data is updated leaving the cells format intact. My problems is that I've 
over 100 cells (a + b = 200) to manually transform. So, perhaps, I need to 
think of a loop.
------------------------------------------------------
Sub Macro2()
Sheets("TEST").Select
    Range("B16").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=DB!R[-14]C[-1]"
    Range("C16").Select
    ActiveCell.FormulaR1C1 = "=DB!R[-13]C[-2]"
    Range("D16").Select
    ActiveCell.FormulaR1C1 = "=DB!R[-12]C[-3]"
    Range("E16").Select
    ActiveCell.FormulaR1C1 = "=DB!R[-11]C[-4]"
    Range("B17").Select
    ActiveCell.FormulaR1C1 = "=DB!R[-15]C"
    Range("C17").Select
    ActiveCell.FormulaR1C1 = "=DB!R[-14]C[-1]"
    Range("D17").Select
    ActiveCell.FormulaR1C1 = "=DB!R[-13]C[-2]"
    Range("E17").Select
    ActiveCell.FormulaR1C1 = "=DB!R[-12]C[-3]"
    Range("E18").Select
End Sub

--------------------------------------------------------------

"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias 
news:be5s04dnrnitqh48ac82ngt9ejeke1pe3s@4ax.com...
> If once a month, automation should not really be required.
>
> But, this macro can be run to copy the sheet1 data to a new sheet.
>
> Sub select_transpose()
>    Range(Range("A1:B1"), Cells(Rows.Count, 
> Selection.Column).End(xlUp)).Copy
>    Sheets.Add
>    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, 
> SkipBlanks:= _
>        False, Transpose:=True
>    Application.CutCopyMode = False
>    Range("A1").Select
> End Sub
>
>
> Gord
>
> On Mon, 21 Apr 2008 08:15:14 +0200, "Olga" <noemail@hotmail.com> wrote:
>
>>The original sourse range is composed of two columns containing labels(A1)
>>and numbers (euros B1).
>>It's variable, every month a new row is added by the query.
>>
>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>news:74in045cci7i669u7aegrsqj56b7ii3196@4ax.com...
>>> Sheet2 will not update itself using the manual transpose method we just
>>> did.
>>>
>>> We will have to link the cells to the source range then tranpose those
>>> links.
>>>
>>> Or write a macro to do the job after each query.
>>>
>>> What is your original source range that will be transposed?
>>>
>>> Is it consistent or variable range?
>>>
>>>
>>> Gord
>>>
>>> On Sun, 20 Apr 2008 23:54:53 +0200, "Olga" <noemail@hotmail.com> wrote:
>>>
>>>>Thank you very much, it worked. But how would sheet 2 update itself when
>>>>sheet 1 is updated refreshing the db connection?
>>>>
>>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>>news:n2bn04hjl0f2ddtlnf1goupbjj1ttnc01k@4ax.com...
>>>>> "Surley I'm doing some wrong"
>>>>>
>>>>> Yes, you are<g>
>>>>>
>>>>> Re-read the instructions................I did not say to copy A1 from
>>>>> sheet1
>>>>>
>>>>> Select the data range on sheet1 and copy.
>>>>>
>>>>> Select A1 on sheet2 and Edit>Paste Special>Transpose>OK>Esc
>>>>>
>>>>> No dragging needed.  You are paste/transposing the copied range, not
>>>>> just
>>>>> one
>>>>> cell.
>>>>>
>>>>>
>>>>> Gord
>>>>>
>>>>> On Sun, 20 Apr 2008 21:05:07 +0200, "Olga" <noemail@hotmail.com> 
>>>>> wrote:
>>>>>
>>>>>>Thank you Gord,
>>>>>>The data is coming from a SQL query and it is text and money values. 
>>>>>>If
>>>>>>I
>>>>>>copy A1 from sheet 1 and paste it as transpose on sheet2 works 
>>>>>>however,
>>>>>>if
>>>>>>I
>>>>>>drag A1 horizontally to fulfill the rest of the cells then, the
>>>>>>information
>>>>>>is not coherent. Surly I'm doing some wrong.
>>>>>>
>>>>>>
>>>>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>>>>news:hr2n04d0r9ld2dan3q3oj6ejm6anqh5dhf@4ax.com...
>>>>>>> Copy the data from sheet1
>>>>>>>
>>>>>>> Select A1 of sheet2 and paste special>transpose.
>>>>>>>
>>>>>>> Note:  cannot be done if you have more than 16384 rows of data in
>>>>>>> column
>>>>>>> A
>>>>>>>
>>>>>>> Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?
>>>>>>>
>>>>>>>
>>>>>>> Gord Dibben  MS Excel MVP
>>>>>>>
>>>>>>> On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>>Hi,
>>>>>>>>On sheet1 the data is oriented vertically as
>>>>>>>>A1
>>>>>>>>A2
>>>>>>>>A3
>>>>>>>>I'd like on sheet2 the data be oriented horizontally
>>>>>>>>A1 to A1
>>>>>>>>A2 to B1
>>>>>>>>A3 to B3
>>>>>>>>Can it be done? if so, how?
>>>>>>>>TIA
>>>>>>>>Olga
>>>>>>>>Excell 07
>>>>>>>
>>>>>
>>>
> 

0
olgass (1)
4/22/2008 7:19:04 PM
Did you try the macro I supplied?

What alterations do you think it needs?

Why do you think you need to link the cells if the changes are to be made once a
month?


Gord

On Tue, 22 Apr 2008 21:19:04 +0200, "Olga" <olgass@yahoo.com> wrote:

>Hi Gord,
>apparently I need to do my transformation manually or by recording  a macro 
>because otherwise, my cells options (borders, alignments, colors, etc) get 
>mist up when actualizing the data/ query. Using the below test micro, only 
>the data is updated leaving the cells format intact. My problems is that I've 
>over 100 cells (a + b = 200) to manually transform. So, perhaps, I need to 
>think of a loop.
>------------------------------------------------------
>Sub Macro2()
>Sheets("TEST").Select
>    Range("B16").Select
>    Application.CutCopyMode = False
>    ActiveCell.FormulaR1C1 = "=DB!R[-14]C[-1]"
>    Range("C16").Select
>    ActiveCell.FormulaR1C1 = "=DB!R[-13]C[-2]"
>    Range("D16").Select
>    ActiveCell.FormulaR1C1 = "=DB!R[-12]C[-3]"
>    Range("E16").Select
>    ActiveCell.FormulaR1C1 = "=DB!R[-11]C[-4]"
>    Range("B17").Select
>    ActiveCell.FormulaR1C1 = "=DB!R[-15]C"
>    Range("C17").Select
>    ActiveCell.FormulaR1C1 = "=DB!R[-14]C[-1]"
>    Range("D17").Select
>    ActiveCell.FormulaR1C1 = "=DB!R[-13]C[-2]"
>    Range("E17").Select
>    ActiveCell.FormulaR1C1 = "=DB!R[-12]C[-3]"
>    Range("E18").Select
>End Sub
>
>--------------------------------------------------------------
>
>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias 
>news:be5s04dnrnitqh48ac82ngt9ejeke1pe3s@4ax.com...
>> If once a month, automation should not really be required.
>>
>> But, this macro can be run to copy the sheet1 data to a new sheet.
>>
>> Sub select_transpose()
>>    Range(Range("A1:B1"), Cells(Rows.Count, 
>> Selection.Column).End(xlUp)).Copy
>>    Sheets.Add
>>    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, 
>> SkipBlanks:= _
>>        False, Transpose:=True
>>    Application.CutCopyMode = False
>>    Range("A1").Select
>> End Sub
>>
>>
>> Gord
>>
>> On Mon, 21 Apr 2008 08:15:14 +0200, "Olga" <noemail@hotmail.com> wrote:
>>
>>>The original sourse range is composed of two columns containing labels(A1)
>>>and numbers (euros B1).
>>>It's variable, every month a new row is added by the query.
>>>
>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>news:74in045cci7i669u7aegrsqj56b7ii3196@4ax.com...
>>>> Sheet2 will not update itself using the manual transpose method we just
>>>> did.
>>>>
>>>> We will have to link the cells to the source range then tranpose those
>>>> links.
>>>>
>>>> Or write a macro to do the job after each query.
>>>>
>>>> What is your original source range that will be transposed?
>>>>
>>>> Is it consistent or variable range?
>>>>
>>>>
>>>> Gord
>>>>
>>>> On Sun, 20 Apr 2008 23:54:53 +0200, "Olga" <noemail@hotmail.com> wrote:
>>>>
>>>>>Thank you very much, it worked. But how would sheet 2 update itself when
>>>>>sheet 1 is updated refreshing the db connection?
>>>>>
>>>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>>>news:n2bn04hjl0f2ddtlnf1goupbjj1ttnc01k@4ax.com...
>>>>>> "Surley I'm doing some wrong"
>>>>>>
>>>>>> Yes, you are<g>
>>>>>>
>>>>>> Re-read the instructions................I did not say to copy A1 from
>>>>>> sheet1
>>>>>>
>>>>>> Select the data range on sheet1 and copy.
>>>>>>
>>>>>> Select A1 on sheet2 and Edit>Paste Special>Transpose>OK>Esc
>>>>>>
>>>>>> No dragging needed.  You are paste/transposing the copied range, not
>>>>>> just
>>>>>> one
>>>>>> cell.
>>>>>>
>>>>>>
>>>>>> Gord
>>>>>>
>>>>>> On Sun, 20 Apr 2008 21:05:07 +0200, "Olga" <noemail@hotmail.com> 
>>>>>> wrote:
>>>>>>
>>>>>>>Thank you Gord,
>>>>>>>The data is coming from a SQL query and it is text and money values. 
>>>>>>>If
>>>>>>>I
>>>>>>>copy A1 from sheet 1 and paste it as transpose on sheet2 works 
>>>>>>>however,
>>>>>>>if
>>>>>>>I
>>>>>>>drag A1 horizontally to fulfill the rest of the cells then, the
>>>>>>>information
>>>>>>>is not coherent. Surly I'm doing some wrong.
>>>>>>>
>>>>>>>
>>>>>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>>>>>news:hr2n04d0r9ld2dan3q3oj6ejm6anqh5dhf@4ax.com...
>>>>>>>> Copy the data from sheet1
>>>>>>>>
>>>>>>>> Select A1 of sheet2 and paste special>transpose.
>>>>>>>>
>>>>>>>> Note:  cannot be done if you have more than 16384 rows of data in
>>>>>>>> column
>>>>>>>> A
>>>>>>>>
>>>>>>>> Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?
>>>>>>>>
>>>>>>>>
>>>>>>>> Gord Dibben  MS Excel MVP
>>>>>>>>
>>>>>>>> On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>>Hi,
>>>>>>>>>On sheet1 the data is oriented vertically as
>>>>>>>>>A1
>>>>>>>>>A2
>>>>>>>>>A3
>>>>>>>>>I'd like on sheet2 the data be oriented horizontally
>>>>>>>>>A1 to A1
>>>>>>>>>A2 to B1
>>>>>>>>>A3 to B3
>>>>>>>>>Can it be done? if so, how?
>>>>>>>>>TIA
>>>>>>>>>Olga
>>>>>>>>>Excell 07
>>>>>>>>
>>>>>>
>>>>
>> 

0
Gord
4/22/2008 11:23:43 PM
Hello Gord, thank you for your support.

Yes I did test your macro; in fact, thanks to you, I learned how to use 
them. I made a little change to your working macro so that it always points 
to the same datasheet (DB) and paste the copy in the same worksheet (TEST).

---------------------------------------

 Sub select_transpose()
   Sheets("DB").Select
   Range(Range("A2:B70"), Cells(Rows.Count, 
Selection.Column).End(xlUp)).Copy
    'Sheets.Add
    Sheets("TEST").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, 
SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
   'Range("A2").Select
 End Sub

------------------------------------

Ones pasted the information; I need to format the cells giving color, type 
of text, size. in few words, making it looking nice. Now, if I run the macro 
again, I lose all the cells formats, thus my work. Even if I need to do it 
once a month, why repeat all the work done? That's why I thought of my 
recorded macro which does leave the cells format intact updating only the 
containing data. I tried to format the datasheet so that the format can be 
pasted as well but it's not the same as formatting the worksheet itself, in 
my case. I hope to have explained myself well, English is not my native 
language and sorry for the time delay, I'm in Spain.

Olga



"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias 
news:0pss045e6ebiqdlpqlp2plenu2ctbb0i32@4ax.com...
> Did you try the macro I supplied?
>
> What alterations do you think it needs?
>
> Why do you think you need to link the cells if the changes are to be made 
> once a
> month?
>
>
> Gord
>
> On Tue, 22 Apr 2008 21:19:04 +0200, "Olga" <olgass@yahoo.com> wrote:
>
>>Hi Gord,
>>apparently I need to do my transformation manually or by recording  a 
>>macro
>>because otherwise, my cells options (borders, alignments, colors, etc) get
>>mist up when actualizing the data/ query. Using the below test micro, only
>>the data is updated leaving the cells format intact. My problems is that 
>>I've
>>over 100 cells (a + b = 200) to manually transform. So, perhaps, I need to
>>think of a loop.
>>------------------------------------------------------
>>Sub Macro2()
>>Sheets("TEST").Select
>>    Range("B16").Select
>>    Application.CutCopyMode = False
>>    ActiveCell.FormulaR1C1 = "=DB!R[-14]C[-1]"
>>    Range("C16").Select
>>    ActiveCell.FormulaR1C1 = "=DB!R[-13]C[-2]"
>>    Range("D16").Select
>>    ActiveCell.FormulaR1C1 = "=DB!R[-12]C[-3]"
>>    Range("E16").Select
>>    ActiveCell.FormulaR1C1 = "=DB!R[-11]C[-4]"
>>    Range("B17").Select
>>    ActiveCell.FormulaR1C1 = "=DB!R[-15]C"
>>    Range("C17").Select
>>    ActiveCell.FormulaR1C1 = "=DB!R[-14]C[-1]"
>>    Range("D17").Select
>>    ActiveCell.FormulaR1C1 = "=DB!R[-13]C[-2]"
>>    Range("E17").Select
>>    ActiveCell.FormulaR1C1 = "=DB!R[-12]C[-3]"
>>    Range("E18").Select
>>End Sub
>>
>>--------------------------------------------------------------
>>
>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>news:be5s04dnrnitqh48ac82ngt9ejeke1pe3s@4ax.com...
>>> If once a month, automation should not really be required.
>>>
>>> But, this macro can be run to copy the sheet1 data to a new sheet.
>>>
>>> Sub select_transpose()
>>>    Range(Range("A1:B1"), Cells(Rows.Count,
>>> Selection.Column).End(xlUp)).Copy
>>>    Sheets.Add
>>>    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
>>> SkipBlanks:= _
>>>        False, Transpose:=True
>>>    Application.CutCopyMode = False
>>>    Range("A1").Select
>>> End Sub
>>>
>>>
>>> Gord
>>>
>>> On Mon, 21 Apr 2008 08:15:14 +0200, "Olga" <noemail@hotmail.com> wrote:
>>>
>>>>The original sourse range is composed of two columns containing 
>>>>labels(A1)
>>>>and numbers (euros B1).
>>>>It's variable, every month a new row is added by the query.
>>>>
>>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>>news:74in045cci7i669u7aegrsqj56b7ii3196@4ax.com...
>>>>> Sheet2 will not update itself using the manual transpose method we 
>>>>> just
>>>>> did.
>>>>>
>>>>> We will have to link the cells to the source range then tranpose those
>>>>> links.
>>>>>
>>>>> Or write a macro to do the job after each query.
>>>>>
>>>>> What is your original source range that will be transposed?
>>>>>
>>>>> Is it consistent or variable range?
>>>>>
>>>>>
>>>>> Gord
>>>>>
>>>>> On Sun, 20 Apr 2008 23:54:53 +0200, "Olga" <noemail@hotmail.com> 
>>>>> wrote:
>>>>>
>>>>>>Thank you very much, it worked. But how would sheet 2 update itself 
>>>>>>when
>>>>>>sheet 1 is updated refreshing the db connection?
>>>>>>
>>>>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de noticias
>>>>>>news:n2bn04hjl0f2ddtlnf1goupbjj1ttnc01k@4ax.com...
>>>>>>> "Surley I'm doing some wrong"
>>>>>>>
>>>>>>> Yes, you are<g>
>>>>>>>
>>>>>>> Re-read the instructions................I did not say to copy A1 
>>>>>>> from
>>>>>>> sheet1
>>>>>>>
>>>>>>> Select the data range on sheet1 and copy.
>>>>>>>
>>>>>>> Select A1 on sheet2 and Edit>Paste Special>Transpose>OK>Esc
>>>>>>>
>>>>>>> No dragging needed.  You are paste/transposing the copied range, not
>>>>>>> just
>>>>>>> one
>>>>>>> cell.
>>>>>>>
>>>>>>>
>>>>>>> Gord
>>>>>>>
>>>>>>> On Sun, 20 Apr 2008 21:05:07 +0200, "Olga" <noemail@hotmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>>Thank you Gord,
>>>>>>>>The data is coming from a SQL query and it is text and money values.
>>>>>>>>If
>>>>>>>>I
>>>>>>>>copy A1 from sheet 1 and paste it as transpose on sheet2 works
>>>>>>>>however,
>>>>>>>>if
>>>>>>>>I
>>>>>>>>drag A1 horizontally to fulfill the rest of the cells then, the
>>>>>>>>information
>>>>>>>>is not coherent. Surly I'm doing some wrong.
>>>>>>>>
>>>>>>>>
>>>>>>>>"Gord Dibben" <gorddibbATshawDOTca> escribi� en el mensaje de 
>>>>>>>>noticias
>>>>>>>>news:hr2n04d0r9ld2dan3q3oj6ejm6anqh5dhf@4ax.com...
>>>>>>>>> Copy the data from sheet1
>>>>>>>>>
>>>>>>>>> Select A1 of sheet2 and paste special>transpose.
>>>>>>>>>
>>>>>>>>> Note:  cannot be done if you have more than 16384 rows of data in
>>>>>>>>> column
>>>>>>>>> A
>>>>>>>>>
>>>>>>>>> Also, I think you have a typo at  A3 to B3.........maybe A3 to C1?
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Gord Dibben  MS Excel MVP
>>>>>>>>>
>>>>>>>>> On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com>
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>>Hi,
>>>>>>>>>>On sheet1 the data is oriented vertically as
>>>>>>>>>>A1
>>>>>>>>>>A2
>>>>>>>>>>A3
>>>>>>>>>>I'd like on sheet2 the data be oriented horizontally
>>>>>>>>>>A1 to A1
>>>>>>>>>>A2 to B1
>>>>>>>>>>A3 to B3
>>>>>>>>>>Can it be done? if so, how?
>>>>>>>>>>TIA
>>>>>>>>>>Olga
>>>>>>>>>>Excell 07
>>>>>>>>>
>>>>>>>
>>>>>
>>>
> 

0
4/23/2008 7:14:53 AM
Reply:

Similar Artilces:

External data link change
Hi, I've a sheet linked to an external data on the net, and I would like that a cell in this sheet to indicate the last date and time it was updated, the simplest way the better but I can do some programming too. Thanks for your attention, -- Domingos Junqueira No need of help any more, I solved the question. Thanks again ...

Synchronize HQ data from SO
I curretly have 2 stores that usually receive inventory update from HQ. However during the last few weeks I had to data entry from each RMS clients (SO). Now I want to update inventory data at HQ using worksheet. But it seems not possible, although sales data show correctly. Is there any way ? -- Many Thanks In an HQ environment all new items must be entered from HQ and sent to stores. Did you create new items at store level ? You may want to create those items in HQ and send to store. Mihir Shah Diviasoft, Inc. www.diviasoft.com "cosamo" <cosamo@discussions.microsoft.c...

Is there a way to get the list command on the data toolbar?
I don't have list on my data toolbar. Is there a download for that or do I have to pay for it? I need it for a homework assignment. Thanks You need to use xl2003. That means you need to buy the newer version (or explain it to the instructor). Maybe he or she will let you slide on this section. cbuskirk20 wrote: > > I don't have list on my data toolbar. Is there a download for that or do I > have to pay for it? I need it for a homework assignment. > > Thanks -- Dave Peterson ...

Why does the change change to a number?
Hi all, I've noticed something wierd and always wondered WHY it happens. When you type a date into a cell, and then change the Formatting of it to a 'general' cell, it turns into a number. How does it come up with that number? What is the significance? i.e. type today's date of "12/7/2007" - change it to a 'General' format, and it then says "39423". I'm a trainer of Excel and this question always comes up. I'm curious myself too. Thanks! Joe It's the number of days since January zero 1900 using Excel default for windows (M...

changing the way Excel displays selected cells
Is there a way to change the way Excel displays selected cells? I'm interested in viewing all the selected cells highlighted (with light blue for instance), but by default excel highlights all the cells but not the first one the same occurs when you define a range with no adyacents cells Your definition of the display is not quite correct. Excel highlights the current cell, Excel also highlights a selecte range. The currently selected cell is generally the first of a range, bu press enter and the current cell changes and becomes the second, the third etc in the range. You cannot...

Track Changes
How do i remove the track changes in outlook? When i press "Enter", a symbol that represents "Enter" will appears. Same for space etc. ...

DST changes for Exchange 5.5
I noticed the 2007 DST Calendar Update "Exchange tool" is available now: http://support.microsoft.com/kb/930879 This will seemingly take care of calendars for mailboxes still on Exchange 5.5 servers, as Exchange 5.5 is listed as "compatible" . However would this address the CDO issues such as BlackBerry users and OWA users still on Exchange 5.5 ? Thanks in advance, Itrcb4 On Mon, 12 Feb 2007 14:31:00 -0800, itrcb4 <itrcb4@discussions.microsoft.com> wrote: >I noticed the 2007 DST Calendar Update "Exchange tool" is available now: > >http://su...

Stop sheet showing when Using Datapig's Force Users to enable macr
Hi, I think this is probably not do-able I'm using Datapig Technologies example to force users to enable macros When the workbook is closed all sheets other than a warning sheet are very hidden so that if the workbook is opened with macros disabled then the user can only see a warning sheet saying to close and open with macros enabled. Then on opening with macros enabled the waring sheet is very hidden and the other sheets can be seen. Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.ScreenUpdating = False Sheets("Warning").Visible = True Sh...

more on VBA function name change
I thought I'd start a new thread since I haven't received any replies to my first one... To recap: I've declared a function in a module using mixed case: Function TMDE_Category (FormName As Form) I noticed recently that it appeared in the module as Function tmde_category(FormName As Form) I changed it back to the mixed case declaration, saved the module, exited the app, reopened it and looked. The function had changed back to the all lowercase declaration. Things I've tried since the original post: Using the databse documenter, I selected all ob...

Can't Publish changes with Deploy Manager
After a migration process, I can't publish the changes made on CRM. When I try do this in Deploy Manager I get the follow error: ---------------------------------------------------------------------------- ----- Publish done with errors. See the event log to get deitails NETRA-INOVACAO: ***Error*** Failed to download XSL template files from Web Server ---------------------------------------------------------------------------- ----- Can somebody help me? I don't know if the migration process have any relationship with the error. Thank you for pay attention. []'s Vin�cius Pitta...

witch one should i get
Hi im Sam i want to buy the latest ms money but witch one should i get i want every can someone compare with Microsoft Money 2006 Standard and Microsoft Money 2006 Premium and Microsoft Money 2006 Deluxe or Microsoft Money 2006 Small Business please would someone help be to decide the best one thank you Sam M06 is two versions obsolete hence two versions closer to being unsupported/disabled from online services. Deluxe is fine for most people. Premium adds some features of slight value to some and large value to others. If you do not have a business need, do not get the business editi...

How to change icon for my application
Hi, I am currently developing an application on visual studio 6.0, and i wish to change the MFC icon on my application header. Anyone can help? Thank you. Raed Sawalha wrote: > Hi, I am currently developing an application on visual studio 6.0, and i > wish to change the MFC icon on my application header. Anyone can help? Thank > you. > > Open the icon resource for editing by double clicking. Then notice the control just above the editing grid that lets you switch between editing the large icon and editing the small one. -- Scott McPhillips [VC++ MVP] thanx that work...

How to change newsgroup message font
Like many other ribbon based programs I sometimes struggle to find how to make a change. This time its how to change the font just for newsgroup messages? regards "nobody" wrote in message news:EWTao.39493$GF5.7129@hurricane... > Like many other ribbon based programs I sometimes struggle to find how to make a change. This time its how to change the font just for newsgroup messages? Newsgroup messages are usually plain text. The font used is that selected at [no name tab] > Options > Mail > Read > Fonts for the encoding specified for the messag...

Which one first?Install Windows Server 2003 SP1 or Exchange SP2 ?
Hi, We are running Exchange 2003 on Windows Server 2003. We planned to update the software in terms of patches, so which one should I do first, the OS patches (SP1 and onwards) or the Exchange patches ? Rgrds, Zul If it was me do the OS first. "Zul J" <mlist@istar.com.my> wrote in message news:%23AeXnXErFHA.2076@TK2MSFTNGP14.phx.gbl... > Hi, > > We are running Exchange 2003 on Windows Server 2003. We planned to update > the software in terms of patches, so which one should I do first, the OS > patches (SP1 and onwards) or the Exchange patches ? > ...

Notify change of email address when changing ISP
How do I notify my entire address book of an email address when I change ISP's? Thanks By sending a mail to everyone ? If you do so, please use BCC for the adresses, this way you won't spread everybody's e-mail adres to everybody. Niels Bob Baker wrote: > How do I notify my entire address book of an email address when I change ISP's? > Thanks ...

CRM data migration 10-13-04
Is there an easy way to migrate data from one CRM server to another? I have looked through the Data Migration toolkit, but it seems to be geared more towards migrating Goldmine/Act/etc to CRM, by populating the cdf tables "manually". Thanks, Matt Matt wrote: > Is there an easy way to migrate data from one CRM server to another? > > I have looked through the Data Migration toolkit, but it seems to be geared > more towards migrating Goldmine/Act/etc to CRM, by populating the cdf tables > "manually". > > Thanks, > Matt You need to use Redeployme...

Pivot Table showing wrong data
I have a table with information such as Salesperson, Customer, Sales $, Year, Month, Date_Customer_Started. The data contains info for 2009 and 2010. I am trying to determine for every new account created in 2009 and 2010, Sales by new customer and salesperson. The Pivot table has sales data and count of customers by year and the rows contain Salesperson, Customer, Month and date_Customer_started. I expect if a customer started in April 2009, and the customer continuing to buy in 2010, sales figures by year and month and the count of customer only in 2009. But the pivot table sh...

Change a formula to an acual number
I want to change the formula I have created to the number it has created Example: Cell A1 is the number 1. Cell A2 is the formula =a1+1 creatin the number 2. I want this to be a two not a formula. Any ideas? Laura, copy, then paste special, valves -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 97 ** remove news from my email address to reply by email ** "Laura" <anonymous@discussions.microsoft.com> wrote in message news...

Format a cell in a range of cells based on value of another cell
In ExceI 2010, I want to highlight those cells in a range of cells which are less than the value of another cell. I have tried several formulas and nothing seems to work. Example formulas applied to the range of cells in K5:K94: =<$G$1 "=<$G$1" ="<$G$1" =<"$G$1" Any help would be appreciated, Barney On 12/07/2011 14:30, Barneypo@gmail.com wrote: > In ExceI 2010, I want to highlight those cells in a range of cells > which are less than the value of another cell. I have tried several > formulas and nothing seems to work. > > Exa...

how do i chart the data below
AAA ABB ACC 3/17/2006 4607 653 599 3/18/2006 4461 754 650 3/19/2006 2167 707 527 3/20/2006 4329 568 722 3/21/2006 4379 2302 842 3/22/2006 10156 1983 1027 3/23/2006 5338 1839 870 i want to show how the values AAA... are being used over the given dates Charter_SKR The easiest and quickest is to select your data range, press F11. This will generate a chart sheet. You can format,change chart type etc as you need. ...Kelly koday@processtends.com "charter_SKR" <charterSKR@discussions.microsoft.com> wrote in message news:6933B9D0-B6AC-48A3-80FF-C8D68B57B8C6@microsoft.com....

Convert Quicken Deluxe 2000 data to Money 2004
Got fed up with Intuit products. Got new computer & backed up Quicken Deluxe 2000 data to floppy disk. Will Quicken 2000 convert to Money 2004 ? The Money 2004 package does not show Quicken Deluxe 2000 Thanks Yes, Money should convert the account data in your Quicken file. -- Will you sponsor me in the Tour de Cure? http://main.diabetes.org/site/TR?pg=personal&fr_id=1058&px=1626087 "jy" <jy24165@earthlink.net> wrote in message news:04f901c35fa8$b971e720$a001280a@phx.gbl... > Got fed up with Intuit products. Got new computer & > backed up Quicken Del...

Prohibiting edits to data in a text box
This seems like it should be simple: Using a form property sheet Access allows me to specify "Allow Edits", "Allow Additions", and "Allow Deletions", but these pertain to entire records. I can't figure out how to "Allow Additions" and not "Allow Edits" to just one specific control on my form. If I use the "Lock" feature then the user can't populate the field of a new record. Any tips? -mjg In the forms current event If Me.NewRecord Then Me.ControlName.Locked = False Else Me.ControlName.Locked = True End...

Problem Changing an Investment Name
I am trying to change an investment name and Money 2006 tells me "The name or symbol 'TRP Spectrum Income' has already been used for a deleted investment. Please enter a different name." When I go to delete investments the name does not appear! Any ideas on how I get Money to accept the name change? This is the first time I have run into this situation and I have made numerous name changes in Money over the years. In microsoft.public.money, Ken wrote: >I am trying to change an investment name and Money 2006 tells me "The name >or symbol 'TRP Spec...

Transferring data IN VBA between workbooks
I need to transfer a range of cells for every row one by one from one file File1.xls to a second file File2.xls from within a VBA program. I had it running where it coded a cut and paste but the program runs for 3 hours and If I tried to do anything else where I cut and paste I would get data from my running program when I tried to paste. Is there any way I can do this other than cut and paste ? Dennis I foud out what I was doing wrong I had this Range("A1") = Range("A1:P1") which will work in a single workbook but to a separate workbook you have to put Range("...

How to copy a column of values into another column where the cells are merged
Hi, I have a sheet with a column that lists some values. I also have a second sheet that has a column where the cells are merged every 4 rows. I need to copy the values of the first sheet into the merged cells, but when I do, I only see every fourth value on the merged cells. Is there a formula or any other way that I can use to be able to copy all the values from the first sheet into the second sheet each value on a different group of merged cells? Thanks! Assume you have values in A1:A5 of Sheet1 that you want to copy into Sheet2, where A1:A4, A5:A8, A9;A12 etc are merged. Put this formul...