|
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 |
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 |
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 |
| All times are GMT +1. The time now is 09:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com