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 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
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 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 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 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 RelatedThis 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 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 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'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 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 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!
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 a macro that I would like to run on every worksheet in my book (over 100 sheets wide). As it takes about 20 secs to run on each sheet, and I don't want to click from sheet to sheet, how can I make it run on every sheet in the book? And no, they aren't named sequentially, either alphabetically or numerically.
is there a function in Excel to return the number of sheets in a book?
I'm having an issue with a macro that copies sheets from one workbook into a new workbook. The issue is it doesn't copy all the sheets. It only copies the first 10 and doesn't capture the the remaining 15. I've checked the range of the translation table and the issue doesn't appear to be there.
Sub SBGFiles()
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim a As Worksheet
Set a = Sheets("Input")
Dim Fpath As String
Fpath = ThisWorkbook.Path & ""
Dim SBG As Range
Dim RU As Range
Run ("UnPro")
For Each SBG In a.Range("B44:D44")
Workbooks.Add
newbook = Workbooks.Count
Workbooks.Item(newbook - 1).Activate
On Error Resume Next
I have a macro that used to create a new book for each of the worksheets in a workbook that would name them the same as the worksheet.
Since our company upgraded to Windows 7 the macro, more often than not, fails to work.
The code for the macro is
Code:
Sub NewBook1()
'
Set wbSource = ActiveWorkbook
For Each ws In wbSource.Sheets
ws.Copy
Set wbDest = ActiveWorkbook
wbDest.SaveAs strSavePath & filePrefix & " " & ws.Name & " " & fileSuffix
wbDest.Close
Next
Application.ScreenUpdating = True
'
End Sub
I have several similar documents open. They each have a macro with the shortcut CTRL + SHFT + T. The problem is each macro is different for each book. When I am working on book X, I don't want macro from book Z to run, but it does.
I need all of them to be open.
I need to get this macro to process the cells for every worksheet in a book rather than just the active one
Public Sub test()
Dim Lr As Long, i As Long, x As Range, _
v1 As String, v2 As String, v3 As String
Set x = ActiveSheet.Cells.Find("*", searchdirection:=xlPrevious)
If x Is Nothing Then Exit Sub
Lr = x.Row
Application.ScreenUpdating = False
For i = Lr To 1 Step -1
v1 = Cells(i, 2)
v2 = Mid(Cells(i, 3), 1, 1)
v3 = Cells(i, 4)
If v1 "OP00" Or v2 "L" Or v3 "CC" Then Cells(i, 1).EntireRow.Delete
Next
Application.ScreenUpdating = True
End Sub
At job, Mac OSX and Address Book 4.0.6 with contact info including in many cases emails. I'd like to get it of there, into Excel to manipulate, and ultimately into a FileWrecker Pro database. How to export 1087 entries from Address Book including only selective data fields is the question, and I know that's "slightly" OT, but I do want to import into Excel, so I hope that vindicates me! If not, and you can point me to a good MacForum, I'll settle.
View 9 Replies View RelatedIs there a way to run a macro automaticly on a closed work book every Friday ? I've found ways of doing this using on-open event but It made me wonder if this was possible.
Macro to highlight a cell A1 every monday ( or any day) when we open the book for the first time.
Macro should not run when we open the book from the next time till that week gets over and again it should run once the next week started.
I have the code below which copies columns A:C from Book1 into Book2. However, it does not work when the Book1 is not open. I would like the macro to still work when the Book1 is closed.
Sub CopyColumnToWorkbook()
Dim sourceColumn As Range, targetColumn As Range
Set sourceColumn = Workbooks("Book1").Worksheets("Sheet1").Columns("A:C")
Set targetColumn = Workbooks("Book2").Worksheets("Sheet1").Columns("A:C")
sourceColumn.Copy Destination:=targetColumn
End Sub
OK, I want to run a macro that will:
(1) go in to sheets 'A', 'B' and 'C'
(2) find all cells in row 1 that equal "1"
(3) copy the entire column and paste as values
I am trying create a macro that will create a new work sheet on an existing workbook. I have minimum experience writing a macro, but I do know how to effectively record one. The issue is, I need the macro to copy multiple columns down to the end of the data and pasting it into the new workbook at the end of the data already pasted-- rows of data are frequently added to the workbook.
View 4 Replies View RelatedI have attached a file with an example of what I need. The data tab show the data has been given to me. I need it in a different format to be able to load it into our system. The Needs tab shows what format I need. Basically, I need to convert this table into a flat file, where there is a record for each "X" value only.
View 2 Replies View RelatedI'm trying to create a macro that will look at the values in one column (column G) and whenever there is a new value, it will copy the data from 2 other columns (I and J) into a new sheet with the original column as the title. I can't think of a better way of explaining that so I've attached a sheet with 'Raw data' being the source sheet and 'Outcome' being the hoped for final product.
I have color coded the sheet to show where the data is coming from, but do not want the colors in the actual sheet.
Was looking into this, but basically i have a repetitive task of copying contents from various columns, and pasting them into 1 column.
1 right after the other. I figured there could be a way for a macro to copy contents from multiple columns and paste them into 1 column.
I have including 2 files, 1 from the start of the file, and 1 how it should be so it will have clarify what i am trying to do.
So basically if you open book1 , i have factory install tabs,
I need the columns contents from the first tab the (5k-20k) starting with Column B, then E, then H, and so on( Every 3rd column starting with B ) to be copied and pasted in Column A of the (Factory Install Tab) ( last tab ).
If you open the 2nd file this will be clearly understood. In the second file in the factory install tab you will notice i did the first 2. I highlighted each starting part in yellow just so its easier to see. SO in column A you should have all the contents in the 5k-20k tab ( starting with b every 3rd ), pasted in column A 1 after the other.
The next tab is the 30k tab, i need all of the columns contents copied starting with B ( every 3rd again ), copied in Column C of the Factory Install tab. Basically every tab's contents needs to have its own column in the Factory Install tab.
If anyone could help that would be great, this is a very time consuming process manually, and it would seem like a macro could be made to easily copy contents.