Excel 2003 :: Macro To Copy And Paste Special Values From Different Workbooks Into One Workbook
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
ADVERTISEMENT
Feb 23, 2008
I often need to use Paste Special . .. Values, using Excel 2003. So I recorded a Macro into "Personal.xls" and assigned a shortcut key combination "CTRL-Shift-V" so that it would always be available. I first select one or more cells and hit CTRL-C to copy to clipboard, click on another cell, then hit CTRL-Shift-V to run the following macro:
VB : Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
It often works . . . and it often fails. When it is working it seems to keep working over and over. When it is failing it keeps failing. So it is almost as if there are two "modes" of operation, which for lack of terms, for this post I will coin the terms "fail mode" and "fixed mode". Once I am in one of the two modes, it stays there for a while. When it is in the "fail mode", I always get the dreaded:
Run-time error 1004: "Pastespecial method of Range class failed".
Since this problem is very intermittent, I believe this is why there are so many posts in the Internet about this, and so many people are replying back "it works fine for me". I have seen many solutions offered but none work so far for just a simple shortcut key to do a PasteSpecial :Values. Some solutions "work" . . . such as adding the PasteSpecial button to the Toolbar, or pressing ALT-E-S-V <Enter> - but both of these bring up the Paste Special dialog box which I want to avoid.
I spent days trying the many solutions offered but so far all I have found is a trick to convert Excel from "fail mode" to "fixed mode". The trick is to add a line to the macro to first do a "Paste Special: Format:
VB:
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Then select and copy any cell/s, click on another cell, and run the macro just ONCE - it will paste the formatted value successfully and you are now in the "fixed mode". Now you can remove the PasteSpecial:Format line that you just added, to get back to the original macro with just the "Paste Special: Values" line:
VB : Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Now Excel is in the "fixed mode" and the original macro will once again start working properly. BUT only for a while !! Then some time later, usually within a day or a couple hours . . . Excel goes back into that "mode" where once again the macro fails repeatedly. I have been unable to isolate what causes the mode to return to failure.
Any simple way to either change the code so that it ALWAYS is successful, why this often is not successful ? I believe that the fact that adding in the Paste Special : Format code and running it just once to fix the problem, also could be a clue as to what causes this problem to "sometimes" occur.
View 9 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
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
Nov 9, 2012
Not sure if it's me or a new change in excel 2010, yet creating a simple macro as listed below does not work.
Outside of macro I will select a range of data and copy.
Then, using macro I'd like i to perform: paste special values, in the current cell
View 5 Replies
View Related
Aug 15, 2008
Currently I have a macro which successfully updates a value in the report page, creates a new document and then copies and pastes (as values)that sheet into the new workbook and saves it down as a unique file.
I need to have graphs on this new workbook. So gathered creating a template and getting the macro to open this template, copy the ranges and then paste into the template (which will update the charts) and then save down.
The ranges i need to paste in are: B5:D7, and B11:P37 the destination cells are exactly the same.
The current code i have is: ...
View 4 Replies
View Related
Oct 21, 2013
i have a macro that opens 10 other workbooks and copies cells onto a "master" workbook. Until recently, everything worked fine. Suddendly, while it opens the 9 books and copies as it is expected, but when it opens the 10th wb, the macro breaks at a very simple copy/paste.
The weirdest thing is that after it breaks, I cannot select any cells neither in the opened wb nor in the master wb. This continues even after I press the "reset" button in the vba. So, I am only able to select objects (text boxes etc) in my wb and not any cells.
Also, the "arrow" icon on the design toolbar is not active. And I've tried pressing and de-pressing the F8 key, but I still cannot select any cells.
I have option explicit in the beginning of my macro. And i'm using excel 2003.
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 8, 2013
I'm trying to use a macro that will copy & paste values from one workbook to another. Everything works fine until I try to get it to Paste to next available row.
It worked the first time, but after that it jumps, several hundred rows at a time.
This is what I have so far:
Code:
Sub CopyData()
Workbooks.Open Filename:="S:Production OfficeMonthly KPI Reports.xlsm", ReadOnly:=False
Windows("Weekly KPI Plan.xlsm").Activate
Worksheets("Data Input").Select
Worksheets("Data Input").[A4:AT650].Select
[Code]....
View 3 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
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
Jul 23, 2009
I am having problems with 1 of my macro's and I am at a loss as to why!
I anticipate there is a simple answer and I apologize in advance, but....
This line of code is no longer working
View 6 Replies
View Related
Apr 30, 2008
I have a spreadsheet with 2 macros in it, one for copying cells and deleting contents/comments etc.
Selection.Copy
Selection.Interior.ColorIndex = xlNone
Selection.ClearContents
Selection.ClearComments
Application.CutCopyMode = True
and another to Paste Special (all except borders)
Selection.Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
The first macro works fine but the second macro comes up with a runtime error '1004' Paste Special method of range class failed.
View 2 Replies
View Related
Jan 25, 2012
Excel 2002.I have data in columns A and B on sheet 3, starting at row 100. The total number of rows of data is variable (max is 50 rows). I want to take the values in A100:B100 and copy them from Sheet3 and paste values transposed to SheetTL Range C1:C2. I have started to write the following code but as you can see I will have to repeat the code upwards of 50 times. It seems I should be able to do a COUNT function to see how many rows of data exist and then somehow loop the code to copy the data from each subsequent row that many times, but how to put that together.
Sheets("Sheet3").Range("A100:B100").Copy
Sheets("TL").Range("C1").PasteSpecial Paste:=xlPasteValues, Transpose:=True
ActiveSheet.PrintOut
If Sheets("Sheet3").Range("A101") = "" Then
[Code] .........
View 5 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
Aug 9, 2009
I created a macro that copies a range from one worksheet to another as shown below, which correctly copies everything including formulas.
View 9 Replies
View Related
Apr 6, 2013
I have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.
I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.
I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need
A prompt to open workbook
Copy range (c8,d69)
Close work sheet
Paste special .value (c8,D69)
I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.
week 1 week 2 week 3 week 4
Product
quantity
cost
quantity
cost
cost
quantity
cost
1
2
3
4
5
6
7
8
View 7 Replies
View Related
Feb 23, 2008
I often need to use Paste Special... Values, using Excel 2003. So I recorded a Macro into "Personal.xls" and assigned a shortcut key combination "CTRL-Shift-V" so that it would always be available. I first select one or more cells and hit CTRL-C to copy to clipboard, click on another cell, then hit CTRL-Shift-V to run the following macro: Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
It often works . . . and it often fails. When it is working it seems to keep working over and over. When it is failing it keeps failing. So it is almost as if there are two "modes" of operation, which for lack of terms, for this post I will coin the terms "fail mode" and "fixed mode". Once I am in one of the two modes, it stays there for a while. When it is in the "fail mode", I always get the dreaded: Run-time error 1004: "Pastespecial method of Range class failed".
Since this problem is very intermittent, I believe this is why there are so many posts in the Internet about this, and so many people are replying back "it works fine for me". I have seen many solutions offered but none work so far for just a simple shortcut key to do a PasteSpecial :Values. Some solutions "work" . . . such as adding the PasteSpecial button to the Toolbar, or pressing ALT-E-S-V <Enter> - but both of these bring up the Paste Special dialog box which I want to avoid. I spent days trying the many solutions offered but so far all I have found is a trick to convert Excel from "fail mode" to "fixed mode". The trick is to add a line to the macro to first do a "Paste Special: Format:
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
...................................
View 8 Replies
View Related
Mar 4, 2009
I have a workbook with many sheets in it. Within each sheet there is a cell with the Text "March 09" there are then 3 cells to the right of this cell with relevant information. Is there a Macro that can search for this text March 09, then copy this and the 3 cells to the right of it and paste special this information 1 cell below for all 4 cells.
View 2 Replies
View Related
Jan 10, 2012
Can a macro make a workbook everytime you copy, it will paste special formulas only and skip blank rows? And can I still let me select the range manually? I would like to use this to link workbooks.
View 2 Replies
View Related
May 9, 2014
I'm using Excel 2013 on Windows 7.
I have an Excel file which has a macro that sucks in data from a number of separate files (two Excel, a varying number of csv). The macro grabs all the individual files and loads them into one of three worksheets with some formatting, sorting, and structuring on the way. All good so far.
I then need to take two of the worksheets and copy them to a new Workbook which I can send out to some users. So I use the Workbooks.Add method, and use the Selection.Copy on just the data (UsedRange.Rows/Columns.Count to ensure it is only the data) and Selection.PasteSpecial with the xlPasteColumnWidths, xlPasteValues, and xlPasteFormats options to copy the data over. None of the data is filtered. Just straight data.
I would expect the newly added workbook to be smaller than the one with the macros because the one with the macros has two extra sheets that I don't transfer, and the sheets I do transfer are identical.
And that's where it gets weird. The file with the macros and more data is 18.7Mb, but the new one with only two sheets is 24.8Mb. One is .xlsm and the other is .xlsx (because it has no macros).
I've tried opening the new workbook and saving as .xlsm (no material difference to the size) and as .xls (it got even bigger). I've opened the new workbook, gone to the end of the data and deleted all the blank rows and columns, but no effect (because the rows are already empty).
Why would the file with less in it be so much bigger (or bigger at all)?
View 2 Replies
View Related
Mar 18, 2014
I am trying to copy one worksheet using the "move/copy" function that is available when you right click a tab name and want to copy the worksheet in the same workbook
The steps I'm using are:
1. Right click the tab name
2. Select Move or Copy,
3. Select Create a Copy
4. Click OK
Doing all of the above does not work. When the new worksheet opens, all columns are the same width. It seems to be stuck on "autofit column width" setting of 8.5. The original worksheet is several columns wide all with different width settings.
I've also used Copy, Paste Special and selected column widths and that does not work either.
View 3 Replies
View Related
Jul 25, 2006
I have two files; Budget & Actual. Wish to copy the value in "B17.B71" from "Budget" file, sheetname "ABC" to "E8" under file "Actual". I tried this macro from my notebook and it works fine. However when I tried it on the company desktop, it fails to work. Error message: Runtime error '9': Subscript out of range.
(Windows("Actual").Activate" is highlighted)
Sheets("ABC").Select
Range("B17.B71").Select
Selection.Copy
Windows("Actual").Activate
Sheets("XYZ").Select
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
:=False, Transpose:=False
View 3 Replies
View Related
Jan 8, 2012
I'm trying to use a VBA Macro to copy and paste a row of cells from 14 worksheets into in another worksheet in a Colum. The worksheets are entitled Data 1, Data 2, Data 3 and so on until Data 14 and the worksheet that I want to paste the data to is entitled sheet 1. The row of data that I want to copy and paste is in the same place on each worksheet C4 - IR4. I can do this using the Macro recorder but there is a lot of code and ultimately I want this to be part of a much bigger Macro once I get better at figuring this stuff out.
I have read that this can be done by using a Do Until and then using a loop. By the way I am using Excel 2003 with XP.
View 6 Replies
View Related
May 25, 2014
I have around 200 excel spreadsheets/workbooks with identical ranges but each with different data. There is a total of 5 columns and 225 rows in each spreadsheet/workbook. Looks something like this:
Workbook 1:
Title 1
Title 2
Title 3
Title 4
Title 5
A2
B2
C2
D2
E2
[Code] ....
The Cells I need to copy are in BOLD. I am trying to paste them onto another workbook as follows
Main Workbook:
Workbook #
File 2
File 3
File 4
A2
Title 5
A3
Title 5
[Code] ....
As you can see, each workbook has identical A2 and Title 5 columns, so they only need to be copied once onto the Main workbook where data from the B and E columns are different for each Workbook. So not only I need to copy and paste from a Workbook onto the Main Workbook, but the code has be able to paste it onto a new row in the Main Workbook (where each row in the Main Workbook will correspond to the data copied from Workbooks 1-200.
I am new to VBA but I tried a code where I would copy and paste one cell at a time onto the same workbook and not onto the Main Workbook, and then how to copy and paste from one workbook to another, but as you can imagine that would take a long time:
VB:
Sub dAmacro()
Sheets("Workbook1").Select
Range("G1") = "Workbook #"
Range("H1") = "File 2"
Range("I1") = "File 3"
[Code] .....
It seems like I need some kind of loop, where it would copy and paste a set of cells and repeat the entire process until it reaches the end of the Workbook 1 while making sure when doing same thing for workbook 2, that the data pastes onto a new row onto the Main Workbook. I feel I have the logic down, but its in the syntax where I am failing.
View 9 Replies
View Related
May 25, 2009
I had to copy data from child woekbooks (*.xls) and paste it to the master workbook with same page to page every time when a macro is executed i had done the copy and paste part
But I'm Facing the problem in which i had to deal with
Validations as on both master and child sheet validation (column based combo box is activated )
one is worktype
2ns is time type
i jst had to copy data to the master macro works perfectly fine but the problem is that a msg box appears which signifies that i had to change the name (version ) for both types when i click yes 2 times it pastes the data
I'm attaching my macro as well as pic of that msg box with this attachment
View 6 Replies
View Related
Sep 20, 2012
how to loop through workbooks in a certain directory and copy the rows in sheet1 where column B contains numbers greater than zero, and then pasting them into a new master workbook. The sheets will be named differently each week but will always be in the same directory.
View 4 Replies
View Related
