Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy certain columns to another sheet using a macro
I have got this macro:
Sub Macro1() ' ' Macro1 Macro ' Macro recorded 24/09/2005 by Shane Nation ' ' Range("A1,B1,C1,E1,G1,I1:J1,L1:N1").Select Range("L1").Activate Selection.Copy Sheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste End Sub Waht I am trying to do is click on call A"n" i.e. selecting different rows as I move down the sheet, then ru a macro which copys only the columns a,b,c,e,g,i,n in the selected row. Open a diffresent and paste them at the current cursur location. This macro just selts the same cells and pasts them in the same location each time it is run. Please can someone help? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy certain columns to another sheet using a macro
use a worksheet selection change event
with the VBE paste this code into Thisworkbook be sure that this workbook has a sheet named "sheet2" Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Dim myrow As Integer Dim mycolToCopy As Variant Dim counter As Integer mycolToCopy = Array(1, 2, 3, 5, 7, 9, 10, 12, 13, 14) 'your columns to copy If Sh.Name = "Sheet1" Then If Target.Column = 1 Then myrow = Target.Row ' copy to same row on sht 2 With ActiveWorkbook.Sheets("Sheet2") For counter = 1 To 10 ' NUMBER OF CELLS TO COPY .Cells(myrow, counter).Value = Sh.Cells(myrow, mycolToCopy(counter)) Next counter End With End If End If End Sub "Shane Nation" wrote: I have got this macro: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 24/09/2005 by Shane Nation ' ' Range("A1,B1,C1,E1,G1,I1:J1,L1:N1").Select Range("L1").Activate Selection.Copy Sheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste End Sub Waht I am trying to do is click on call A"n" i.e. selecting different rows as I move down the sheet, then ru a macro which copys only the columns a,b,c,e,g,i,n in the selected row. Open a diffresent and paste them at the current cursur location. This macro just selts the same cells and pasts them in the same location each time it is run. Please can someone help? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy certain columns to another sheet using a macro
Thank you for your help. Can't get it to work, way over my simple head.
Thanks Shane "Vacation's Over" wrote in message ... use a worksheet selection change event with the VBE paste this code into Thisworkbook be sure that this workbook has a sheet named "sheet2" Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Dim myrow As Integer Dim mycolToCopy As Variant Dim counter As Integer mycolToCopy = Array(1, 2, 3, 5, 7, 9, 10, 12, 13, 14) 'your columns to copy If Sh.Name = "Sheet1" Then If Target.Column = 1 Then myrow = Target.Row ' copy to same row on sht 2 With ActiveWorkbook.Sheets("Sheet2") For counter = 1 To 10 ' NUMBER OF CELLS TO COPY .Cells(myrow, counter).Value = Sh.Cells(myrow, mycolToCopy(counter)) Next counter End With End If End If End Sub "Shane Nation" wrote: I have got this macro: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 24/09/2005 by Shane Nation ' ' Range("A1,B1,C1,E1,G1,I1:J1,L1:N1").Select Range("L1").Activate Selection.Copy Sheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste End Sub Waht I am trying to do is click on call A"n" i.e. selecting different rows as I move down the sheet, then ru a macro which copys only the columns a,b,c,e,g,i,n in the selected row. Open a diffresent and paste them at the current cursur location. This macro just selts the same cells and pasts them in the same location each time it is run. Please can someone help? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i copy rows to columns on separate sheet, and have them upd | Excel Worksheet Functions | |||
Copy a row from one sheet to another and not all columns copy | Excel Programming | |||
Macro to copy columns from 1st sheet to 2nd according to mapping-p | Excel Programming | |||
macro to copy columns to sheet | Excel Discussion (Misc queries) | |||
How to create a Macro to Copy Information in one sheet to another sheet. | Excel Programming |