Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose to Non-adjacent Cells
I'm trying to transpose selected rows of data from a worksheet to columns on
multiple worksheets, however the transposed columnar data will be on non-adjacent rows. I loop through the rows, columns, and worksheets. Part of the code within the loops is: Sheets("Equip").Activate Range(Cells(m, 2), Cells(m, 5)).Copy Sheets(s).Cells(7, c).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True Sheets("Equip").Range(Cells(m, 7)).Copy Destination:=Sheets(s).Range(Cells(12, c)) The transpose part of the code works, but I don't know why the second part fails where I'm just trying to copy a single cell. Any help? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose to Non-adjacent Cells
I think the problem lies with the line
Sheets("Equip").Range(Cells(m, 7)).Copy _ Destination:=Sheets(s).Range(Cells(12, c)) Your Cells properties are not qualified to point to the proper worksheet. Without qualification, they point to the ActiveSheet, which may not be "Equip" or the sheet named by the variable 's'. Also, your use of Range with Cells is unnecessary. Try the following: Sheets("Equip").Cells(M, 7).Copy _ Destination:=Sheets(S).Cells(12, C) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "DennisW" wrote in message ... I'm trying to transpose selected rows of data from a worksheet to columns on multiple worksheets, however the transposed columnar data will be on non-adjacent rows. I loop through the rows, columns, and worksheets. Part of the code within the loops is: Sheets("Equip").Activate Range(Cells(m, 2), Cells(m, 5)).Copy Sheets(s).Cells(7, c).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True Sheets("Equip").Range(Cells(m, 7)).Copy Destination:=Sheets(s).Range(Cells(12, c)) The transpose part of the code works, but I don't know why the second part fails where I'm just trying to copy a single cell. Any help? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose to Non-adjacent Cells
Thanks for your help. I actually found a solution:
Sheets("Equip").Cells(m, 7).Copy Sheets(s).Cells(12, c).PasteSpecial Paste:=xlPasteValues Sorry for the inconvenience, I just started trying to learn VBA. "Chip Pearson" wrote: I think the problem lies with the line Sheets("Equip").Range(Cells(m, 7)).Copy _ Destination:=Sheets(s).Range(Cells(12, c)) Your Cells properties are not qualified to point to the proper worksheet. Without qualification, they point to the ActiveSheet, which may not be "Equip" or the sheet named by the variable 's'. Also, your use of Range with Cells is unnecessary. Try the following: Sheets("Equip").Cells(M, 7).Copy _ Destination:=Sheets(S).Cells(12, C) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "DennisW" wrote in message ... I'm trying to transpose selected rows of data from a worksheet to columns on multiple worksheets, however the transposed columnar data will be on non-adjacent rows. I loop through the rows, columns, and worksheets. Part of the code within the loops is: Sheets("Equip").Activate Range(Cells(m, 2), Cells(m, 5)).Copy Sheets(s).Cells(7, c).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True Sheets("Equip").Range(Cells(m, 7)).Copy Destination:=Sheets(s).Range(Cells(12, c)) The transpose part of the code works, but I don't know why the second part fails where I'm just trying to copy a single cell. Any help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare 2 column cells and return the adjacent columns cells data of the cell | Excel Worksheet Functions | |||
Compare 1 cell to column of cells returning adjacent cells info? | Excel Worksheet Functions | |||
Add cells on two adjacent rows but non-adjacent columns | Excel Worksheet Functions | |||
transpose array of non-adjacent cells | Excel Worksheet Functions | |||
Transpose a text string while copying adjacent column data to new | Excel Worksheet Functions |