This is the error i get by doing> edit> move or copy sheet
A formula or sheet you want to move or copy contains the name 'HTML', which already exists on the detination worksheet. Do you want to use this version of the name?
I want to do is copy a sheet into the same book. If I click yes, two more errors pop up.
I am currently using a macro to copy a sheet from a closed workbook in to my current workbook. However this copying is based on the sheet name. At present when I run the following code
View 11 Replies View RelatedI have managed to use ADO to copy data from a closed book to my active sheet. Problem : it copies only the text, and not the dates or figures...
Code:
Sub TestReadDataFromWorkbook()
' fills data from a closed workbook in at the active cell.
GetDataFromClosedWorkbook "H:P&LYE TempDiv P&LP&L Report 020312.xls", "A1:Z1000", Range("A1"), False
End Sub
Code:
Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _
TargetRange As Range, IncludeFieldNames As Boolean)
'GetDataFromClosedWorkbook "C:FolderNameWorkbookName.xls", "A1:B21", ActiveCell, False
'GetDataFromClosedWorkbook "C:FolderNameWorkbookName.xls", "MyDataRange", Range("B3"), True
[Code]...
I have one sheet for each day of the month with a table that has Employee Name, Reason, and Expected Return. I need to copy each line into the monthly sheet, but each day may have varying number of rows. Needless to say I would like to do this without copying lots of blank lines into my consolidated sheet.
View 10 Replies View RelatedI have an excel file which I use when I book keep journals in work. Each time I come across a new piece of bookkeeping I add to the file, name the sheet and index it. (I've attached a loose example for illustration) so the bookkeeping template.xls gets bigger every day.
Currently, when I book keep on a particular day, I open the template.xls workbook;
draft my journals;
and then manually copy the worksheets I have completed during the day from the template.xls workbook and paste the sheets into a new book and save the days bookkeeping as the current date.
With the file I have attached can someone show me how to write a macro and apply it to each of the buttons on the various work sheet tabs (each button will run the same macro).
Upon clicking a button, I would like the macro to;
Copy the active sheet into a new book and save the file as the current date.
Each subsequent click of a button the macro will check if the .xls file exists for the current date, if it does, then it will just copy the active worksheet to the file that has already been created.
In the new file which is saved as the current date, I need to remove the macro button and the hyperlink that reads back to index.
I have had some helpful macro's provided from another forum, however, the other forum does not appear to enable users to upload files, so I can't quite convey what it is I am trying to achieve.
I have the below code, which now looks to see if a file is open or not, if it is, then copy and past 'Data' and if not open the book and copy 'Data'.
I think the code is sort of right, but im missing something, as i keep getting runtime error when i try and copy. Here is the
Sub PrintSaveKPIUpdate()
Dim sFilName As String
sFilName = "C: estCashSales_KPI.xls"
Set Main = ThisWorkbook
If IsOpen(sFilName) Then
' Book is Open.
Worksheets("Setup Data"). Range("Data").Activate
Attached is my code, pay attention to the bold part. I want the sourceSheet to be copied as a sheet and pasted in the targetSheet (the Sheet2 of "NewBook") but I want it pasted asvalues. Here is the specific part which needs to be looked at...and below is the full code.
VB:
Set sourceBook = Application.Workbooks.Open(sourceFilename)
Set sourceSheet = sourceBook.Sheets("Current")
Set targetSheet = NewBook.Sheets("Sheet2")
[Code].....
I would like to know if there is a way to copy a spreadsheet and paste it into a new tab on another spreadsheet. We currently download 2 lots of spreadsheets from SAP and would like to create another spreadsheet to act as a 'central' preadsheet. What I would like to happen is that the data from the 2 downloaded spreadsheets will be automatically pasted into 2 new tabs on the central spreadsheet, then run some formatting macros (which I can do fine).
View 3 Replies View RelatedI Have been working on a spreadsheet for attendance recording. I will be keeping a new one for each month. I need to be able to take accumulated data from one book and put it in the new one. I have the Following
View 2 Replies View RelatedI have a code in which we can split the data using column values into new and same sheet also. I want to modify this accordingly to my need but i am new in VBA so can't modify this. i want 2 changes in this macro:
1. I want to set a value (Like:"Resolved"," In Process" ) in the macro to extract the data related to the value only. Means, when i run this macro it will extract the data only for "Resolved" status. I have to create two macros for each defined status.
2. After splitting, it will ask me to save the new workbook location.
3. Save it.
The codes i m having are :
First code to make directories:
Code:
Public Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
[code].....
I am trying to copy a range from one workbook to another book sheet with the below code. But its giving error.Can any body fix the below code.
Dim rng1, rng2 As Range
Set rng1 = ThisWorkbook.Sheets(1).Range("A1")
'Create a new work book
Dim xlapp As Object 'Excel.Application
Set xlapp = CreateObject("Excel.Application")
xlapp.Visible = True
' Add a new workbook
Dim xlbook As Object 'Excel.Workbook
Set xlbook = xlapp.Workbooks.Add......................
I need a macro that will copy specific columns from one book into another workbook.
I imagine this isn't too hard, but i can't seem to find the answer.
Also, i have another macro at the moment which allows me, when i run it, to select the workbook which i wish to copy the columns from.
I made the following code to merge 2 workbooks together.
The code is to be executed when the user has Workbook A opened. (All sheets in workbook KPISWD are supposed to be moved after all worksheets in workbook KPICustomers).
I keep getting a debug error on the code that is supposed to do the actual move and loop until it is finished with all of the sheets in Workbook B.
Code:
Dim KPICustomers, KPISWD As String
KPICustomers = ActiveWorkbook.Name
Workbooks.Open Filename:= _
"W:FacturatieKPI per periode SWD.xls"
KPISWD = ActiveWorkbook.Name
[Code] ..........
I am trying to take an address book on the internet and copy/paste it into excel- not hard right? Ok, so that is done. The problem I have is that there are over 2000 addresses- and they all copy straight down into one column. I know how to transpose the information- but come on- there are over 2000. that seems like a LOT of work. So my first question would be is there a way to do this all at once? For each company/name/address/phone I need one row per company.
View 9 Replies View RelatedIs the some way I can find out how many VLOOKUP formulas I have in a sheet and a workbook?
I can find them with ctrl F, but is there a what to return a number of how many instead of counting frome the Find box.
I'm trying to copy rows from one workbook that is exported from access to multiple worksheets in multiple workbooks. I used a macro I found here from JBeaucaire that will copy the data for me but it over writes the information I have in rows 1-3 and also the rows below, 28-35.
Is there a way to copy the data without loosing the information in the first three rows and the rows below where the data goes?
I have a workbook that I would like the same operation on all sheets except the first sheet in the book every time I update it (which is once a week).
I get data (currency format) for all of my employees on a weekly basis. Unfortunately this data is shown as a negative number and I need to invert these values. (So I use the formula below)... but I have to do this manually for about 35 sheets and I figure I could automate this somehow with a macro. Here's what I do usually and what I would like the macro to do automatically:
I want to insert a column after column G.
I would like to enter the following formula into H2:
=IF(G4="u", "u", IF(G4
I have a workbook with a single worksheet that has about 2000 rows, columns A, B, C, D, E & F
Cells in colums A, B, C, E & F all have very different information in them, nothing is similar in any of those columns that I can base a criteria on. Cells in column D however will have 1 of about 18 possibilities in them
What I want to do is have another workbook with 18 worksheets, each worksheet named 1 of the 18 possibilities, and somehow magically pull the data from the first workbook and insert it into the correct worksheet in the second workbook, leaving the data in the first workbook intact.
I update the first workbook several times a day, adding and deleting from it so would need to update as it goes, or be able to run the update as and when needed.
My skills are limited to simple formulas inserted into cells and dragging them down!
is there a way to cause my custom menu items to grey out like the built-in ones, depending on the current selection or when the status of the active worksheet/workbook changes (e.g. when the book is protected to disallow certain actions)?
Obviously I can trap any problems once the associated VBA is invoked, but it'd be more professional if I could just prevent that from happening in the first place.
I'm trying to write a macro that selects cell A1 in each sheet within the book. I've written the following code:
Code:
Sub a1()
For i = 1 To Sheets.Count
Sheets(i).Cells(1, 1).Select
Next i
Sheets(1).Select
End Sub
However, it produce this message: "Run-time error '1004': Select method of Range class failed."
If I slightly modify the code to
Code:
Sub a1()
For i = 1 To Sheets.Count
Sheets(i).Select
Cells(1, 1).Select
Next i
Sheets(1).Select
End Sub
It works fine. I'm curious why this happens.
i have done a search on this topic and found many similar answers to many similar questions. All specify using Application.Run "workbookname.xls!macroname".
In my code, the filename has an assigned value as the master code goes out to many secondary files - the user selects the particular one they want at the start of the macro. The variable assigned is called "UpdateKAMsFile".
how do i get the macro called KAMsRandomColour to run in the workbook designated by UpdateKAMsFile?
so to start off im not able to attach things due to security reasons, what i need is either 1 of 2 macros. if its possible, both are related. one possible is as follows: i need it to go through a certain column (say I for example) and look at the information in it, this information can vary from peoples names along with dates and other stuff, i want it to look through these and if anything has a date today and prior i need the cell to be highlighted. problem is sometimes it might have 2 dates, or no dates. it should have todays date, their name, other information, and future date of something. but not everyone does, this is the macro i dont think that can exists.
2nd macro possibility the other macro uses the first sheet, AFTER been highlighted, normally by hand, and takes it to another workbook and puts in in certain spots. so the first sheet has names of everyone in column K. what i need is it to look at column I and if its highlighted take entire row to other book, and put into sheet under the person name in their tab. the second book has a tab for each person (at this time 18 tabs) which can fluxuate, and each tab is the persons first and last name, without spaces. since when i put sums on main page it didnt want to work with the spaces i had to omit them. again im not sure this is possible.
I am trying to do away with our traditional "hard copy" book of warranty codes here and I would like to create a sheet with 6 changing combo boxes so I don't have to flip through this dad-burn book all day for codes.
the first combo box would be like "brands" then "type" "model" "section" "item" "repair type" and then like a cell to display a value from a list depending on all 6 previous combo box selections. Similar to those car part websites where you enter your vehichles info to arrive at a part number or price.
Got an issue that I can only seem to do with things like the program Hotkey and making physical macros. I'm sure it is easy but I'm lost.
I have data in one book that looks like this (Call it Book1)
Date: Service
ProviderReason for callCRM
SR #DM
SR #WO #Device #Serial #Issue15/02/2010DATE: 03/02/10
TIME: 11:29
BUS: 1234AO
ISSUE DETAILS: Issue with Console
and data in another book that looks like this (Call it Book2)
DeviceSerial NumberNameTerminal PositionTerminal Group Location DescriptionTerminal IDDescription Service Provider 104012343030123456ConsoleDriver Console1234AO90401234Service Provider Name found Here
I need to find the BUS: 1234AO from the cell in Book1 then look that Bus up in Book2 and then from Book2 copy the Device, Serial Number, and Service Provider into the cells into Book1 and repeat this infintely often. The problem I have is the the bus can have 1 Console and 1-5 Readers and I need to find that data also. So for example I need to find BUS 1234AO and get the Console details and past that into one issue and the next issue will have the same bus number 1234AO but I need the details for Reader2 (that detail is in the Name field as shown above.) The issue details will always have the Console text and Reader# text in it.
The problem I have is that the Reason for call cell has ALT-ENTER characters in it and the details are not always in the same spot but the details are almost always in the format ####AO (four numbers and the letters AO)
I want to do a loop where you can copy say A3 worksheet 1 then add another sheet naming the work sheet "A3" then copying A3 worksheet 1 to A1 "A3". After that looping to A4 to a new work sheet naming the work sheet "A4"copying the value to A1 "A4", etc...
Is there a simply way of doing this loop? I can probably fit my other coding into the structure.
I want to be able to copy a name from one sheet (Available Players), paste it to a cell in another sheet (Round 1 through Round 20). The cell that will be copied is fixed but the place where it will be pasted will be different and may be on a different sheet.
also i would like to change the color of the copied cell to "greyed" out or cut if it can not be greyed out. I have created a button and put in a macro that i created but have been having problems with it, generic 1004 errors that i can not figure out. i am attaching the document.
I want to create a macro button that can create copy, insert, paste and rename the new sheet in next month's name, like if the active sheet's name is January, I want to copy the whole sheet of January, insert new sheet, paste the new sheet and rename the new sheet to next month like February?
Also rename the new sheet (February) cell B3 the same as new sheet's name (February)
So if month of February is near end, the macro button in February will create the same way as Jan did which means the next sheet will be named March and so on.
Example, I have a sheet named DATA1, I want to add a new worksheet, copy a certain range from the DATA1 sheet to the new sheet and rename it the same name DATA1 and delete the old DATA1 sheet.
Also, the sheetname will be unknown, the macro must get the sheet name first.
I have I workbook that contains 2 sheets. The first sheet "Business Objects" is the master list. The second sheet "Gene" contains similar data but is incomplete. There is a unique identifer for both sheets and that is item ID. what I would like to do is look at the Item ID column on sheet1 compare it to SS# on sheet two and copy any rows from sheet1 to the end of the sheet in sheet 2. I have found in my searches on this forum a bit of code that identifies the ones in sheet1 that are not in Sheet2 and highlights them red(which is not neccessary for me, but I am struggling to figure out how to take that and paste it to sheet2.
View 3 Replies View RelatedEXAMPLE: Complete Sheet called "Day1". When day1 is complete you click on button and it then copies itself and creates and renames new sheet to "Day2", then when "Day2" is complete you click on button and it then copies itself and creates and renames new sheet to "Day3", and so on and so forth to "Day30".
View 9 Replies View Related