Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Paste Special To The Active Cell

i need some help
I have 200 columns that in the 3nd row on each of them there is an input box. So, in A3, B3, C3, D3, E3 etc there is an input box. I fill the input box with numbers and a formula doing a calculation. The result of the calculation returns to the column IV starting from the row 4.
I want to copy the column IV and paste special to the column that i last use to activate the input box. (Eg.If i was on D3 and i activate the input box i want to paste special the column IV to the column D starting from the D4)

Thanks in advance

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Paste Special
I was wondering how to code a paste special with the format(color, row/column size,font, number format) and the contents of the cells without pasting the formulas in those cells.

By the way, thanks for all the help you VBA/Excel gurus have been to me in my quest to become ungreen in my VBA/Excel knowledge.

Paste Special??
How do I create a function that is similar to Paste Special that actually paste an object or a link?

I don't see this method in Clipboard Object!

So, How should I prepare this 'Paste Special'??

Thanks in advance..

Paste Special
I have the code for cut, copy, paste, undo, yadda, but what is "Paste Special"?

Why would I use it?

What is the code for it?

Paste Special
Under many edit menus there are Undo/Cut/Copy/Paste/Paste Special.

What is Paste Special?

Is it possible to code that like it is for the others?

RTB - Paste Special, Does Anyone Know?
Hi I need to paste some data into a RTB, but i need the data Filtered, put in simply all i need is to paste Text form a Large document with pictures, the user may select the pictures+text, and say 'copy in the document (a web page for example). and when he comes to my APP and say 'paste' i need the data to be pasted. but minus the pictures, with all the text formatting. is this possible?

Paste Special Dillema
I have an excel document and I want to copy the cells a1-g22 into a blank Powerpoint presentation and paste special. I try to run the record function and fail to get a recording of this function. Any Ideas. I can easily link the cells but cannot paint special.

How To Paste Special Into Powerpoint?

I have written code that takes Excel Graphs and pastes them into Powerpoint. However can anyone please tell me how i can get the graphs pasted as 'Microsoft Excel Chart Objects' ? (i.e. as aloud on the paste special menu) as i can find this code anywhere.


Excel Paste Special
Hi, Im new to VB programming. can anyone help with what is obviously a very basic problem which has me stumped.
I want to paste special (values) into the spreadsheet. Below is a portion of the code. If I break the program at this point the clipboard contains the data i want to paste (and I can manually paste special) but the progam seems to ignore the pastespecial command and skip to the next line of the program.

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

Any help much appreciated

Paste Special The Same Formula?
Hello All,

Terrible easy problem, I can create a funcitioning macro in VBA, but for some reason I just can't find the solution using VB.

I have a row of data in 10 columns. I do know how to put a formula under the the data in the first column. I would like to take and use paste special to select the next 9 colums in that row and paste the first formula into them.


1 24.33 32.35 ..........
2 =a1+3 =b1+3 .......

I am using the pastespecial command and I keep getting an error that the copy and paste areas are not the same size.

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


Paste Special In Excel
I am trying to have excel open two files, copy the contents of one file and paste only the values into a sheet on the other file. Below is the code that I have used that does everything but the paste special function. Does anyone know the proper paste special function?

Set Excel = CreateObject("Excel.Application")

Excel.Application.Workbooks.Open newMetrics
Excel.Application.Workbooks.Open rptMetricsNew
Excel.Application.Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Force Paste Special When Pasting
Does anyone have a clue whether it is possible to force the Paste Special / Unformatted Text functionality (when clipboard contains text) - instead of the normal Paste?

Cheers from CPH,

Doing A Paste Special Into Word Object
I have a word document that has links to an excel spreadsheet. From VB i am opening this word document and doing a paste special into a new document. I just want to copy the formatting and values into the new document and not the links. ofcourse if i just do a simple paste the links also get copy with the formatting. The problem is when i do a paste special without the links and the formatting. the document that is created has the pasted part as a word object. And if i want to edit it, i have to double click on the object and that opens up a new word instance. And the links are still there. below is a code fragment for what i am trying to do. Hopefully someone can shed some light on the matter.

