| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Multiple non adjacent columns from one Worksheet to another
Hi guys,
I am trying to create a macro that would Copy Columns A-H,K,M,N,S,U from worksheet("DrList") to WorkSheet("DrListCal") within the same workbook. I know how to select ajacent columns but how do we deal with non ajacent ones? Would appreciate some help codding. Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Multiple non adjacent columns from one Worksheet to another
Hi.
Range("A:H,K:K,M:M,N:N,S:S,U:U") Note that you may have trouble with pasting not adjacent columns. Regards. Daniel Hi guys, I am trying to create a macro that would Copy Columns A-H,K,M,N,S,U from worksheet("DrList") to WorkSheet("DrListCal") within the same workbook. I know how to select ajacent columns but how do we deal with non ajacent ones? Would appreciate some help codding. Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Multiple non adjacent columns from one Worksheet to anoth
Thanks Daniel,
I have one more complication. I need to select ranges A15 to Last cell Column H that has entry, K15 to Last cell in Column K, M15 to Last cell in column M......... Possible to do a one line range reference to this multiple ranges? I tried: Dim Lr As Long Lr = LastRow(DrList) Range(A15:H"&Lr,K15:K"&Lr, M15:M"&Lr,............).select but I got a syntax error. Would really appreciate some help. "Daniel.C" wrote: Hi. Range("A:H,K:K,M:M,N:N,S:S,U:U") Note that you may have trouble with pasting not adjacent columns. Regards. Daniel Hi guys, I am trying to create a macro that would Copy Columns A-H,K,M,N,S,U from worksheet("DrList") to WorkSheet("DrListCal") within the same workbook. I know how to select ajacent columns but how do we deal with non ajacent ones? Would appreciate some help codding. Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Multiple non adjacent columns from one Worksheet to anoth
Union(Range("A15:H" & Lr), Range("K15:K" & Lr), Range("M15:M" &
Lr)).Select Daniel Thanks Daniel, I have one more complication. I need to select ranges A15 to Last cell Column H that has entry, K15 to Last cell in Column K, M15 to Last cell in column M......... Possible to do a one line range reference to this multiple ranges? I tried: Dim Lr As Long Lr = LastRow(DrList) Range(A15:H"&Lr,K15:K"&Lr, M15:M"&Lr,............).select but I got a syntax error. Would really appreciate some help. "Daniel.C" wrote: Hi. Range("A:H,K:K,M:M,N:N,S:S,U:U") Note that you may have trouble with pasting not adjacent columns. Regards. Daniel Hi guys, I am trying to create a macro that would Copy Columns A-H,K,M,N,S,U from worksheet("DrList") to WorkSheet("DrListCal") within the same workbook. I know how to select ajacent columns but how do we deal with non ajacent ones? Would appreciate some help codding. Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Multiple non adjacent columns from one Worksheet to anoth
Daniel
You were right about problems pasting non adjacent columns. Decided that I will define each range separately and paste them one after the other. The following works for copying the first range "ColAH". Q: How do I code so that it continues to copy and paste the next ranges ColK, ColM and so on.... to the next column in destination sheet? my macro looks like this: Sub CopyDrList() Dim TargetWks As Worksheet, SourceWks As Worksheet Dim TargetCol As Long Dim ColAH As Range, ColK As Range, ColM As Range Dim ColN As Range, ColS As Range, ColU As Range Set SourceWks = Sheets("DrList") Set TargetWks = Sheets("DrListCal") Set ColAH = SourceWks.Range("A15:H" & SourceWks.Range("H65536").End(xlUp).Row) Set ColK = SourceWks.Range("K15:K" & SourceWks.Range("K65536").End(xlUp).Row) Set ColM = SourceWks.Range("M15:M" & SourceWks.Range("M65536").End(xlUp).Row) Set ColN = SourceWks.Range("N15:N" & SourceWks.Range("N65536").End(xlUp).Row) Set ColS = SourceWks.Range("S15:S" & SourceWks.Range("S65536").End(xlUp).Row) SetColU = SourceWks.Range("U15:U" & SourceWks.Range("U65536").End(xlUp).Row) 'This is finding the next column available in the target sheet. If TargetWks.Range("A1").Value = "" Then 'Cell A1 is blank so the column to put data in will be column #1 (ie A) TargetCol = 1 Else 'cell A1 does have data so find the next available column TargetCol = TargetWks.Range("IV1").End(xlToLeft).Column + 1 End If 'Start copying data in the first range from the source sheet to the target sheet ColAH.Copy TargetWks.Cells(1, TargetCol) 'How do we continue copying the next range (ie: ColK, ColM€¦. ColU) into the next empty column in target sheet? Exit Sub End Sub "Daniel.C" wrote: Hi. Range("A:H,K:K,M:M,N:N,S:S,U:U") Note that you may have trouble with pasting not adjacent columns. Regards. Daniel Hi guys, I am trying to create a macro that would Copy Columns A-H,K,M,N,S,U from worksheet("DrList") to WorkSheet("DrListCal") within the same workbook. I know how to select ajacent columns but how do we deal with non ajacent ones? Would appreciate some help codding. Thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Multiple non adjacent columns from one Worksheet to anoth
Try :
Sub CopyDrList() Dim TargetWks As Worksheet, SourceWks As Worksheet Dim TargetCol As Long Dim ColAH As Range, ColK As Range, ColM As Range Dim ColN As Range, ColS As Range, ColU As Range Set SourceWks = Sheets("DrList") Set TargetWks = Sheets("DrListCal") Set ColAH = SourceWks.Range("A15:H" & SourceWks.Range("H65536").End(xlUp).Row) Set ColK = SourceWks.Range("K15:K" & SourceWks.Range("K65536").End(xlUp).Row) Set ColM = SourceWks.Range("M15:M" & SourceWks.Range("M65536").End(xlUp).Row) Set ColN = SourceWks.Range("N15:N" & SourceWks.Range("N65536").End(xlUp).Row) Set ColS = SourceWks.Range("S15:S" & SourceWks.Range("S65536").End(xlUp).Row) Set ColU = SourceWks.Range("U15:U" & SourceWks.Range("U65536").End(xlUp).Row) 'This is finding the next column available in the target sheet. If TargetWks.Range("A1").Value = "" Then 'Cell A1 is blank so the column to put data in will be column #1 (ie A) TargetCol = 1 Else 'cell A1 does have data so find the next available column TargetCol = TargetWks.Range("IV1").End(xlToLeft).Column + 1 End If 'Start copying data in the first range from the source sheet to the target Sheet ColAH.Copy TargetWks.Cells(1, 1) ColK.Copy TargetWks.Cells(1, TargetWks.Range("IV1").End(xlToLeft).Column + 1) ColM.Copy TargetWks.Cells(1, TargetWks.Range("IV1").End(xlToLeft).Column + 1) ColN.Copy TargetWks.Cells(1, TargetWks.Range("IV1").End(xlToLeft).Column + 1) ColS.Copy TargetWks.Cells(1, TargetWks.Range("IV1").End(xlToLeft).Column + 1) ColU.Copy TargetWks.Cells(1, TargetWks.Range("IV1").End(xlToLeft).Column + 1) End Sub Daniel Daniel You were right about problems pasting non adjacent columns. Decided that I will define each range separately and paste them one after the other. The following works for copying the first range "ColAH". Q: How do I code so that it continues to copy and paste the next ranges ColK, ColM and so on.... to the next column in destination sheet? my macro looks like this: Sub CopyDrList() Dim TargetWks As Worksheet, SourceWks As Worksheet Dim TargetCol As Long Dim ColAH As Range, ColK As Range, ColM As Range Dim ColN As Range, ColS As Range, ColU As Range Set SourceWks = Sheets("DrList") Set TargetWks = Sheets("DrListCal") Set ColAH = SourceWks.Range("A15:H" & SourceWks.Range("H65536").End(xlUp).Row) Set ColK = SourceWks.Range("K15:K" & SourceWks.Range("K65536").End(xlUp).Row) Set ColM = SourceWks.Range("M15:M" & SourceWks.Range("M65536").End(xlUp).Row) Set ColN = SourceWks.Range("N15:N" & SourceWks.Range("N65536").End(xlUp).Row) Set ColS = SourceWks.Range("S15:S" & SourceWks.Range("S65536").End(xlUp).Row) SetColU = SourceWks.Range("U15:U" & SourceWks.Range("U65536").End(xlUp).Row) 'This is finding the next column available in the target sheet. If TargetWks.Range("A1").Value = "" Then 'Cell A1 is blank so the column to put data in will be column #1 (ie A) TargetCol = 1 Else 'cell A1 does have data so find the next available column TargetCol = TargetWks.Range("IV1").End(xlToLeft).Column + 1 End If 'Start copying data in the first range from the source sheet to the target sheet ColAH.Copy TargetWks.Cells(1, TargetCol) 'How do we continue copying the next range (ie: ColK, ColM€¦. ColU) into the next empty column in target sheet? Exit Sub End Sub "Daniel.C" wrote: Hi. Range("A:H,K:K,M:M,N:N,S:S,U:U") Note that you may have trouble with pasting not adjacent columns. Regards. Daniel Hi guys, I am trying to create a macro that would Copy Columns A-H,K,M,N,S,U from worksheet("DrList") to WorkSheet("DrListCal") within the same workbook. I know how to select ajacent columns but how do we deal with non ajacent ones? Would appreciate some help codding. Thanks |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Multiple non adjacent columns from one Worksheet to anoth
Thanks. That works great!
"Daniel.C" wrote: Try : Sub CopyDrList() Dim TargetWks As Worksheet, SourceWks As Worksheet Dim TargetCol As Long Dim ColAH As Range, ColK As Range, ColM As Range Dim ColN As Range, ColS As Range, ColU As Range Set SourceWks = Sheets("DrList") Set TargetWks = Sheets("DrListCal") Set ColAH = SourceWks.Range("A15:H" & SourceWks.Range("H65536").End(xlUp).Row) Set ColK = SourceWks.Range("K15:K" & SourceWks.Range("K65536").End(xlUp).Row) Set ColM = SourceWks.Range("M15:M" & SourceWks.Range("M65536").End(xlUp).Row) Set ColN = SourceWks.Range("N15:N" & SourceWks.Range("N65536").End(xlUp).Row) Set ColS = SourceWks.Range("S15:S" & SourceWks.Range("S65536").End(xlUp).Row) Set ColU = SourceWks.Range("U15:U" & SourceWks.Range("U65536").End(xlUp).Row) 'This is finding the next column available in the target sheet. If TargetWks.Range("A1").Value = "" Then 'Cell A1 is blank so the column to put data in will be column #1 (ie A) TargetCol = 1 Else 'cell A1 does have data so find the next available column TargetCol = TargetWks.Range("IV1").End(xlToLeft).Column + 1 End If 'Start copying data in the first range from the source sheet to the target Sheet ColAH.Copy TargetWks.Cells(1, 1) ColK.Copy TargetWks.Cells(1, TargetWks.Range("IV1").End(xlToLeft).Column + 1) ColM.Copy TargetWks.Cells(1, TargetWks.Range("IV1").End(xlToLeft).Column + 1) ColN.Copy TargetWks.Cells(1, TargetWks.Range("IV1").End(xlToLeft).Column + 1) ColS.Copy TargetWks.Cells(1, TargetWks.Range("IV1").End(xlToLeft).Column + 1) ColU.Copy TargetWks.Cells(1, TargetWks.Range("IV1").End(xlToLeft).Column + 1) End Sub Daniel Daniel You were right about problems pasting non adjacent columns. Decided that I will define each range separately and paste them one after the other. The following works for copying the first range "ColAH". Q: How do I code so that it continues to copy and paste the next ranges ColK, ColM and so on.... to the next column in destination sheet? my macro looks like this: Sub CopyDrList() Dim TargetWks As Worksheet, SourceWks As Worksheet Dim TargetCol As Long Dim ColAH As Range, ColK As Range, ColM As Range Dim ColN As Range, ColS As Range, ColU As Range Set SourceWks = Sheets("DrList") Set TargetWks = Sheets("DrListCal") Set ColAH = SourceWks.Range("A15:H" & SourceWks.Range("H65536").End(xlUp).Row) Set ColK = SourceWks.Range("K15:K" & SourceWks.Range("K65536").End(xlUp).Row) Set ColM = SourceWks.Range("M15:M" & SourceWks.Range("M65536").End(xlUp).Row) Set ColN = SourceWks.Range("N15:N" & SourceWks.Range("N65536").End(xlUp).Row) Set ColS = SourceWks.Range("S15:S" & SourceWks.Range("S65536").End(xlUp).Row) SetColU = SourceWks.Range("U15:U" & SourceWks.Range("U65536").End(xlUp).Row) 'This is finding the next column available in the target sheet. If TargetWks.Range("A1").Value = "" Then 'Cell A1 is blank so the column to put data in will be column #1 (ie A) TargetCol = 1 Else 'cell A1 does have data so find the next available column TargetCol = TargetWks.Range("IV1").End(xlToLeft).Column + 1 End If 'Start copying data in the first range from the source sheet to the target sheet ColAH.Copy TargetWks.Cells(1, TargetCol) 'How do we continue copying the next range (ie: ColK, ColM€¦. ColU) into the next empty column in target sheet? Exit Sub End Sub "Daniel.C" wrote: Hi. Range("A:H,K:K,M:M,N:N,S:S,U:U") Note that you may have trouble with pasting not adjacent columns. Regards. Daniel Hi guys, I am trying to create a macro that would Copy Columns A-H,K,M,N,S,U from worksheet("DrList") to WorkSheet("DrListCal") within the same workbook. I know how to select ajacent columns but how do we deal with non ajacent ones? Would appreciate some help codding. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I copy every fifth column in one worksheet to adjacent col. | Excel Discussion (Misc queries) | |||
| Arithmetical Mode Value for Filtered cells in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
| Sum Count of Single Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
| Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
| Copying multiple columns not adjacent | Excel Discussion (Misc queries) | |||
Linear Mode
