| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to select cells in specific Columns?
I would like to select cells of specific columns in each row and past them
into another sheet. The codes I have now is following. -------------------------------------- Dim JGBValueDate As Date JGBValueDate = Sheets("JGB Coupon Template").Range("B1").Value Sheets("JGB Coupon Sort").Select Range("A2").Select Do Until ActiveCell.Value = "" If ActiveCell.Offset(0, 15).Value = JGBValueDate Then Application.CutCopyMode = False Range("A2,D2,E2,F2,G2,L2,M2,N2,V2,W2").Select selection.Copy Sheets("JGB Coupon Template").Select Range("A4").Select selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else End If ActiveCell.Offset(1, 0).Select Loop -------------------------------------------- Because of the code, Range("A2,D2,E2,F2,G2,L2,M2,N2,V2,W2").Select, I think I am pasting the cells in the 2nd Row only... Could you show me the codes I need to select cells of specific columns in multiple rows and past them into another sheet.? Thank you for your help. nk |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to select cells in specific Columns?
Try this:
Dim JGBValueDate As Date JGBValueDate = Sheets("JGB Coupon Template").Range("B1").Value Sheets("JGB Coupon Sort").Select Range("A2").Select ToRowOffset = 0 'number of rows copied TO 'JGB Coupon Template' Do Until ActiveCell.Value = "" If ActiveCell.Offset(0, 15).Value = JGBValueDate Then FromRowOffset = ActiveCell.Row - 2 'The offset from Row 2 of the ' row being copied FROM (row 2 = 0) Range("A2,D2:G2,L2:N2,V2:W2").Offset(FromRowOffset , 0).Copy Sheets("JGB Coupon Template").Range("A4").Offset(ToRowOffset, 0).PasteSpecial _ Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False ToRowOffset = ToRowOffset + 1 End If ActiveCell.Offset(1, 0).Select Loop -- p45cal "nk" wrote: I would like to select cells of specific columns in each row and past them into another sheet. The codes I have now is following. -------------------------------------- Dim JGBValueDate As Date JGBValueDate = Sheets("JGB Coupon Template").Range("B1").Value Sheets("JGB Coupon Sort").Select Range("A2").Select Do Until ActiveCell.Value = "" If ActiveCell.Offset(0, 15).Value = JGBValueDate Then Application.CutCopyMode = False Range("A2,D2,E2,F2,G2,L2,M2,N2,V2,W2").Select selection.Copy Sheets("JGB Coupon Template").Select Range("A4").Select selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else End If ActiveCell.Offset(1, 0).Select Loop -------------------------------------------- Because of the code, Range("A2,D2,E2,F2,G2,L2,M2,N2,V2,W2").Select, I think I am pasting the cells in the 2nd Row only... Could you show me the codes I need to select cells of specific columns in multiple rows and past them into another sheet.? Thank you for your help. nk |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to select cells in specific Columns?
Hello,
Thank you for the codes. It worked perfectly. nk "p45cal" wrote: Try this: Dim JGBValueDate As Date JGBValueDate = Sheets("JGB Coupon Template").Range("B1").Value Sheets("JGB Coupon Sort").Select Range("A2").Select ToRowOffset = 0 'number of rows copied TO 'JGB Coupon Template' Do Until ActiveCell.Value = "" If ActiveCell.Offset(0, 15).Value = JGBValueDate Then FromRowOffset = ActiveCell.Row - 2 'The offset from Row 2 of the ' row being copied FROM (row 2 = 0) Range("A2,D2:G2,L2:N2,V2:W2").Offset(FromRowOffset , 0).Copy Sheets("JGB Coupon Template").Range("A4").Offset(ToRowOffset, 0).PasteSpecial _ Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False ToRowOffset = ToRowOffset + 1 End If ActiveCell.Offset(1, 0).Select Loop -- p45cal "nk" wrote: I would like to select cells of specific columns in each row and past them into another sheet. The codes I have now is following. -------------------------------------- Dim JGBValueDate As Date JGBValueDate = Sheets("JGB Coupon Template").Range("B1").Value Sheets("JGB Coupon Sort").Select Range("A2").Select Do Until ActiveCell.Value = "" If ActiveCell.Offset(0, 15).Value = JGBValueDate Then Application.CutCopyMode = False Range("A2,D2,E2,F2,G2,L2,M2,N2,V2,W2").Select selection.Copy Sheets("JGB Coupon Template").Select Range("A4").Select selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Else End If ActiveCell.Offset(1, 0).Select Loop -------------------------------------------- Because of the code, Range("A2,D2,E2,F2,G2,L2,M2,N2,V2,W2").Select, I think I am pasting the cells in the 2nd Row only... Could you show me the codes I need to select cells of specific columns in multiple rows and past them into another sheet.? Thank you for your help. nk |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I select specific cells to be my tab stops? | Excel Worksheet Functions | |||
| select cells that sum up to a specific value | Excel Discussion (Misc queries) | |||
| I need a specific value in auto filter to fill a combo box and than match the select value in 2 other columns. | Excel Worksheet Functions | |||
| macro to select cells containing specific text and delete all cells but these | Excel Programming | |||
| Select specific columns for a given cell selection | Excel Programming | |||
Linear Mode
