I've created an Excel 2007 workbook with about a dozen worksheets that are full of formulas and links to other workbooks, as well as conditional formatting that isn't backwardly compatible with Excel 2003. However, many people who want to see this workbook are still on Excel 2003. I need to recreate an Excel 2003 compatible workbook with the appearance and formatting of the original and have all the formulas and links converted to values. So, the final product will "look" just like the original without the conditional formatting, links or formulas. It will just have the look, colors and values displayed for all cells.
Essentially, I want a macro that will copy however many worksheets (1-n) are in an Excel 2007 workbook to a new Excel 2003 workbook. I'd like the worksheets to retain their label names as well.
However, whenever I run the code, if there's even on word that is bold in the cell, then the entire text string in the cell turns bold. How can I stop this from happening? i want to keep the format of the text string the same, only remove and replace the items listed in the code.
I need a code that will search column A over multiple sheets for a user specified value through an input box. I then need this code to copy and paste the entire row to a new workbook titled "Batch Report" Anyone have any ideas? I have a code that will copy and paste only a few rows but no where near all of them, and I cant figure out why.
I have 2 workbooks, one is my source of data and the other is the destination workbook. What I need to do is to copy the row data when the checkbox is marked and the criteria was met to the destination workbook. I have the code below:
VB: Sub CheckBox5_Click() 'uses formulas in an empty column to spot all appropriate rows at once Dim LR As Long, NR As Long, wbMASTER As Workbook, wbNAME As String wbNAME = "C:UsersPublicDocumentsSPOT_MB.xls"
What I want to do now is to :
1. Create a checkbox that will run the code to copy the row when the criteria is met to the next empty row of a master file. 2. Keep the master file open while the data is updating.
I have an existing macro that takes my sheet and puts it in body of email.
how do i add to it to change the entire worksheet's font?
Code: Function RangetoHTML(rng As Range) Dim fso As Object Dim ts As Object Dim sTempFile As String Dim objWB As Workbook Dim i As Long Dim iLastRow As Long sTempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
Code, couldn't make it work with VB HTML Maker. I'm new with VBA Basically my idea is when someone open default file, insert data, try to save file, macros save workbook with inserted data as another file (*.xlsx) and make pdf in another folder. Pdf part is working fine, but with second part just can't find right option.
Private Sub Workbook_AfterSave(ByVal Success As Boolean) Dim Name As String Dim i As Integer, j As Integer Name = ThisWorkbook.Path & "PDF Archive" & ActiveSheet.Range("F6") & " Invoice " & ActiveSheet.Range("B11")
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim ActSheet As Worksheet Dim ActBook As Workbook Dim CurrentFile As String Dim NewFileType As String Dim NewFile As String
I ran a simple macro that I created in my workbook, got a run-time error, clicked debug, clicked reset to stop the macro running, and now all of the icons in my workbook are greyed out. I can select cells, change worksheets, enter information in cells etc....just I can't save the workbook, or select any of the icons on my ribbon / in the office menu. Would really like to avoid terminating the application as I made a number of changes that I didn't save (doh doh doh).
I have a macro that will go through a set of numbers and check them against a condition. if the cell complies with the condition i want to copy the entire row to a new sheet. sofar I have tried the following to select the current row but to no avail.
I have an Excel database of department contracts with a Master worksheet. I have created a worksheet tab for each of the departments. Column G has a dropdown list for each department. When I enter a new contract onto the Master I want it to auto copy to the coordinating worksheet based on the selected department.
If possible I would also like it to enter the newest contract would enter into the coordinating Contract Party entered into column D. (ie... If Hospital A is entered in column D on master, the new entry on the worksheet being copied to will be entered under the last Hospital A, if Hospital B is entered on Master it copies under Hospital B). If this isn't possible then I am not too worried about that part.
I have attached a blank version of the file. Current Master 5-15-14.xlsx
I got a macro to copy and paste values onto another tab within my worksheet. I have a lot of data and currently takes about 30 seconds to calculate and paste. Not sure if its an issue with my macro or with my computer (Mac - Excel 2011).
Here is an example of my macro:
Sub SimulateWeek() If Range("AdvanceWeek").Value = "Week 1" Then Range("Week1B").Copy Sheets("Schedule - Results").Range("C2").PasteSpecial Paste:=xlPasteValues
(this continues on until 'ElseIf Range("AdvanceWeek").Value = "Week 31"....etc). So you can see I have the same code repeated 31 times.
We have a workbook that we create each month that has one worksheet per day of the month, labeled 12_01_2011, 12_02_2011, 12_13_2011...etc. The pages are an empty template with formulas and fields in place that we simply copy and paste the results of an SQL query into. Presently, we are copying the page manually several times over, and then manually renaming the pages with the new dates for the upcoming month.
So, here is my question. Macro that I might use that would:
1) Make a copy of the template for each day of the month.
2) Label each page in sequence with the dates for the upcoming month.
First, I can get the balance formula, and when I drag the square at the bottom right, I am able to drag the entire formula down...I want to do that. However, when I drag it down, the number keeps popping up. I was wondering if there is a way to be able to drag the entire thing down and be able to make it so that it is hidden (the cell is blank) unless the debit or credit cell in the corresponding row is filled. Also, if possible, I want to be able to drag it down to infinity, so it can go on forever, so that there is no need to always drag it down when I add more things in it. I am attaching what I have till now.
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
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 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?
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.
How is code or functions kept unique to a workbook? for instance i create a few toolbars that are relevant to "work book 1", however when i open another workbook "2" the toolbars do not function properly or are removed by the opening/closing of the work book, similarly other macros seem to struggle with more than one workbbok open at a time.
I need the macro to look at cells B9:B84 on the Sheet1 tab of the Cost Template. If it finds an x I need it to copy the 3 cells to the right of the x and paste them in a template. For example if it sees an x in cell B9 it would copy cells C9, D9 and E9, open the Purchase Order to the Detail tab, then paste it to cells B3, C3 and D3. It would continue looking for an x down to B84. So if it found 5 cells with x, it would give me 5 instances of the Purchase Order with 3 cells pasted into each.
I've attached my Cost Template and the Purchase Order it needs to copy to. In the Cost Template is a macro called Create_PO. This is what I was trying to alter to make this happen. I can't seem to get it right! FYI in case it matters, I had to change the Cost Template from .xltm to .xlsm in order to upload it on this site.
I have 2 workbooks , each with one sheet that is populated. I have tried to record the macro where by, I open the first workbook and then want the second book opened, copy the information on the 2nd workbook, paste it into the 1st workbook, close the second workbook. I have assigned a macro to it but when it opens all it does is open the second workbook.
I have a spreadsheet with multiple worksheets in a spreadsheet which has been distributed and filled out by others.
I would to be able to: -open each returned spreadsheet (by browsing and selecting the file) -click a macro and have the macro select information from a range for each worksheet and have that information copied into the relevant worksheet in a master spreadsheet. I.e workbook1 to workbook1, workbook2 to workbook2
-during the copying process the information should be filled in the next empty row in the master spreadsheet
What it does is on the menu sheet is the workbook destination (i1) and it copies the range from that workbook to the original workbook and pastes it into "sheet1" but the below brings up a mesage box about data being on the clipboard.