Set objWord = CreateObject("Word.Application")
Set objWordDoc = objWord.Documents.Open(App.Path & "", False)
objWord.Documents.Add DocumentType:=wdNewBlankDocument
objWord.Selection.Pastespecial Link:=False, DataType:=wdPasteRTF, Placement:= _
wdInLine, DisplayAsIcon:=False
objWord.ActiveDocument.SaveAs FileName:=App.Path & "This2.doc", FileFormat:= _
wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
False, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False, SaveFormsData:=False, _

Thank you.

Paste Special Values As Standard

When I copy/paste, and would like to only paste values and not formats.
I don't want to go to edit or right click, and then chose paste special values.
I would like to press ctrl-v and then only paste the values and not the formats. Is this possible?


Copy And Paste Special Code In VB.NET

I wonder to know if anyone can do me this favor. Thanks in advance.
Right now I use VB.NET to write a code. But I don't know what the codes are if I want to do the following steps.

1. Copy all the cells in one worksheet(Excel)
2. "Paste Special" those cells as "Values" in the same worksheet.

What can I code by using VB.NET?
Thanks a lot.
I really appreciate it.


Paste Special Data Validation

I'm wondering if anyone can help.
I'm writing code in VBA in Excel to copy only the data validation from a selected line in one workbook to a range in another workbook.
When i use my code it brings the error message: "Paste Special Method of range class failed"
My code is as below:

    Range(Cells(1, 1), Cells(400, 13)).Select
    Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

I've looked in the Excel help, and this says that 'xlDataValidation' is not an option for PasteSpecial.
There must be a way to only paste the validation!
Any help would be appreciated.


Pate Special - Paste Link Question
Pate Special - Paste link question
I made a very small app in vb and i would like my user's to be abel to use Paste Special.

Here i explane a case:

The user would do a copy from a textbox from my app and go into word or excel and do a Paste Special and Paste Link and the name of my app.

How can i get this to work with my app.

I guest it is with OLE but i'am new to programming so i need a hand on this one.


HELP!!! - Paste Special From VB To A Other Soft With Past Link.
I was wondering if there was another way to do this instead of going into VBA.

Is there a controle,OLE or something like that, that would give me access to the past link.

So i could do a copy from my total TxtBox to the other soft link word and excel.

I dont want to do a vba with only 1 soft but have it available to all soft that have a past link in it.

Here is the code!

Private Sub cmdCalculate_Click()
txtTotal = txtNumber1 * txtNumber2
End Sub

Private Sub cmdQuit_Click()
End Sub


HELP!!! - Paste Special From VB To A Other Soft With Past Link.
I was wondering if there was another way to do this instead of going into VBA.

Is there a controle or something link that, that would give me access to the past link.

So i could do a copy from my total TxtBox to the other soft link word and excel.

I dont want to do a vba with only 1 soft but have it available to all soft that have a past link in it.

Here is the code!

Private Sub cmdCalculate_Click()
txtTotal = txtNumber1 * txtNumber2
End Sub

Private Sub cmdQuit_Click()
End Sub


Activating (or Retrieving) The Value Of A Cell, Based On The Current Active Cell
We will test the patience for newbies!

I want to use the address of the current active cell on Sheet1 to move to the same cell on Sheet2 and then to the same column but an offset of a variable number of rows, and then use the value of that cell.

Eg the active cell is C3, an element of an nxn array on Sheet1, the upper left corner is in A1. I want to retrieve the value at the same relative location to the upper left corner pf one of several nxn arrays on Sheet2, where (if necessary) I can specify an offset after activating sheet2.[ C3 ] to Cx, where x is a calculated value.

Thanks from the newest of newbies!

Paste Special Method Of Class Failed Error
Application.Run "Violations_VBA.xls!RefreshTable"
Sheet1.Range("M5:P5").Copy Destination:=Range("A65536").End(xlUp).Offset(1, 0)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

The above code brings back a 'PasteSpecial method of Range class failed'. The code is copying from sheet 1 and pasting to the bottom of a list in the Total sheet. What am I doing wrong?

Cell Location Relative To Active Cell
I am new to VB but I really need some help with how to copy the value of the cell that located 5 cells (to the right) from the active cell? This value will be pasted to different workbook.


Selecting A Cell Based On Where The Active Cell Is
Firstly. Hello gurus, developers, lurkers and other bobs. It is a pleasure to find this place and meet you all.

Secondly. Here is the problemette that I am looking for assistance on.

