Paste special

[Guest]

Hi,

I need a code to activate a tab named "Italian",then to select the first
empty row in column C ,then to paste special "Values and numbers format"..
Is it possible?

 

[Sandy Mann]

Hi Pietro.

You don't have to activate the Italian sheet to paste to it, assuming that
you want to copy one cell and paste it into the first enpty cell in Column C
then try:

Option Explicit
Sub PasteIt()
Dim cValue As Double
Dim nFormat As String
Dim Here As Long

cValue = ActiveCell.Value
nFormat = ActiveCell.NumberFormat


With Sheets("Italian")
Here = .Cells(1, 3).End(xlDown).Row + 1
.Cells(Here, 3).Value = cValue
.Cells(Here, 3).NumberFormat = nFormat
End With
End Sub


If you do really want to activate the sheet then use:

Sub PasteIt2()
Dim cValue As Double
Dim nFormat As String
Dim Here As Long

cValue = ActiveCell.Value
nFormat = ActiveCell.NumberFormat


Sheets("Italian").Activate
Here = Cells(1, 3).End(xlDown).Row + 1
Cells(Here, 3).Value = cValue
Cells(Here, 3).NumberFormat = nFormat
End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Thank you for your answer..
But i still did not select the cell,i need to select it and excute the below
command to paste an entire row starting from the select cell(the first empty
cell in coumn C):

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

 

[Sandy Mann]

Hi Pietro,

But i still did not select the cell

Do you mean that it did not paste to the correct row in Column C?

If you mean to paste into the row after the last entry in Column C then use:

Option Explicit
Sub PasteIt()
Dim Here As Long
Dim There As Long

Here = ActiveCell.Row
There = Sheets("Italian").Cells(Rows.Count, 3).End(xlUp).Row + 1

Range(Cells(Here, 3), Cells(Here, 256)).Copy

Sheets("Italian").Activate
Cells(There, 3).Select

Selection.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Selection.PasteSpecial Paste:=xlFormats, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Application.CutCopyMode = False
End Sub

This will copy everything in the active sheet in the row in which the cursor
is located except columns A&B and paste it into the "Italian" sheet in the
Row under the last entry in Column C.

But as I said It is not necessary to actually activate the "Italian" sheet
to paste to it:

Option Explicit
Sub PasteIt2()
Dim Here As Long
Dim There As Long

Here = ActiveCell.Row
There = Sheets("Italian").Cells(Rows.Count, 3).End(xlUp).Row + 1

Range(Cells(Here, 3), Cells(Here, 256)).Copy

Sheets("Italian").Cells(There, 3).PasteSpecial _
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Sheets("Italian").Cells(There, 3).PasteSpecial _
Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Application.CutCopyMode = False
End Sub

If I still don't understand what it is you want then do post back with a
more detailed explanation of your requirements.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk