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.
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.
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
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?
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.
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" ......................
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...?
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)
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.
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
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.
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:
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...
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
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
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?
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'
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?
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
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?
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.....
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.
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.
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.
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...
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......................
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