| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying some rows and columns
Hi all,
Of sheet 1, rows 2 tot 252 and columns A to AR I want to copy columns A to C to sheet 2 colums A to C rows 2 and further, column D to sheet 2 colum N rows 2 and further, column AA to sheet 2 column O rows 2 and further, columns AK and AL to sheet 2 colums D and E rows 2 and further, column AM to sheet 2 column G rows 2 and further, column AN to sheet 2 column F rows 2 and further, column AO to sheet 2 column J rows 2 and further, column AP to sheet 2 column H rows 2 and further, column AR to sheet 2 column I rows 2 and further, and all that only for those rows where in column DM is not an "x". Formats, text colors and interior colors are also to be copied. Due to clumsy code (sorry for that) I can't change the order of the columns in sheet 1 without getting an awful lot of trouble. I'm looking for nice compact and fast executing code, your assistance will be appreciated. Jack Sons The Netherlands |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
copying some rows and columns
Hi
I think this is what you need: Sub aaa() Dim TargetSh As Worksheet Dim DestSh As Worksheet Dim LastRow As Long Application.ScreenUpdating = False Set TargetSh = Worksheets("Sheet1") Set DestSh = Worksheets("Sheet2") LastRow = 252 With TargetSh .Range("DM1:DM" & LastRow).AutoFilter field:=1, Criteria1:="<x" .Range("A2:C" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("A2") .Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("N2") .Range("AA2:AA" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("O2") .Range("AK2:AL" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("D2") .Range("AM2:AM" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("G2") .Range("AN2:AN" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("F2") .Range("AO2:AO" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("J2") .Range("AP2:AP" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("H2") .Range("AR2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("I2") .Range("DM1:DM" & LastRow).AutoFilter End With Application.ScreenUpdating = True End Sub Regards, Per "Jack Sons" skrev i meddelelsen ... Hi all, Of sheet 1, rows 2 tot 252 and columns A to AR I want to copy columns A to C to sheet 2 colums A to C rows 2 and further, column D to sheet 2 colum N rows 2 and further, column AA to sheet 2 column O rows 2 and further, columns AK and AL to sheet 2 colums D and E rows 2 and further, column AM to sheet 2 column G rows 2 and further, column AN to sheet 2 column F rows 2 and further, column AO to sheet 2 column J rows 2 and further, column AP to sheet 2 column H rows 2 and further, column AR to sheet 2 column I rows 2 and further, and all that only for those rows where in column DM is not an "x". Formats, text colors and interior colors are also to be copied. Due to clumsy code (sorry for that) I can't change the order of the columns in sheet 1 without getting an awful lot of trouble. I'm looking for nice compact and fast executing code, your assistance will be appreciated. Jack Sons The Netherlands |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
copying some rows and columns
Per,
Thank you. So much of my own clumsy code I can now discard! I really learned a thing or two (or more)! What if I also want to bring the cells in row 1 of the columns mentioned to the destination sheet and I do not want the textboxes that exist in row 1 of the targetsheet to be copied to the destination sheet? Jack. "Per Jessen" schreef in bericht ... Hi I think this is what you need: Sub aaa() Dim TargetSh As Worksheet Dim DestSh As Worksheet Dim LastRow As Long Application.ScreenUpdating = False Set TargetSh = Worksheets("Sheet1") Set DestSh = Worksheets("Sheet2") LastRow = 252 With TargetSh .Range("DM1:DM" & LastRow).AutoFilter field:=1, Criteria1:="<x" .Range("A2:C" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("A2") .Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("N2") .Range("AA2:AA" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("O2") .Range("AK2:AL" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("D2") .Range("AM2:AM" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("G2") .Range("AN2:AN" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("F2") .Range("AO2:AO" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("J2") .Range("AP2:AP" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("H2") .Range("AR2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("I2") .Range("DM1:DM" & LastRow).AutoFilter End With Application.ScreenUpdating = True End Sub Regards, Per "Jack Sons" skrev i meddelelsen ... Hi all, Of sheet 1, rows 2 tot 252 and columns A to AR I want to copy columns A to C to sheet 2 colums A to C rows 2 and further, column D to sheet 2 colum N rows 2 and further, column AA to sheet 2 column O rows 2 and further, columns AK and AL to sheet 2 colums D and E rows 2 and further, column AM to sheet 2 column G rows 2 and further, column AN to sheet 2 column F rows 2 and further, column AO to sheet 2 column J rows 2 and further, column AP to sheet 2 column H rows 2 and further, column AR to sheet 2 column I rows 2 and further, and all that only for those rows where in column DM is not an "x". Formats, text colors and interior colors are also to be copied. Due to clumsy code (sorry for that) I can't change the order of the columns in sheet 1 without getting an awful lot of trouble. I'm looking for nice compact and fast executing code, your assistance will be appreciated. Jack Sons The Netherlands |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
copying some rows and columns
Jack,
I am glad you learned a bit from my code. If you change the code to copy row 1 also and paste starting in row 1, the textbox will not be copied, neither will the content of the textbox. You can place the content of the textbox in the underlying cell. To do that, enter design mode, and right click a textbox Properties Find LinkedCell property, and enter the cell address, eg. A1, then you just change the code to copy/paste starting at row 1 .Range("A1:C" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("A1") Hopes this helps. .... Per "Jack Sons" skrev i meddelelsen ... Per, Thank you. So much of my own clumsy code I can now discard! I really learned a thing or two (or more)! What if I also want to bring the cells in row 1 of the columns mentioned to the destination sheet and I do not want the textboxes that exist in row 1 of the targetsheet to be copied to the destination sheet? Jack. "Per Jessen" schreef in bericht ... Hi I think this is what you need: Sub aaa() Dim TargetSh As Worksheet Dim DestSh As Worksheet Dim LastRow As Long Application.ScreenUpdating = False Set TargetSh = Worksheets("Sheet1") Set DestSh = Worksheets("Sheet2") LastRow = 252 With TargetSh .Range("DM1:DM" & LastRow).AutoFilter field:=1, Criteria1:="<x" .Range("A2:C" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("A2") .Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("N2") .Range("AA2:AA" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("O2") .Range("AK2:AL" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("D2") .Range("AM2:AM" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("G2") .Range("AN2:AN" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("F2") .Range("AO2:AO" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("J2") .Range("AP2:AP" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("H2") .Range("AR2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("I2") .Range("DM1:DM" & LastRow).AutoFilter End With Application.ScreenUpdating = True End Sub Regards, Per "Jack Sons" skrev i meddelelsen ... Hi all, Of sheet 1, rows 2 tot 252 and columns A to AR I want to copy columns A to C to sheet 2 colums A to C rows 2 and further, column D to sheet 2 colum N rows 2 and further, column AA to sheet 2 column O rows 2 and further, columns AK and AL to sheet 2 colums D and E rows 2 and further, column AM to sheet 2 column G rows 2 and further, column AN to sheet 2 column F rows 2 and further, column AO to sheet 2 column J rows 2 and further, column AP to sheet 2 column H rows 2 and further, column AR to sheet 2 column I rows 2 and further, and all that only for those rows where in column DM is not an "x". Formats, text colors and interior colors are also to be copied. Due to clumsy code (sorry for that) I can't change the order of the columns in sheet 1 without getting an awful lot of trouble. I'm looking for nice compact and fast executing code, your assistance will be appreciated. Jack Sons The Netherlands |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
copying some rows and columns
Per,
If you change the code to copy row 1 also and paste starting in row 1 I did this With TargetSh .Range("DM1:DM" & eindrij).AutoFilter field:=1, Criteria1:="<x" .Range("A1:C" & eindrij).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("A1") .Range("F1:F" & eindrij).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("N1") .Range("AA1:AA" & eindrij).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("O1") ... .Range("DM1:DM" & eindrij).AutoFilter End With but then the text boxes come with the cells of row 1 of TargetSh. I got rid of them with DestSh.Shapes("resteert").Select Selection.Delete DestSh.Shapes("betaald").Select Selection.Delete DestSh.Shapes("legenda_1").Select Selection.Delete DestSh.Shapes("L").Select Selection.Delete which is rather clumsy, I'm afraid (better: I'm sure). What now? Jack. "Per Jessen" schreef in bericht ... Jack, I am glad you learned a bit from my code. If you change the code to copy row 1 also and paste starting in row 1, the textbox will not be copied, neither will the content of the textbox. You can place the content of the textbox in the underlying cell. To do that, enter design mode, and right click a textbox Properties Find LinkedCell property, and enter the cell address, eg. A1, then you just change the code to copy/paste starting at row 1 .Range("A1:C" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("A1") Hopes this helps. ... Per "Jack Sons" skrev i meddelelsen ... Per, Thank you. So much of my own clumsy code I can now discard! I really learned a thing or two (or more)! What if I also want to bring the cells in row 1 of the columns mentioned to the destination sheet and I do not want the textboxes that exist in row 1 of the targetsheet to be copied to the destination sheet? Jack. "Per Jessen" schreef in bericht ... Hi I think this is what you need: Sub aaa() Dim TargetSh As Worksheet Dim DestSh As Worksheet Dim LastRow As Long Application.ScreenUpdating = False Set TargetSh = Worksheets("Sheet1") Set DestSh = Worksheets("Sheet2") LastRow = 252 With TargetSh .Range("DM1:DM" & LastRow).AutoFilter field:=1, Criteria1:="<x" .Range("A2:C" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("A2") .Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("N2") .Range("AA2:AA" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("O2") .Range("AK2:AL" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("D2") .Range("AM2:AM" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("G2") .Range("AN2:AN" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("F2") .Range("AO2:AO" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("J2") .Range("AP2:AP" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("H2") .Range("AR2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("I2") .Range("DM1:DM" & LastRow).AutoFilter End With Application.ScreenUpdating = True End Sub Regards, Per "Jack Sons" skrev i meddelelsen ... Hi all, Of sheet 1, rows 2 tot 252 and columns A to AR I want to copy columns A to C to sheet 2 colums A to C rows 2 and further, column D to sheet 2 colum N rows 2 and further, column AA to sheet 2 column O rows 2 and further, columns AK and AL to sheet 2 colums D and E rows 2 and further, column AM to sheet 2 column G rows 2 and further, column AN to sheet 2 column F rows 2 and further, column AO to sheet 2 column J rows 2 and further, column AP to sheet 2 column H rows 2 and further, column AR to sheet 2 column I rows 2 and further, and all that only for those rows where in column DM is not an "x". Formats, text colors and interior colors are also to be copied. Due to clumsy code (sorry for that) I can't change the order of the columns in sheet 1 without getting an awful lot of trouble. I'm looking for nice compact and fast executing code, your assistance will be appreciated. Jack Sons The Netherlands |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
copying some rows and columns
Jack,
You are close, but no need to select the shapes: DestSh.Shapes("resteert").Delete DestSh.Shapes("betaald").Delete DestSh.Shapes("legenda_1").Delete DestSh.Shapes("L").Delete Per "Jack Sons" skrev i meddelelsen ... Per, If you change the code to copy row 1 also and paste starting in row 1 I did this With TargetSh .Range("DM1:DM" & eindrij).AutoFilter field:=1, Criteria1:="<x" .Range("A1:C" & eindrij).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("A1") .Range("F1:F" & eindrij).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("N1") .Range("AA1:AA" & eindrij).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("O1") ... .Range("DM1:DM" & eindrij).AutoFilter End With but then the text boxes come with the cells of row 1 of TargetSh. I got rid of them with DestSh.Shapes("resteert").Select Selection.Delete DestSh.Shapes("betaald").Select Selection.Delete DestSh.Shapes("legenda_1").Select Selection.Delete DestSh.Shapes("L").Select Selection.Delete which is rather clumsy, I'm afraid (better: I'm sure). What now? Jack. "Per Jessen" schreef in bericht ... Jack, I am glad you learned a bit from my code. If you change the code to copy row 1 also and paste starting in row 1, the textbox will not be copied, neither will the content of the textbox. You can place the content of the textbox in the underlying cell. To do that, enter design mode, and right click a textbox Properties Find LinkedCell property, and enter the cell address, eg. A1, then you just change the code to copy/paste starting at row 1 .Range("A1:C" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("A1") Hopes this helps. ... Per "Jack Sons" skrev i meddelelsen ... Per, Thank you. So much of my own clumsy code I can now discard! I really learned a thing or two (or more)! What if I also want to bring the cells in row 1 of the columns mentioned to the destination sheet and I do not want the textboxes that exist in row 1 of the targetsheet to be copied to the destination sheet? Jack. "Per Jessen" schreef in bericht ... Hi I think this is what you need: Sub aaa() Dim TargetSh As Worksheet Dim DestSh As Worksheet Dim LastRow As Long Application.ScreenUpdating = False Set TargetSh = Worksheets("Sheet1") Set DestSh = Worksheets("Sheet2") LastRow = 252 With TargetSh .Range("DM1:DM" & LastRow).AutoFilter field:=1, Criteria1:="<x" .Range("A2:C" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("A2") .Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("N2") .Range("AA2:AA" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("O2") .Range("AK2:AL" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("D2") .Range("AM2:AM" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("G2") .Range("AN2:AN" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("F2") .Range("AO2:AO" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("J2") .Range("AP2:AP" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("H2") .Range("AR2:AR" & LastRow).SpecialCells(xlCellTypeVisible).Copy DestSh.Range("I2") .Range("DM1:DM" & LastRow).AutoFilter End With Application.ScreenUpdating = True End Sub Regards, Per "Jack Sons" skrev i meddelelsen ... Hi all, Of sheet 1, rows 2 tot 252 and columns A to AR I want to copy columns A to C to sheet 2 colums A to C rows 2 and further, column D to sheet 2 colum N rows 2 and further, column AA to sheet 2 column O rows 2 and further, columns AK and AL to sheet 2 colums D and E rows 2 and further, column AM to sheet 2 column G rows 2 and further, column AN to sheet 2 column F rows 2 and further, column AO to sheet 2 column J rows 2 and further, column AP to sheet 2 column H rows 2 and further, column AR to sheet 2 column I rows 2 and further, and all that only for those rows where in column DM is not an "x". Formats, text colors and interior colors are also to be copied. Due to clumsy code (sorry for that) I can't change the order of the columns in sheet 1 without getting an awful lot of trouble. I'm looking for nice compact and fast executing code, your assistance will be appreciated. Jack Sons The Netherlands |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copying multiple rows to columns | Excel Discussion (Misc queries) | |||
| Copying Columns of Data to Rows | Excel Discussion (Misc queries) | |||
| copying information from rows to columns | Excel Discussion (Misc queries) | |||
| Copying data from one worksheet to another from rows to columns | Excel Worksheet Functions | |||
| Copying from columns to Rows | Excel Discussion (Misc queries) | |||
Hybrid Mode
