Gosh, the workbook I'm copying the graphs from is only 4 mb in size but after I copy and paste those graphs to another workbook (empty one) it became 122mb in size
Does any of you know why this has happened and how to copy the original size graphs to new workbook?
I'm having a little bit of trouble with my code. I basically need to copy-paste special a large workbook (50 worksheets) into a new range-valued workbook. I've defined Sheet as S, but in some instances when I use the variable "S" I received a runtime error 1004.
Please see the highlighted red "S" in the code below.
Also, if one knew of an easier method for this particular operation that would be great!
Sub PasteSpecial()
Dim W As Workbook
Dim S1 As Worksheet
Dim S As Sheets
Set W = Workbooks("newfile2")
Set S1 = W.Worksheets("Sheet1")
Set S = Sheets
S(Array("Sheet1", "Sheet2", "Sheet3")).Select
S1.Activate
S(Array("Sheet1", "Sheet2", "Sheet3")).Copy
Cells.Select
Range("a1").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Paste:=XlPasteFormat
S("Sheet2").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Paste:=XlPasteFormat
S("Sheet3").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Paste:=XlPasteFormat
MsgBox "New Range-Valued Workbook has been created"
Application.CutCopyMode = False
End Sub
Copy data from workbook, open existing workbook, select range and paste. But my copied data is lost.
Sub Select_Copy_Paste()
'
'
Windows("ElektroFunctiesDatabase.xlsm").Activate
Sheets("PowerSupply's").Select
Range("A2:I6").Select
Selection.Copy
[Code] .........
' Here i need to do something to paste data into r.address?
I m not able to use the standard Excel Paste Special function when I copied a range of cells in another Excel workbook opened in a separate instance of Excel. Instead, Paste Special thinks that I have copied some non-Excel objects and gave me the Paste As options. This is not the case if I open both workbook within the same instance of Excel. Could you share with us if there is a trick to trigger the normal Paste Special options in such situations (without having to invoke Macro procedures)?
View 2 Replies View RelatedI have been trying to edit a code which previously saved a copy in a new workbook to a specific folder/path. (Additionally it copies and clears some figures, but this is working as it should.)
However I would like the copy to be saved at the same location as the original workbook, regardless of the path the original workbook is saved.
I.e if I need to move the workbook containing the code to a new folder/location, when using the macro, the new copy should be stored in the same folder/location as the original one.
For now it is only saving the copy into "My Documents"
Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 16-02-2009 by ceng
'
Sheets("Bunker ROB").Select
Sheets("Bunker ROB").Copy
ActiveWorkbook.SaveAs Filename:= _
ActiveWorkbook.Path & Range("D3"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
[Code] ........
I am having a problem with a workbook, I have copied all cells and pasted Special (Values only). But for some reason the workbook is 38 Meg's. The workbook only contains 6 worksheets with about 60 rows and 32 columns with figures and no formulas. I have tried to find formulas by Goto/Special/Formulas and Excel has found nothing. What shoud I do?
View 4 Replies View RelatedI run a model in Excel that automatically saves my file every xx iterations. After saving the file I want to make a backup of the file. Tried
[Code] .....
but get a permission denied error message.
I don't want to use .SaveAs as it is a huge file that takes a while to save and SaveAs has a tendency to break links that should not be broken..
I have an open workbook (A) and this is where the code should reside. I want to use VBA to copy the content of an entire worksheet from a closed workbook (B) to an existing worksheet in workbook A. How would you accomplish this?
View 7 Replies View RelatedI am looking for a code that would copy the data from each worksheet in a given workbook and then paste to just one worksheet within a different workbook. The Sheet names are auto generated when I run this canned report but the naming structure is always the same...the first worksheet is named Repair Details and then the next sheet is named Repair Details_1, the next sheet is named Repair Details_2 and so on for every sheet in workbook. So I would like to copy all of the data(Headers to last cell) and then paste in a worksheet(ex: Master Repair Report.xlsx and the worksheet could be titled Master Repair Details) on a different workbook, then the next sheet would copy from the one under the header to the last record and paste to the same workbook. This process would repeat for every worksheet in the Repair Details Workbook and paste to Master Repair Details worksheet in the Master Repair Report workbook.
View 4 Replies View RelatedI have 2 workbooks. 1 is where data is entered called wksPB, the other is like a database workbook. The wksPB has data to be feed from column B to F and there is a dropdown.
what im looking to do is, if the combobox value is selected as Decline then it should display a messagebox that reference value in cell B is denied. if combobox value is Agreed then the macro should search the database workbook for the text entered in column b of wksPB and then copy data of C:E of wksPB to H:J of database sheet where that text is found and also the approver name in wksPB C24 to the approver cell of where that text was found. I've reached till finding the text but what I get my head twiting on is how to copy the text from wksPB column B:F to database sheet column H:K. Im attaching the sample workbooks and the code where ive reached till.
[Code] .....
Attached Files
Replacement Records - 2014.xlsx
forum file.xlsm
I need to copy a range of values (actually two ranges). The ranges are of fixed size. Le't say A2:D20 and E2:H20.
Both workbooks have the exact same layout, and they both have the same named worksheet (in this case it's called Entry). However the actual filename of the source workbook is unknown. I know numerous users have changed the filename of the xls file.
I want to instruct them to open the old and new workbook, and open a third workbook containing the copy macro, they run the macro and it copies the data.
How can I reference a known worksheet name but of an unknown workbook name esp when the source and destination share the worksheet name?
Here's my macro:
Code:
Sub CopyRow()'
'Copies row to new sheet, highlights it, marks column 'A' as copied.
'
Dim cCell As Range
Set cCell = Selection.Cells(1, 1)
Selection.Copy
Sheets("Sheet2").Select
Rows("2:2").Select
[Code] .....
Is it possible to modify it to paste into a different workbook called c:filesDestination.xlsm, instead of the existing workbook (Source.xlsm)? The destination sheet name is the same (Sheet2). It's OK if both workbooks are open at the same time.
I have some vba that opens a closed workbook, copies data from a named range and then pastes it to the active workbook.
However, what is happening is that the closed workbook is opened and only part of the data is pasted. What I would prefer to happen is this:
Open the closed workbook-->copy the named range-->paste(append) to next empty cell in column B.
Heres the code that I have got.
Sub Workbook_test()Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open("G:WAREHOUSEPlanningSmartNew Training Plan raining plan.xls", True, True)
[Code]....
On one workbook I have "column A" that lists serial #'s, one serial number per cell. What I need is to be able to take all the non blank data from that column and copy it to another workbook with all those serial #'s in 1 cell numerically sorted and separated by commas.
View 1 Replies View RelatedI would like to use a command button to copy a row of data from one workbook to a column in another workbook. The row of data will have different values on a daily basis but will always be B2 through BE2. I would like the copied row of data pasted in the next available column to the left in the other work book.
View 3 Replies View RelatedI have multiple workbooks ( with unique names) under same folder. Each workbook has "Report-Corn" sheet.
I would like to copy cell "P15" from each (workbook-> report-corn -> P15) and paste to Master.xls workbook -> Sheet 1 one after another which is also located under same folder.
I have a large Excel workbook (about 70,000KB) and it takes ages to load and is slow to operate.
Is there anyway of compressing it or making it run faster and not slow down the computer?
I have a large model which basically imports data into a sheet, pastses extra columns with formulas into the data sheet, refreshes some pivot tables based on all the data in the data sheet and then refreshes a report with the data from both the data sheet and the pivots.
My problem is that the model is incredibly unstable. It will usually only let me run the process about 1 or 2 times before causing Excel to have a severe error, and crash. That xls will then become corrupted, and then not work again. However, if I restore a backup version, it will always run fine again, regardless of the data, 1 or 2 times.
In the past, it seems like the error may be tied to using either:
Sheets.add
or
Sheets.delete
though I think this is a syptom, rather than the problem. VBA will actually just not perform these commands when the sheet is corrupted.
I have a workbook that has a lot of formulas in it that reference material stored on my local computer.
I need a macro that will copy all 42 tabs in to a new workbook book without all formulas, but saving all the formats have made. This would save me from every other day coping and pasting manually each of the pages.
I have this nice macro that copies data from another workbook. I want to make it to also add the filename of the workbook (for example data.xlsx) it copies the data from, in a cell on the actual workbook (for example on sheet "Combined", Cell A10). What should I add to my macro?
View 3 Replies View RelatedI have a sheet named sheet9 and i want to copy the data from this workbook-sheet9 to a workbook called import data and sheet named "database".
View 5 Replies View RelatedI'll soon have an ETL process that will load about 150K rows into an Excel workbook. On of the columns will be the end user's userid.
I need to autofilter that external workbook based on the end user's userid, copy that range, clear a worksheet on the current workbook, and copy that range to the current worksheet.
For example, see the attached workbooks. I need to replace the data in Source.xlsb!Cases with the data in Output.xlsb!Case_List, filtered on my userid, which we'll call foo.
My requirement is as follows......
I want to get a copy of worksheet from specific workbook to active work book in which i want a copy of sheet get moved.
I am creating a spreadsheet to provide to a group of about 40 people. The spreadsheet allows each member to track the referrals they receive and give. I need to plan on creating updates for the spreadsheet and want to make it so they can easily transfer their data to the most current version in the future. Here is a breakdown of the workbook:
Worksheets:
Members
Lists
Referrals Given
Referrals Received
Totals
Formulas
I need to copy the data that they have input from the following worksheets:
Referrals Given
Referrals Received
Here is a breakdown of the "Referrals Received" worksheet:
A1:V4 - Data that will not change and includes many named cells
A5:Nxxx - Dynamic Named Range where they input their data
O5:Vxxx - Contains formulas
I need to be able to copy
A5:Vxxxxx
from the old workbook to the new
Some side items to note:
There is VBA in the worksheet of
Private Sub Worksheet_Change(ByVal Target As Range)
and included in the code is an Unprotect & Protect code to always keep the sheet protected
I figure whatever works for this sheet will work for all the sheets.
I need to copy several macros from the Personal Macro workbook to a new workbook that I need to take to another computer. I need to be able to run the macros on that computer. I can only record macros and make minor changes in the VB editor. I do not know VB code.
View 5 Replies View RelatedI have a very brief question
dlgAnswer = Application.Dialogs(xlDialogOpen).Show
Selection.Copy
Windows("Derivative YK pricing Mod G.xls").Activate
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("C5").Select
Application.CutCopyMode = False
Windows("EXPORT1.xls").Activate
ActiveWindow.Close
As you can see, I open a workbook with the dlgAnswer, I was wondering how I could close that opened workbook
Currently I have
Windows("EXPORT1.xls").Activate
ActiveWindow.Close
however the workbook will not always be called export1.xls, I want it to close whichever one I opened earlier.
I am looking to create a summary page to show open items within a log of initiatives. The workbook has 21 different tabs logging initiatives by the each individual person and also 9 other data tabs. On the tabs containing the initiative logs, it states whether the initiative is open or closed and I only want the summary tab to show the open items. Is it possible to do this?
View 1 Replies View RelatedI'm writing an application in Excel, its a little over 5MB after I've run through UsedRegion and trimmed stuff, 94 worksheets. I'm encountering a fair number of crashes these days. My OS is fully patched as is MS Office. Running Windows server 2003 and Excel 2007.
I was wondering about the pros and cons of writing an application in one large workbook or many smaller ones. So far its been one large one but these crashes are really impeding my progress.
What's the best way to remedy these crashes or even start to diagnose?
I have a Picture in a workbook kept open. ( workbook 1 )And i have some X workbook open....i want a copy of sheet from workbook 1 To X workbook which is currently active.
View 1 Replies View RelatedWhat I am trying to do is when data is changed in a workbook, I would like to be able to have it automatically copy the changes to another workbook.
View 2 Replies View Related