I have been given an Excel spreadsheet to maintain and improve. What I am looking for is to be able to copy data from one worksheet to another with the following requirements. The data that I select is in worksheet 1, columns A and C which I then want to copy to worksheet 2. The process will be dependent on where the active cell is in worksheet 1, which could be anywhere within the range D3 to L1000. (I hope this makes sense)

Up until being given this spreadsheet to maintain I have never used any form of VB before. I have tried a multitude of searches here and on a few other sites and the results of those indicted using Range(ActiveCell, ActiveCell.End(xlToLeft)).Select in some way but so far I have been unable to make it function as required so I am open to suggestions.



Open Word File With Excel Macro And Paste Special An Excell Chart
I even want to create an Acrobat file from the word template within the Excell macro, but I presume the string would be similar....

How To Match The Value Of Active Cell With Other Value Cell
What is the code to match the value of active cell with other value cell in all workbook
I mean :
Example for one cell

If ActiveCell.Value = Cells(1, 3).Value Then MsgBox "Yes"
I want to check for all cells in workbook

If ActiveCell.Value = "any cells.Value in workbook" Then MsgBox "Yes"

Active Cells Paste
can any one please help me
if i have specified a cell as the active cell but then i want to paste a value in a cell two or 3 columns away from the active cell this is using vb in excel.

cheers stuart

Paste To An Empty Cell
My question must have been answered before... but I just can't find the thread...

how can VB paste into an empty excel cell, without producing the "paste method out of range" error?


Copy/Paste In One Cell
I have some data:
Tles[empty cell]
Tom[empty cell]
Peter[empty cell]
Pedro[empty cell]
Kat[empty cell]

When the emails are all the same I need to copy the values in A1:A5.
Then Paste the set in each empty cell seperated by commas
(i.e. Tles, Tom, Peter, Pedro, Kat)
So that all the empty cells have that same set in them.

I started with some code like this, active cell being the 1st email, but get stuck when I need to paste:
'Copy paste values
ActiveCell.Offset(0, -2).Select
first = ActiveCell 'holds first value
ActiveCell.Offset(1, 0).Select
second = ActiveCell ' 'holds second value etc etc
ActiveCell.Offset(1, 0).Select
third = ActiveCell
ActiveCell.Offset(1, 0).Select
fourth = ActiveCell
ActiveCell.Offset(1, 0).Select
fifth = ActiveCell
'Need paste code line here to paste all entries as one in the cells

Can anyone help me out.

How Can I Paste To An Excel Cell?
I have tried this but it isn't working.


'Start a new workbook in Excel

Set oExcel = New Excel.Application

Set obook = oExcel.Workbooks.Add

'Add data to cells of the first worksheet in the new workbook
Set oSheet = obook.Worksheets(1)

