always start in certain cell/row

[antony moseley]

HI,

I have a macros that copy’s certain bits of data forma row, then copies it to another sheet, however I always need it to start in column A. E.g. if my active cell is C3, I want to start in A3, likewise if the active cell is E3, I want to be able to start in A3.

As always the help is greatly appreciated

Antony

[Richard Buttrey]

Hi,

Just reposition the cursor with some code like:

Range("A" & ActiveCell.row).Select

In practice it's best to avoid .Select or .Activate instructions. They are rarely needed since usually you only need to reference a range to do something with it. However if your existing macro needs A? whatever selecting then just use the above.

HTH

[blane245]

This can be done several ways. One way to reference the cell in Column A woth the same row as the active cell is

Cells(ActiveCell.Row, 1)

[antony moseley]

This can be done several ways. One way to reference the cell in Column A woth the same row as the active cell is

Cells(ActiveCell.Row, 1)

What other way is there to do this?

I neglected to say - lesson learnt!!!

On the same row i have the need for two start places - eg columns A to G i need to start at A, but in column H to N i need to start at H.

I kind need to be if in an celll in column C, go back two cells/columns or if in column J alos go back two cells/column.

Apologoies for not stating this in my first post

Antony

[blane245]

Here are two range variables that contain the cells in columns A and H based on the active cell

Dim CellA As Range
Set CellA = Range(Cells(ActiveCell.Row, 1).Address)
Dim CellH As Range
Set CellH = Range(Cells(ActiveCell.Row, 8).Address)

[antony moseley]

Here are two range variables that contain the cells in columns A and H based on the active cell

Dim CellA As Range
Set CellA = Range(Cells(ActiveCell.Row, 1).Address)
Dim CellH As Range
Set CellH = Range(Cells(ActiveCell.Row, 8).Address)

HI,

I am usnure as to how i should use the above... any tips?

Thanks

Antony

[blane245]

I you can post a copy of your file (with nonsensitive data, of course), then we can better help you with your problem. For now, the range variables, CellA and CellH, can be used as the source to copy from one worksheet to another.

[antony moseley]

HI,

File attached.

Bascailly the active cell could be left as any one of B,C,D,E,F,G or H after someone has filled in the info - but i want to be able to copy the data from A to I to another sheet to create an e-mail so end off.

Likewise in with the otehr side left in K,L,M,N,O,P,Q, but i want to be able to copy the data from J to R etc etc

Trust the above makes sense

Thanks

Antony

[romperstomper]

Dim rngCopy as Range
Select Case Activecell.column
   Case <= 8
      Set rngCopy = cells(activecell.Row, 1).Resize(, 9)
   Case else
      Set rngCopy = Cells(activecell.row, 10).Resize(, 9)
End Select

for example.

[antony moseley]

Dim rngCopy as Range
Select Case Activecell.column
   Case <= 8
      Set rngCopy = cells(activecell.Row, 1).Resize(, 9)
   Case else
      Set rngCopy = Cells(activecell.row, 10).Resize(, 9)
End Select

for example.

Hi,

Nothing happens with the above code - i asumming that it should be selecting the set of data???

Antony

[romperstomper]

No - it merely assigns the relevant range to a variable. What you do with it after that is up to you!

[antony moseley]

No - it merely assigns the relevant range to a variable. What you do with it after that is up to you!

HI,

I think the grey stuff must not be working....

If i ran the code and try to do a copy function after all it does is copy the cell that the cursor was left in... i am unsure as to what i should be putting next in the code to get it to copy/select the range

A

[romperstomper]

rngCopy.Copy

should do it.

[antony moseley]

spot on!!!