Copy certain columns to another sheet using a macro

[Shane Nation]

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

 

[Guest]

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]

Thank you for your help. Can't get it to work, way over my simple head.

Thanks

Shane