Select and Copy Range using Offset
 

Hi,

I'm experincing some problems with trying to accomplish the following.

Using Excel as a DB and have exisiting code that will enter a ne
record after the last entry in the list.

Before the Data Entry form is displayed i would like to copy the Cel
Format of the previous columns for the new record column only.

The following activates the first cell of the required range (the lef
most):

ActiveWorkbook.ActiveSheet.Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtClarifyRef.Value
ActiveCell.Offset(0, 1) = txtCaseTitle.Value
ActiveCell.Offset(0, 2) = cboOperatingSystem.Value

ActiveCell.Offset(-1, 14).Select

I am hoping to select a range from this ActiveCell to an offset valu
(10 columns along). Copy the formatting (not the values) and apply i
to the row below (but with same column references)

--
Message posted from http://www.ExcelForum.com

Select and Copy Range using Offset
 

Jon,

Try this

cLastRow = Cells(Rows.Count,"A").End(xlUp).Row
Cells(cLastRow,"A").Resize(1,10).Copy
Cells(cLastRow+1,"A").PasteSpecial Paste:=xlPasteFormulas

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jondorv " wrote in message
...
Hi,

I'm experincing some problems with trying to accomplish the following.

Using Excel as a DB and have exisiting code that will enter a new
record after the last entry in the list.

Before the Data Entry form is displayed i would like to copy the Cell
Format of the previous columns for the new record column only.

The following activates the first cell of the required range (the left
most):

ActiveWorkbook.ActiveSheet.Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtClarifyRef.Value
ActiveCell.Offset(0, 1) = txtCaseTitle.Value
ActiveCell.Offset(0, 2) = cboOperatingSystem.Value

ActiveCell.Offset(-1, 14).Select

I am hoping to select a range from this ActiveCell to an offset value
(10 columns along). Copy the formatting (not the values) and apply it
to the row below (but with same column references).


---
Message posted from http://www.ExcelForum.com/