ActiveCell, ActiveCell.End

[Guest_imported]

I have a command button that pastes info to columns a to c. This info can be any number of rows long. The command then goes on to paste formulae from the first row columns say d to x to the same number of rows previously pasted onto columns a to c using a ActiveCell, ActiveCell.End command.

This works fine except went there's only 1 row to be copied. For some reason unknown to me it copies the formulae from the first row to the last row.

Eg.

Range("c8:U8&quot.Select
Application.CutCopyMode = False
Selection.Copy
Range("C8&quot.Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Please Help

 

[dwilson01]

Although this may not be the most elegant approach, it seems to work, try this

Range("c8:U8&quot.Select
Application.CutCopyMode = False
Selection.Copy
Range("C8&quot.Select
If ActiveCell.End(xlDown).Row < 65535 Then
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End If
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

 

[Guest_imported]

Cheers,

This does the trick, but it thinks the end of the sheet is the last row, and obviously increases the size of my workbook!

Help!

 

[dwilson01]

It is not so obvious to me that size of the workbook increases simply because you test for the last row of data. The above IF statement is just that, IF. If ActiveCell.End(xlDown).Row = 65536 (the last row in the sheet) then there isn't any data below the current selection. Thus, the formula will only be copied to the current row.

A confusing point to me is that you are pasting data to columns A, B and C and then copying your formulae over the data in column C. I'm puzzled? Maybe someone else can take over from here.

 

[bryanbayfield]

Yep, I'm baffled as well.

You're copying to columns A to C? Then why does your code indicate that you're copying the range

range("C8:U8")

and then pasting formats and formulas into that exact same range? Also, you haven't said which range you're copying from?!

Me no understandy.

 

[Guest_imported]

Sorry you were right, once i saved the workbook the end became the first row again.

Thank very much for the help

 

[Guest_imported]

Sorry for the confusion. It's a bit of a nightmare trying to explain with as little text as possible.

Its working fine now:

Private Sub PasteBoQInformation_Click()
msheet = ActiveSheet.Name
Select Case msheet
Case &quot;1&quot;, &quot;2&quot;, &quot;3&quot;, &quot;4&quot;, &quot;5&quot;, &quot;6&quot;, &quot;7&quot;, &quot;8&quot;, &quot;9&quot;, &quot;10&quot;, &quot;11&quot;, &quot;12&quot;, &quot;13&quot;, &quot;14&quot;, &quot;15&quot;, &quot;16&quot;, &quot;17&quot;, &quot;18&quot;, &quot;19&quot;, &quot;20&quot;, &quot;21&quot;, &quot;22&quot;, &quot;23&quot;, &quot;24&quot;, &quot;25&quot;, &quot;26&quot;, &quot;27&quot;, &quot;28&quot;, &quot;29&quot;, &quot;30&quot;, &quot;31&quot;, &quot;32&quot;, &quot;33&quot;, &quot;34&quot;, &quot;35&quot;, &quot;36&quot;, &quot;37&quot;, &quot;38&quot;, &quot;39&quot;, &quot;40&quot;
If Application.CutCopyMode = False Then
MsgBox &quot;Please Select BoQ Items!&quot;
Else
Range(&quot;A8&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range(&quot;C8&quot.Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Copy
Range(&quot;v8&quot.Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range(&quot;c8:U8&quot.Select
Application.CutCopyMode = False
Selection.Copy
Range(&quot;c8&quot.Select
If ActiveCell.End(xlDown).Row < 65528 Then
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End If
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range(&quot;v8&quot.Select
If ActiveCell.End(xlDown).Row < 65528 Then
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End If
Selection.Copy
Range(&quot;c8&quot.Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range(&quot;D8&quot.Select
End If
Case Else
MsgBox &quot;You Can't Enter BoQ Information on sheet &quot; & msheet
End Select
End Sub

Thanks