Transpose a column list to every other cell in a row on anothersheet, then center
I want to transpose a column list of names to every other cell in a row on another sheet and then "center across selection" each name with the cell it is in plus the cell to the right. The list on sheet 1 will vary from 4 to 10, 20 names. So on sheet 3 cells D4 and E4 would have a name centered on them, cells F4 and G4 would have the next name centered on them and so on for all the names in the row. The previous transposed & centered names need to be cleared and the new transposed set of names overwritten to cell D4 each time the code is run. This code puts the names in the correct spot on sheet 3, but all in a continuous row. Thanks, Howard Sub Name_Hours_OPNumber() Dim nhoRng As Range Set nhoRng = Sheets("Sheet1").Range("D7:D" & Cells(Rows.Count, "D").End(xlUp).Row) nhoRng.Copy Sheets("Sheet3").Range("D4").PasteSpecial Paste:=xlPasteAll, Transpose:=True End Sub |
Transpose a column list to every other cell in a row on another sheet, then center
Hi Howard,
Am Thu, 13 Aug 2015 22:49:29 -0700 (PDT) schrieb L. Howard: I want to transpose a column list of names to every other cell in a row on another sheet and then "center across selection" each name with the cell it is in plus the cell to the right. The list on sheet 1 will vary from 4 to 10, 20 names. try: Sub Name_Hours_OPNumber() Dim vardata As Variant Dim i As Long, n As Long Application.ScreenUpdating = False With Sheets("Sheet1") vardata = .Range("D7:D" & .Cells(Rows.Count, "D").End(xlUp).Row) End With n = 4 For i = LBound(vardata) To UBound(vardata) With Sheets("Sheet3") .Activate .Cells(4, n) = vardata(i, 1) .Range(Cells(4, n), Cells(4, n + 1)).Select Selection.HorizontalAlignment = xlCenterAcrossSelection End With n = n + 2 Next Application.ScreenUpdating = True End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
Transpose a column list to every other cell in a row on anothersheet, then center
try: Sub Name_Hours_OPNumber() Dim vardata As Variant Dim i As Long, n As Long Application.ScreenUpdating = False With Sheets("Sheet1") vardata = .Range("D7:D" & .Cells(Rows.Count, "D").End(xlUp).Row) End With n = 4 For i = LBound(vardata) To UBound(vardata) With Sheets("Sheet3") .Activate .Cells(4, n) = vardata(i, 1) .Range(Cells(4, n), Cells(4, n + 1)).Select Selection.HorizontalAlignment = xlCenterAcrossSelection End With n = n + 2 Next Application.ScreenUpdating = True End Sub Regards Claus B. -- Hi Claus, Very nice! Works excellent. Thank you. I added some lines to do additional stuff. Do you see any glaring errors? It does exactly what I want it to do, but may not be best coding practices. Howard Sub Name_Hours_OPNumber() Dim vardata As Variant Dim i As Long, n As Long Application.ScreenUpdating = False With Sheets("Sheet3").Range("4:5") .ClearContents .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True End With With Sheets("Sheet1") vardata = .Range("D7:D" & .Cells(Rows.Count, "D").End(xlUp).Row) End With n = 4 For i = LBound(vardata) To UBound(vardata) With Sheets("Sheet3") .Activate .Cells(4, n) = vardata(i, 1) .Cells(5, n) = "Hours" .Cells(5, n + 1) = "OP Number" .Range(Cells(4, n), Cells(4, n + 1)).Select Selection.HorizontalAlignment = xlCenterAcrossSelection End With n = n + 2 Next [D6].Activate Application.ScreenUpdating = True End Sub |
Transpose a column list to every other cell in a row on another sheet, then center
Hi Howard,
Am Fri, 14 Aug 2015 03:34:58 -0700 (PDT) schrieb L. Howard: I added some lines to do additional stuff. Do you see any glaring errors? It does exactly what I want it to do, but may not be best coding practices. no, every thing is okay. But delete [D6].Activate at the end of the code It's not good practice and also superfluous. If you want go back to D6 then try it with Application.Goto range("D6") Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
Transpose a column list to every other cell in a row on anothersheet, then center
On Friday, August 14, 2015 at 3:58:18 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Fri, 14 Aug 2015 03:34:58 -0700 (PDT) schrieb L. Howard: I added some lines to do additional stuff. Do you see any glaring errors? It does exactly what I want it to do, but may not be best coding practices. no, every thing is okay. But delete [D6].Activate at the end of the code It's not good practice and also superfluous. If you want go back to D6 then try it with Application.Goto range("D6") Regards Claus B. Okay, and thanks again. Howard |
All times are GMT +1. The time now is 10:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com