PDA

View Full Version : copy paste transpose multiple sheets onto one sheet



TGwilding
08-18-2005, 12:44 PM
Hello,
I am trying to write a macro that will take a sheet from another workbook, copy, paste values and transpose the entire first row (A1:IV1) of a certain sheet(sheet1) into column A (A1:A256) of a new workbook (NewWorkbookSheet1). I would then like to take range A123:A372 (dates in sheet1) and copy, paste values, transpose in range B1:IQ1 of NewWorkbookSheet1. I would then like to copy, paste values and transpose range B123:B372 of sheet1 into B2:IQ2 of NewWorkbookSheet1, and do the same for C123:C372 of sheet1 into B3:IQ3 of NewWorkbookSheet1, and do the same for D123:D372 of sheet1 into B4:IQ4 of NewWorkbookSheet1, and so forth until IV123:IV372 is transposed into B256:IQ256.

Once this process is complete, I want the macro to essentially do the same, but pull info from another sheet and paste into NewWorkbookSheet1. So the new command would be copy, paste values and transpose A1:IV1 of sheet2 into A257:A511 and then commence copy, paste, transpose B123:B372 sheet2 into B257:IQ257 of NewWorkbookSheet1, and then C123:C372 sheet2 into B258:IQ258 of NewWorkbookSheet1.
Can anyone help me get started on this. I have little VBA knowledge and would like to learn.
thanks to anyone who can lend a hand

MOS MASTER
08-18-2005, 12:47 PM
Hi, :yes

I moved your thread to the right forum. :whistle:

TGwilding
08-19-2005, 10:15 AM
I am working with something that looks like this.

Sub PullinREturns()
G:\Portfolio Mgmt - Due Diligence\Toolbox\_
[Manager Analysis_new.xls] CurrentMgrs '!.Activate
'would like it to reference this workbook sheet CurrentMgrs. This line is not working

Rows("1:1").Select
Selection.Copy
Windows("Book1").Activate
'would like it to reference the workbook i am working in.The same workbook
'where the macro is

Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
G:\Portfolio Mgmt - Due Diligence\Toolbox\_
[Manager Analysis_new.xls] CurrentMgrs '!.Activate
'would like it to reference this workbook sheet CurrentMgrs. This line is not working



Rows("123:372").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
'would like it to reference the workbook i am working in.The same workbook
'where the macro is

Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End Sub

Can anyone help me with this
much thanks

Zack Barresse
08-19-2005, 10:17 AM
some info for ya I took the liberty to add them myself, hope you don't mind.