Clipboard.SetText ("Nothing")
oSheet.Range("b1").Paste = Clipboard.GetText
rem oSheet.Paste("b1,clipboard.gettext)
oSheet.Range("B1:Z1").Font.Bold = True

Thanks for any help

Return Worksheet Name && Paste In Cell
Sorry if this is easy, but it's been a while since I had to write code and searching in Help was useless.

I recently got a new PC with Office 2003 software. Now I have to do some edits to our file storage tracking program (that my boss and I wrote several years ago) to make it work with the new version. I'm okay with most of the edits, but I'm having trouble with the following:

I need code to return the name (on the tab) of the active worksheet (into a variable?) so that I can paste it into a specific cell on the active worksheet (specifically cell A6).

Thanks in advance,

Paste To Selected/specified Cell Only Excel
Hi guys

i am working my way though a project and just want to check that there isnt a beter way of doing this

here is a sample of the code its made of strings i captured and split to get the peices i wanted.

Dim fData As String
fData = "Steds Ref:" & vbTab & EngRef & vbTab & "Booking Clerk:" & vbCrLf & "Engineer:" & vbTab & Eng & vbTab _
& "Region:" & vbCrLf
MyDataObj.SetText fData
Range("B2").PasteSpecial xlPasteAll

the excel sheet i have to work with is a pre formated form with cells with descriptions in then right of that the blank cell where the data goes.
basically i select the top left cell then using a combo of vBTabs & vBCrLf in the paste string it navigate round the cells then pastes the data in. problem is i have to include the data in the descriptions fields as they get wiped out otherwise with the paste.

Questions as follows:
1/ is there a way of specifying cells and string individually then pasting thus avoiding cells with Description text in them.

this would also allow me to re-enable the sheet thus stopping the editing of the Description cells

Paste Text 50 Characters In To A Cell?
I want to paste a text into a cell. The text should always start 50 characters in to the cell, regardless of wich text that are written before.
How do I do that?

Paste Clipboard Content In Active Window
I am trying to mimic the PureText tool of Steve Miller for use in my own application. PureText can be found here (

It works, except for the function where the clipboard content, converted to text, is pasted in the active window.

I have the following code (trimmed, only the function that doesn't work is included).

Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long

Private Declare Function SendMessageSTRING Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As String) As Long

Private Declare Function GetForegroundWindow Lib "user32" () As Long

Dim sTemp As String
Dim lCurHwnd As Long
Dim lEdit As Long

sTemp = Clipboard.GetText(vbCFText)
lCurHwnd = GetForegroundWindow
lEdit = FindWindowEx(lCurHwnd, 0&, "Edit", vbNullString)
SendMessageSTRING lEdit, WM_SETTEXT, 256, stemp

I think the problem is that the code doesn't return the right handle for MDI apps. Cause it does work on notepad, but not in on Word or other MDI apps.

Any suggestions ?

Copy-paste Equation Trendline To Cell
I want to copy-paste the equation of a trendline to a cell. The code I've is:

Sub Macro1()
' Macro1 Macro
' Macro recorded 06/11/2002 by R&ED

ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets("data").Range("deel1"), PlotBy:= _
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="grafiek"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _
Backward:=0, DisplayEquation:=True, DisplayRSquared:=True).Select

ActiveWindow.SmallScroll Down:=-9
End Sub

I've recorded this with the macro recorder in Excel. But this code doesn't work.
I thought something with like Selection.Copy was needed, but it seems like it's not possible.
Knows anyone how to copy the datalabel of the trendline to a cell?

Fast Paste Of An Array To A Cell/range?
I know we can get a range into an array, but how about putting the values back into a range?

Is there an efficient method of pasting an array to a range?


MSFlexGrid Copy/paste Cell Contents?
I am using MSFlexGrids to display data from an array. I would like to be able to copy and paste contents of a cell to a text box, but I can't figure out how to make the right button on the mouse bring up the copy/paste menu. I can capture "pressing right mouse" button event, but can't get beyond this. I found an example program that automatically brings up this copy/paste menu, but I could find not code that made it happen. I checked all the properties for the MSFlexGrid they were using and matched them in my program, but still no copy/past menu. Can anyone help? thanks...TeeTime

How Do I Use The Getfilename Function To Paste The Filename To A Cell
Please help me.
I am just trying to get the thing to allow the user to browse and get file name that then gets pasted into a cell.

Thx in advance for any help


Excel Chart - Paste Special As Picture In Excel Itself
Hi All,

I am working on a report and it needs to be generated daily. After generating and creating charts, I need to paste special those charts as "Pictures". Is there any way to do this? And also i need to paste special every single chart in the work book. I already done a code but it not working properly.. and the important point is i need to paste special the chart at the exact position where i intended to cut. Is this possible???

Pls treat this as emergency and advice me on this.

Thanks in advance..


Copy, Change Spreadsheet, Move Down One Cell And Paste
I need a simple macro to let me highlight some data, then the macro copies the highlighted selection, changes the tab in the spreadsheet, then the cell reference moves down one row and then pastes the selection.

Note: I will select the data and I will select the cell for the cell reference so that when the macro move the cell reference down by 1 row it will paste in the correct place - hence when i continue to use the macro the information will always be pasted in the correct cell as the macro will move the cell reference down by one row.

This should be a simple macro to write, however, when I try simply 'moving down one cell/row' the macro is recorded for the particular cell reference used to create the macro rather than simply just move down by one ropw regardless of the cell reference - - - is this possible?

Look forward to your solutions,


Function That Sorting Rows And Paste The Result In One Cell
I have the following columns

0 1 2 0 1 1

i need a function that, to sort the data by asceding order and then to concatenate the result.

So using the above will get the following

Is it possible ??
Thanks in advance

If the above is possible, may i have another function too, that excluding the zeros so to get the




Creating Auto Copy And Paste Of Cell Range

Very new to this so help would be appreciated.

Looking to copy cell range say H8 to M8 down to a specific row dependent upon whether a text condition is met on that row at cell E. Text condition is that there is text there. N o copy on no text there. Application range is entire sheet.

Some sample code to get me going would be much appreciated.


How To Set Active Cell ?
Hi, my friends.
I wish to set r (the found range object of only 1 cell) to be the active cell for the following offset function.
I tried many conbinations but failed. Can anybody give me a clue how to fix it ? Many thanks !

Function RaceDistanceCoefficient(ByVal lDistance As Long, ByVal lMarginDist As Long, _
sCol As String) As Double
Dim rngSource As Range, oCell As Range, r As Range
Dim dSumSpeed As Double, dRaceSpeed As Double, dMarginRaceSpeed As Double
Dim a As Integer
Set rngSource = oWB.Worksheets("Sheet1"). _
Range(sCol & "2", Range(sCol & "65536").End(xlUp)) 'Distance Column
For Each oCell In rngSource
Set r = oExcel.Cells.Find(lngDistance)
Next oCell
If Not r Is Nothing Then '[I wish to set r as the active cell for the folllowing codes : ]
While oExcel.ActiveCell.Offset(a, 0) = oExcel.ActiveCell
dSumSpeed = dSumSpeed + oExcel.ActiveCell.Offset(a, 7).Value
a = a + 1
dRaceSpeed = dSumSpeed / a

How To You Get The Row/col For The Active Cell ?
Just a quick one.

I'm using .OFFSET in a script which manipulates an Excel spreadsheet (see below).

VB Code:
For i = 1 To 53        If Weeks(i) Then            ActiveCell.Offset(0, 2).Select            ActiveCell.Offset(-1, 0).Value = "Margin"            ActiveCell.Value = "Week " & Format(i, "00")            ActiveCell.Offset(-1, 1).Value = "Margin %"            ActiveCell.Offset(0, 1).Value = "Week " & Format(i, "00")            RangeCell = ActiveCell.Offset(0, 1).Row        End If            Next i

When I have finished this loop, I want to know the cell address of the final entry.

Can anyone tell me the syntax for this as I can't seem to find it.


Active Cell
how do I change the value of ActiveCell, as in move which cell you can get a valur from.


Excel Active Cell

This may seem a little simple, but I have no idea!!

I want to store the position of the current active cell so I can come back to it after a loop has finished. How should I go about this?

Finding Out What Cell Is Active

This is just a quick question.
Im using vb to manipulate an excel file.

I Need to find the out what cell the activecell is on.

i know the following piece of code will give you the row its on
i.e answer will be a number

answer = OBJECT.ActiveCell.Row

but is there any way to find out what specific row and column it is
on e.g E7 or B4
not just what row its on

any help would be much appricated

thanx in advance


Color Active Cell
Hi Guys,

How do i actually earlier higlighted cell to its original color?

Lets say now i clicked cells(1,1) , it will automatically change it to blue
Thats working fine..but how do i do the following
When i switch to cells(1,2) , the earlier cells will turn to its original color and now cells(1,2) will be highlighted to blue

I can detect the cell being clicked, and higlight it..but how do i change previous higlighted cell to its original color

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Cells(Target.Row, Target.Column).Select
ActiveCell.Interior.Color = RGB(20, 20, 20)
End Sub

Trying To Delete A Row That A Active Cell Is In
This is the code i have at the moment but i cant find how to delet the row were my active cell is in

what the program does at the moment is check to see if any of the six numbers in the row are the same and if they are delete the row

the part i am hving the problem with is the

worksheet("sheet1").rows(??????).delete were i have pu the question mark is were i am having the porblem at the moment, normaly you just write a number in the and it will delete that row but i am trying to get it to delete the row it is on if there is a number double up
please help!

this is the code iam working on

While ActiveCell.Value <> ""

If ActiveCell.Value = ActiveCell.Offset(0, 1).Value Then


ActiveCell.Offset(0, 1).Activate

End If

ActiveCell.Offset(1, 0).Activate


Select A Row That The Active Cell Is On
Hi Everyone,
is it possible to select the row that the active cell is on?
Thanks for the help

Cheers, Adam

Making A Cell Active In VB
Pls let me know how to make a cell active from VB code

Copyright 2005-08, All rights reserved