I am trying to copy a contents of cell to the other tab in the same
spreadsheet and written the macro as below.

Sub AddComment()
'
' AddComment Macro
' Macro recorded
'
Selection.Copy
Sheets("Comments").Select
Selection.SpecialCells(xlCellTypeLastCell).Select
Range("A4").Select
ActiveSheet.Paste
Range("B4").Select
End Sub

What it is doing is copy the 'selected cell' in the sheet only.
As my spreasheet is very wide, I want to be able to run the macro from any
cell in a perticular row and copy a specific cell contents (e.g. contents
from column A for the selected row)

Help is much appriciated.
Thanks.

I don't quite follow what you are trying to do, but maybe you can adapt this
example to your needs

Sub test()
Dim rngSource As Range
Dim rngDest As Range

Set rngSource = ActiveWorkbook.Worksheets("Sheet1").Range("A1")
Set rngDest = ActiveWorkbook.Worksheets("Sheet2").Range("B2")

rngSource.Copy Destination:=rngDest

End Sub

rngDest should be same size as rngSource or it can be a single cell

If you only want to paste values, you could use the PasteSpecial function
but you can also do this

rngDest.Value = rngSource.Value

In passing, although the macro recorder faithfully records your Select and
Activate keystrokes you seldom need to use Select or Activate in your actual
code.

Regards,
Peter T


"Beginner for Macro" <Beginner for (E-Mail Removed)> wrote in
message news:0BA37802-6FBC-4BEB-86B5-(E-Mail Removed)...
>I am trying to copy a contents of cell to the other tab in the same
> spreadsheet and written the macro as below.
>
> Sub AddComment()
> '
> ' AddComment Macro
> ' Macro recorded
> '
> Selection.Copy
> Sheets("Comments").Select
> Selection.SpecialCells(xlCellTypeLastCell).Select
> Range("A4").Select
> ActiveSheet.Paste
> Range("B4").Select
> End Sub
>
> What it is doing is copy the 'selected cell' in the sheet only.
> As my spreasheet is very wide, I want to be able to run the macro from any
> cell in a perticular row and copy a specific cell contents (e.g. contents
> from column A for the selected row)
>
> Help is much appriciated.
> Thanks.

Sorry, I should have explained a lil bit more.

What I have is a database very wide and long.
The range for the code example below looks like this
Column A : Header A1 = Country, A2:A? = Country names
Column B : Header B1 = Name, B2:B? = Names
Column C : Header C1 = Gender, C2:C? = F or M
Column D : Header D1 = Birthday, D2? = Dates
Column ...: ......
Column XXX : Header XXX1 = Last Update, XXX2:XYZ? = Dates

I want to be able to run a macro while on XXXn (or any other cell in that
row) but copy the contents from Bn (contents in column B for the same row,
i.e. B2 if the selection is anywhere in row 2 and B5 if the selection is
anywhere in row 5) to anoother Tab in the same spreadheet.

The problem I am having with the macro written above (in the original
posting) is it copys the contents from XXXn (whatever the other cell selected
before running that macro) and not from column 'B'.

I hope this is useful and a bit more clear.
Thanks in advance.


"Peter T" wrote:

> I don't quite follow what you are trying to do, but maybe you can adapt this
> example to your needs
>
> Sub test()
> Dim rngSource As Range
> Dim rngDest As Range
>
> Set rngSource = ActiveWorkbook.Worksheets("Sheet1").Range("A1")
> Set rngDest = ActiveWorkbook.Worksheets("Sheet2").Range("B2")
>
> rngSource.Copy Destination:=rngDest
>
> End Sub
>
> rngDest should be same size as rngSource or it can be a single cell
>
> If you only want to paste values, you could use the PasteSpecial function
> but you can also do this
>
> rngDest.Value = rngSource.Value
>
> In passing, although the macro recorder faithfully records your Select and
> Activate keystrokes you seldom need to use Select or Activate in your actual
> code.
>
> Regards,
> Peter T
>
>
> "Beginner for Macro" <Beginner for (E-Mail Removed)> wrote in
> message news:0BA37802-6FBC-4BEB-86B5-(E-Mail Removed)...
> >I am trying to copy a contents of cell to the other tab in the same
> > spreadsheet and written the macro as below.
> >
> > Sub AddComment()
> > '
> > ' AddComment Macro
> > ' Macro recorded
> > '
> > Selection.Copy
> > Sheets("Comments").Select
> > Selection.SpecialCells(xlCellTypeLastCell).Select
> > Range("A4").Select
> > ActiveSheet.Paste
> > Range("B4").Select
> > End Sub
> >
> > What it is doing is copy the 'selected cell' in the sheet only.
> > As my spreasheet is very wide, I want to be able to run the macro from any
> > cell in a perticular row and copy a specific cell contents (e.g. contents
> > from column A for the selected row)
> >
> > Help is much appriciated.
> > Thanks.
>
>
>