I need to transpose some data that I bring in via a microsoft query, however
when I do the copy and special paste it works properly the first time,
whenever i update the query, the transposed special paste goes back to the
orginal format. Is there anyway to do this?

Terry

hi
how are you updating the query now? how big is it? were are you transposing
it?
a tad short on details but you might try something like this code.

Sub TransposeMSQ()
Sheets("Sheet1").Activate
Range("A1").QueryTable.Refresh BackgroundQuery = False
Range("A1:A50").Copy
Range("L1").PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End Sub

you might need to adjust the ranges to fit your data. the code can be
improved on if i have more details. right now i can only give vaque
possiblities.
you could run the code from macros are attach it to a command button on the
sheet.

regards
FSt1

"tmacdonald" wrote:

I need to transpose some data that I bring in via a microsoft query, however
when I do the copy and special paste it works properly the first time,
whenever i update the query, the transposed special paste goes back to the
orginal format. Is there anyway to do this?

Terry

The query automatically updates based upon 2 parameters, (start date) & (end
date), the query brings in 4 rows normally each day, however there is a
possibility for additional rows (1 to 4 more rows) to be added without any
advanced notification. The query is designed to pull in 1 months worth of
data, however if need be the data can be expanded to more than 1 month of
less than a month.

There are 4 rows normally and 59 columns. I have tried your code an it
works, however it is copying some of the data before it is transposed and is
placing it below the transposed data, is there anyway to correct this?

Any further assistance would be greatly welcomed

Terry

"FSt1" wrote:

hi
how are you updating the query now? how big is it? were are you transposing
it?
a tad short on details but you might try something like this code.

Sub TransposeMSQ()
Sheets("Sheet1").Activate
Range("A1").QueryTable.Refresh BackgroundQuery = False
Range("A1:A50").Copy
Range("L1").PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End Sub

you might need to adjust the ranges to fit your data. the code can be
improved on if i have more details. right now i can only give vaque
possiblities.
you could run the code from macros are attach it to a command button on the
sheet.

regards
FSt1

"tmacdonald" wrote:

I need to transpose some data that I bring in via a microsoft query, however
when I do the copy and special paste it works properly the first time,
whenever i update the query, the transposed special paste goes back to the
orginal format. Is there anyway to do this?

Terry