Copy Paste Special As Values Based On Validation Cell Contents
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
ADVERTISEMENT
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
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
Oct 31, 2011
I have a workbook which contains lots of formula and is therefore very large (about 70 mb). The sheet pulls information from field instruments every hour of every day for the entire year.
I want to write a macro, that loops through a range (B5:B8764) and copies the entire row if the value in column B is less than =NOW() - 7 days. The copied rows should then be pasted with values only exactly where they were located e.g. row 5 should be pasted back to row 5 the only difference being only the values will be pasted.
View 2 Replies
View Related
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
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
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
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
Oct 7, 2009
I am writting code that copys a range and then pastes the values back over the same range, which gets rid of the functions in the cells. I am doing this for multiple sheets. When the code pastes the copied selection back over the same range the code is interrupted by a message box that asks me wether I want to replace the contents of the destination cells or not. I do, how do I get my code to answer yes to this message box?
View 4 Replies
View Related
Oct 4, 2012
Have a slightly unique issue that I can't seem to find the specific solution for. I am wanting to filter a range, using row 1, then copy the visible cells (Excluding the title row 1) and paste into the same location, so over the data as Values.
I can do the filtering part and the selection of the visible row. Only trouble is pasting it to the same location.
this is the code so far - anything better would be great
Sub Macro2()
Sheets("GF_Scoring Database").Activate
Dim extvalue As String
Dim rng As Range
[Code]....
View 2 Replies
View Related
Jul 6, 2008
my destination cells will have formulas like:
='E:My WorkTestData[CCC.xlsx]Input'!$S$31
Everything works fine. But I don't want the destination cells to display the full formula, but the actual Value only
I thought of a way to build a script that first builds the formula to one cell then Copy Paste Special value of that Cell to my desired Cell.
But when the code runs The cursor moves back and forth all the time...
View 9 Replies
View Related
Jan 18, 2009
I know how to use the copy and paste special to copy a formula from one row down many rows in that same column. When I do this the formula will automatically change by 1 number {such as =IF(ISBLANK(C5),"",VLOOKUP($C5,Sheet1! $B$3:D4891,3,FALSE)) then when I click on copy and pasted special each following row will change by one number ---- to =IF(ISBLANK(C6),"",VLOOKUP($C6,Sheet1!$B$3:D4892,3,FALSE))
Can this also be done if the row you want to copy and paste is a row with data validation / a drop down menu? I simply tried using the same process for a row that has data validation the row with the validation has for the source =INDIRECT($B$5)
Is there a way to copy this down about 20 rows and have the ($B$5) change in each row by one number {such as ($B$6 THEN $B$7, ETC...) Or do I just have to do the whole data validation process for each row?
View 3 Replies
View Related
Dec 19, 2012
I am trying to see if it is even possible to have data validation applied to a specified range of cells, but if the value of cell 'B3' is '1' then the data validation will not run on the other cells (H4:G7)
View 2 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
Apr 22, 2014
I tried all failed.We have about 160 different workbooks (one for each business unit) stored online. Staff enters information about their weekly revenue and expenses and here at head office I collect that information and consolidate them.What I am trying to do is that;1.) Create a master Workbook with ~160 worksheets (One worksheet for each unit) named exactly the same with other workbooks2.) And macro can pull the information from related files stored in a certain folderIt is very much like another members problem but I am not sure why I cant get that code working for me? [URL]
View 5 Replies
View Related
Mar 17, 2014
Please see attached sample worksheet. Column A will be generated by the user manually.
I'm looking for a way in VBA to have A1:D20 in Sheet2 copied and pasted in the "Bank Reconciliation" Sheet based on how many "Markets" there are in Column A. Then, once that's complete to have A22:D30 (the smaller box in Sheet2) copied and pasted directly below those results.
I have what the macro would hopefully generate to the right in "Bank Reconciliation" (B6:E54) as an example. So if there's a market in A1, copy and paste the box to B6. If there's a market in A2, copy and paste the box directly below the first (B26) etc. etc. until it's done, then paste the smaller box directly below whatever the macro generates.
Book2.xlsx
View 3 Replies
View Related
Aug 29, 2008
I'm trying to find a macro that will copy data from the areas of B120:E179 and I120:K179 for example (linked to another worksheet within the workbook) and special paste (Values Only and skipping blank cells) it to the next available open cell up top where basic data entry will be taking place B10:E29 and I10:K29. I need it to only copy/paste the rows with data (skipping all cells/rows with no data) and once it is finished coping I will need it to place an "X" in column M next to the row that it copied data from. I would also need it to reference the data in each row from B to E and if there is an entry say on B14 to E14 that matches it but if I10 to E29 are blank then paste that information on row 14. If it does not match or if those columns are full then paste on next available line.
I hope I'm making sense here. This is for a vehicle tracking log between checkpoints. Each driver and info will be listed on each row. Columns B through E will contain information for each driver: name, badge, #passengers, and vehicle #. The log lists location, time, and destination for outgoing travelers in columns F to H. Incoming info is listed on Columns I to K............
View 4 Replies
View Related
Jun 6, 2014
I have a line of code that returns a run-time error 1004 whenever it is passed through. All I am trying to do is copy and paste. I am missing some glaring error? (It is only a selection of the code to highlight the part I am having issues with. "maxdate" and "d" have been set)
VB:
Dim ws, ws1 As WorkSheets
Set ws = ThisWorkbook.Sheets("Data")
Set ws1 = ThisWorkbook.Sheets("Target")
[Code].....
View 5 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
Mar 24, 2007
I am trying to write a macro to do the following: Loop through a range of cells on a worksheet Sheet1 M17:M46 and if there is text in the cell then Copy all the data to the left Of Coumun M in the same row and paste the values to the next open row of a range A17:L46 on another worksheet (sheet2.) Basically the text in column M is an idicator to add the data to an order sheet. If there is no text don't add the data , look in the next row etc.
I'm not sure if this is the way to go
Sub CopyRows()
Worksheets("sheet1").Select
FinalRow = Range("M65536").End(xlUp).Row
' Find the last row of data
' Loop through each row
For x = 2 To FinalRow
' Decide if to copy based on column M
ThisValue = Range("M" & x).Value
' could be any value
If ThisValue = "yes" Then
Worksheets("sheet2").Range("A" & x & ":L" & x).Copy
Worksheets("sheet1").Select
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
ActiveSheet.PasteSpecial = xlValues
Worksheets("sheet1").Select
End If
Next x
End Sub
View 4 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
Mar 27, 2009
I set up a spread*** with drop-down menus for some of the cells. The user can only select values from that list and an error message pops up when something is typed it that is not in the list (via Data Validation
Error Alert).
So this works all fine ... except if the user copies and pastes a cell with a different drop-down menu into a cell overwriting the existing validation.
I cannot totally lock the work*** and if I lock the cells with the drop-down menus nothing can be changed. So how can I prevent this from happening? Is there a way to block all copy/paste operations that are copying the validation? I know that with the Paste Special I can have cells copied without the validation.
View 14 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
