Copy And Paste Special Values For Non Contiguous Ranges?
Mar 18, 2014
I use to have a macro that could copy formulas from non contiguous ranges and paste the values in the same cells. So for example, I could select a non contiguous range like B5:B10, E10:F12, and G1:G10. The macro would then copy and paste the values in those 3 ranges, so those 3 ranges would be values instead of formulas.
I think it worked by storing each range and then looping through each range to copy and paste special values.
View 2 Replies
ADVERTISEMENT
Dec 20, 2007
Been playing with this for some time and can't quite figure it out. I'm trying to copy a named range and paste special value to another named range of the same size. I recorded a Macro that does what I need, and I'll use if necessary, but thought I'd try to consolidate the code a bit and can't get it to work. The recorded macro is as follows:
Sub PasteRanges()
Application.Goto Reference:="DataCopy30Yr"
Selection.Copy
Application.Goto Reference:="DataPaste30Yr"
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
End Sub
My attempt to consolidate was this:
Sub PasteRanges()
Range("DataCopy30Yr").Copy Destination: Range("DataPaste30Yr").PasteSpecial (xlPasteValues)
End Sub
I get the Run-time error 1004 Copy method of Range class failed when I try to run this. Again, I can use the longer version with no problem, but in the interest of learning and since I have spent some time not being able to come up with the solution, I thought I'd ask the experts opinions before I gave up on it.
View 6 Replies
View Related
Feb 14, 2013
For some reason the following code, when executed, pulls a named range from the source workbook (sProj_Name). I feel like I have used this exact same code before and not encountered this issue. This named range is linked to the source workbook so if I save the file and re-open I get the wonderful 'would you like to update links' pop up. I cant figure out why, when I am forcing it to paste only values and formats, it is picking up this named range. In addition the source workbook has way more than one named range, so why it doesn't pull all instead of just one is a mystery to me as well.
VB:
With Workbooks(sProj_Name).Worksheets(2)
.Cells.Copy
Sheet2.Cells(1, 1).PasteSpecial Paste:=xlPasteValues
Sheet2.Cells(1, 1).PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
End With
View 2 Replies
View Related
Jan 4, 2012
I have a perplexing problem. I created code to copy a worksheet that has the correct page setup and then copy/paste special to get just the values, not the formulas from the master sheet. It works standalone - the copied worksheet after doing the paste/special just has the data along with the master sheet's original page setup. When I include the code within a For Each... loop, it only copies the worksheet and I get the formulas. What is different between the two approaches that would cause the loop code to not do the paste/special values?
Code that works:
Application.DisplayAlerts = False
Application.CutCopyMode = False
Sheets("PerfCompDashDistrict").Activate
Sheets("PerfCompDashDistrict").Select
Sheets("PerfCompDashDistrict").Copy Before:=Sheets("Store")
[Code] ........
Code doesn't work:
For Each DistrictCell In DistrictRange
Sheets("PerfCompDashDistrict").Range("c4") = DistrictCell
Calculate
Sheets("PerfCompDashDistrict").Activate
Sheets("PerfCompDashDistrict").Select
Sheets("PerfCompDashDistrict").Copy Before:=Sheets("Store")
[Code] .....
View 2 Replies
View Related
Jan 15, 2008
I have 9 worksheets in a book with sheets 1-8 used for data entry, and sheet 9 used for a weekly data upload. Sheets 1-8 are all formatted the same, they just represent different vendors. I need a macro to look through column F (invoice number) on sheets 1-8 and see if the value of each cell already appears in column c on sheet 9. If the value is not found and the row value for column M (sheets 1-8) is >0, I need to copy the data from columns D:K and paste special values into the next blank row on sheet 9.
Basically I'm trying to see if I've already paid an invoice, and if not then I need to automatically add the data for payment. I've tried to mess around with a VBA code myself and ended up with a migraine every time. I've also tried to use the advanced filter, but the cell values I need to copy over contain formulas and conditional formatting...not to mention I can't delete the data on sheet 9 that has already been added.
View 9 Replies
View Related
Jun 21, 2006
In the code below, I am copying information from a master sheet to various sub-sheets, but I cant figure out how, based on the way I have written my code, to copy and paste special values. I commented out the line that I need pasted special values.
Dim intData As Integer
Set wsData = Worksheets("Data")
LastRow = wsData. Range("A" & Rows.Count).End(xlUp).Row
Set wsCrit = Worksheets.Add
wsData.Range("E3:E" & LastRow). AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("E3"), Unique:=True
LastRowCrit = wsCrit.Range("E" & Rows.Count).End(xlUp).Row
For I = 4 To LastRowCrit
Set wsNew = Worksheets.Add
wsNew.Name = wsCrit.Range("E" & I).Value
wsNew.Range("a3").Value = "Estimator"
wsNew.Range("a5").Value = "IP Number"
wsNew.Range("b5").Value = "Project Name" ......................
View 5 Replies
View Related
Jun 23, 2006
i would like to copy a sheet to another sheet, rename, copy and paste special values. but after the sheet is copied to another, the macro stops working...?
Sheets("Proposal").Copy After:=Sheets("Proposal")
'rename...
ActiveSheet. Name = "ProposalEmail"
ActiveSheet.Copy
ActiveSheet.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
:=False, Transpose:=False
View 2 Replies
View Related
Mar 8, 2007
I am trying to create a macro to copy multiple sheets to a single named worksheet, all within the same workbook. The code below works, except I want to copy only the data (no formulas). Can I add code to paste values, or do I need to start over?
Public Sub CopyandPaste()
Dim ws As Worksheet
Worksheets("Summary").UsedRange.Delete
For Each ws In Worksheets
If ws.Name <> "Summary" Then
ws.Range("a2"). CurrentRegion.Copy _
Destination:=Worksheets("Summary").Range("A65536").End(xlUp)
End If
Next ws
End Sub
View 4 Replies
View Related
Jan 27, 2007
Need VBA macro that will copy & paste (Special > AS VALUES) from one of two (Data A & B) sheets based on the contents of a validation cell ($D$4) in a third (Report) sheet? The destination starting cell would be $F$11. ALSO - I'd like to have the Named Ranges "DataAExtract" & "DataBExtract" used in the code (for the COPY region) so I can see an example of how to reflect my actual named ranges in my working file.
The reason for doing this is that the "c.Characters...." lines in my conditional formatting macros (attached) are not working on cells containing formula output (in my working file the Report page is all populated by VLOOKUP results), but the macros run fine on hard-coded values. In my attached workbook, I'd like to have the "NEW" macro for the copy & paste step fire first in the sequence of macros running after the FORM button-click (control located in cell $D$5 of the Report sheet), whether that's by writing a new macro and calling mine before the new one ends, OR by consolidating all of my macros plus the new one into one smooth progression.
With this low-tech approach I can get updated VALUES into the report area once the user selects a data source and a customer on the report sheet. The COPY ranges in my working spreadsheet will update based on the selections made in the report page. I tried recording a macro and then modifying the recorded code to add the "If > Then" functionality I'm looking for, but I'm pretty green when it comes to VBA code and syntax.
View 3 Replies
View Related
Nov 24, 2012
Copy over data from different workbooks and using paste special values to paste it into a new workbook using a macro. Here is what I have and what I am looking for:
My file path is
C:Documents and SettingsMy DocumentsProjectCostsDecember12
In this folder I have workbooks called:
Function1
Function2
Function3
In each workbook I have 4 worksheets
Cashable12-13
NonCashable12-13
Total12-13
GrandTotal12-13
I also have a workbook called DecMonthlyTotal in the same folder with the same named worksheets.
I am looking for a macro to be placed in the DecMonthlyTotal that will pull the data from the Cashable12-13 worksheets from Function1, Function2 and Function3 and paste special the values into workbook DecMonthlyTotal, worksheet name Cashable12-13, it will also pull the data from the NonCashable12-13 worksheets from Function1, Function2 and Function3 and paste special the values into workbook DecMonthlyTotal, worksheet name NonCashable12-13
Both the Cashable12-13 and the NonCashable12-13 have Columns A - G The row that the macro should start the copying from is Row 3 for each of the workbooks; however I don't have an end row for the workbooks as this will vary.
I am using Excel version 2003.
View 1 Replies
View Related
Nov 20, 2008
I have Workbooks("A") and Workbooks("B") open.
Workbooks("A") contains Sheets("Sheet1"). Range("mySource").
Workbooks("B") contains Sheets("Sheet1").Range("myTarget").
Both ranges have been created by joining multiple ranges, in this way:
Union(Range("C1:C13"), Range("K1:K2"), Range("K5:K9"), Range("K14"), Range("Q6"), _
Range("I18"), Range("B20:P20"), Range("B24"), Range("C26:E26"), Range("C29"), _
Range("B34:B40"), Range("B44:Q50")).Name = "mySource" 'or "myTarget"
So: both ranges contain the same number of cells with the same addresses, and they have been added in the same order. However, if now I try to pass all values from mySource to myTarget, in this way:
Workbooks("B").Sheets("Sheet1").Range("myTarget").Value = _
Workbooks("A").Sheets("Sheet1").Range("mySource").Value
the result is a complete mess. Only the first "subrange" of mySource ("C1:C13") is passed to myTarget, and pasted in each of its "subranges", sometimes by rows and sometimes by columns...
View 2 Replies
View Related
May 8, 2008
I had some code working fine in office 2003:
Sub Replacing()
Dim rRange As Range
Dim lArea As Long
Dim Co As Byte
Dim NaCo As Byte
NaCo = 99
Set rRange = Range("B:C,E:F,H:I")
With rRange
For lArea = 1 To .Areas.Count
With .Areas(lArea)
Co = Choose(lArea, 1, 2, 3)
.Replace What:=Co, Replacement:=NaCo, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
Next lArea
End With
End Sub
the problem is that it's not working for Office 2000
View 2 Replies
View Related
May 18, 2008
This time I want to replace:
number "99" in range("B:C") to number "1";
number "99" in range("E:F") to number "2";
number "99" in range("H:I") to number "3";
Sub Replacing()
Dim rRange As Range
Dim lArea As Long
Dim Co As Byte
Dim NaCo As Byte
NaCo = 99
Set rRange = Range("B:C,E:F,H:I")
With rRange
For lArea = 1 To .Areas.Count
With .Areas(lArea)
Co = Choose(lArea, 1, 2, 3)
.Replace What:=Co, Replacement:=NaCo, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
Next lArea
End With
End Sub
View 2 Replies
View Related
Aug 2, 2014
I have tried:
Code:
With Sheet1
x = .[b7:i16,b19:i23,b27:i32].Value
But that only loads the first range (.[b7:i16]).
This does work:
Code:
.[b7:i16,b19:i23,b27:i32].Copy .[z1]: x = .[z1:ag21]: .[z1:ag21].Clear
I would rather avoid having to copy/paste, load the array, then clear. Is there a way to load the array directly?
View 2 Replies
View Related
Jun 28, 2009
I have a little bit of problem with lookup function. When i paste values from another worksheet (paste special, values) in a cell which is lookup value i get #N/A. These values are numbers. When i put '7 for example i get the values i want from lookup table. I have a lot of these cells and its tedious job to put ' in front of every value. Is there a quicker solution?
View 3 Replies
View Related
Feb 7, 2007
I am trying to run create a simple macro that copies and paste special values - something I have done 100's of times but for some reason I keep getting an error message - even though I recorded the macro and didnt write it by hand - see below:
Sub Macro6()
Cells.Select
selection.Copy
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
For which I get 'Compile Error - Expected Function or Variable'
View 6 Replies
View Related
Jan 29, 2014
I would like to implement specific cell ranges from two specific worksheets each within 33 workbooks (which all have several tabs) into a summary page in a separate workbook.
The cell ranges are going across my spreadsheet in rows and I would like for them to transpose into a columns depending on the data which I have separated by catergory on the summary page. They are all on the same location in each workbook which is separated by country. The cell ranges are E26:P37 and I would like to transpose them and have them put below eachother without overwriting for my format on the summary page, how I can put this together in a macro?
View 1 Replies
View Related
Aug 31, 2007
I am trying to have this loop go through colums of data and compare columns 3 and 4. Where there are differences, parts of the row are copied and pasted in a summary table.
My problem is the output to the summary table.
How do I control which cells in the row are copied? (I only want to copy columns 1, 3 & 4)
How do I control where the copied cells are pasted to?
Sub Change()
Dim r As Long
'Loop to Compare Values
For r = 4 To Cells(Rows.Count, 1).End(xlUp).Row
'Looks For Funds That Upgraded
If Cells(r, 4) < Cells(r, 3) Then
'Highlights the row
Cells(r, 1).Resize(1, 5).Interior.ColorIndex = 34
'Copies The Applicable Cell Over Into Column 7
Cells(Rows.Count, 7).End(xlUp).Offset(1) = Cells(r, 1)
'Places a note into column 5
Cells(r, 5) = "UPGRADE"
End If
If Cells(r, 4) > Cells(r, 3) Then
Cells(r, 1).Resize(1, 5).Interior.ColorIndex = 36
Cells(Rows.Count, 7).End(xlUp).Offset(1) = Cells(r, 1)
Cells(r, 5) = "DOWNGRADE"
End If
Next r
Columns("G:G").EntireColumn.AutoFit
End Sub
View 9 Replies
View Related
Mar 13, 2008
I need to make the data in cells f4:f7 skip a row as shown in column D. How can I do this without doing a long and tedious cut and paste as I have over two years of data to reformat. Is there a code or some type of auto-format setting I could use?
View 3 Replies
View Related
Feb 14, 2008
I am trying to copy paste info from one sheet to another, however, I would like to select where the information is to be pasted, not have it go to a predetermined cell or column.
Here is the code I have so far that works fine, but it requires a macro for each column I would like to paste to.....
View 6 Replies
View Related
Jan 31, 2014
I want to paste a group of non-contiguous cells, and paste the values elsewhere on the sheet while maintaining the same relative position. is this possible via macro somehow?
for example, in column A i want to copy A1,A2,A5,A9; and paste into D1,D2,D5,D9 in one step.
View 9 Replies
View Related
Mar 17, 2007
Paste can this be set to default to Paste Special Values only ?
I have a sheet with a number of lists validated drop down boxes. The sheet is networked and works fine.
Problem other users as they are entering information into the required cells they are copying and pasting. Then the inevitable occurs they paste data into the wrong cells the validation from the original cell is pasted as well. Is there a way I can set paste special values only to be the default for the full sheet.
Unfortunately we use excel 97 in the office.
View 9 Replies
View Related
Nov 4, 2009
Working in one workbook, I am using sheet 1 as a master form and copying it to the back of the workbook and renaming it.
View 3 Replies
View Related
Jun 8, 2009
I just want to find out what formula could make a function "copy paste special value"?
View 9 Replies
View Related
May 4, 2006
I have the following code in a macro which works fine. Is there a way to shorten the code down to 1 line?
Sheet2.Select
Range("C2:F2,H2:J2,N2,P2,R2,T2,W2,Y2,AA2").Select
Range("AA2").Activate
Selection.Copy
Sheet9.Select
Range("B3:O3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I would like to use the code in a Select Case Statement & the above code is too cumbersome.
View 2 Replies
View Related
Nov 6, 2007
I have a person who needs to be able to copy and paste values very frequently. She would like me to set up a personal macro on her PC that will allow her to be able to right-click in a cell and have a context item that is paste values so she does not need to go to the paste special dialog continually.
E.g: She selects a range from one workbook and copies it, She then selects a cell in a second workbook and pastes the values into the second workbook. She does not mind pasting the formatting, but she does not want to paste the formulas as there are many named ranges in the formulas and if she inadvertently pastes everything, all of a sudden the next time she opens her second workbook there are links to the first. All she wants in the second workbook are the values, not the formulas.
View 9 Replies
View Related
Dec 21, 2008
I want to put a button on a sheet that will paste special (VALUES) from the clipboard into whichever cell is selected.
What code do I use? I'm using Excel 2007
View 13 Replies
View Related
Mar 14, 2011
I am trying to copy a range of cells and Need to transpose the values. Easy Enough? When I right-click and paste special, it gives me a new window with an array of options but not the standard Paste special value screen. I have used the others in the past and just pasted text. However this does not give me the paste transpose option which is what I need.
I am sure this is a User error where there is something about the way that I am copying this information but I am Drawing a Blank...
View 7 Replies
View Related
Oct 26, 2006
I have a workbook where I want to retain FORMATS in all of my cells in all of my sheets, but allow the user to enter data. So I got this code to undo PASTE and instead do a PASTE SPECIAL VALUES:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim UndoString As String
On Error GoTo err_handler
UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
If Left(UndoString, 5) = "Paste" Then 'Only allow Paste Special|Values
Application.ScreenUpdating = False
Application.Undo
Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.ScreenUpdating = True
End If......................
View 9 Replies
View Related
May 14, 2007
I have a macro that should allow users to change a certain range to values, but I have problems with that last piece. Can anyone tell me what the command is to paste the range as values?
Sub Change_Range()
Dim UserRange As Range
DefaultRange = Selection.Address
On Error Goto Canceled
Set UserRange = Application.InputBox _
(Prompt:="Change Range to Values:", _
Title:="Range Change", _
Default:=DefaultRange, _
Type:=8)
UserRange.Copy
UserRange.PasteSpecial
Canceled:
End Sub
View 9 Replies
View Related
