I have a macro which opens two different hyperlinks in excel. Opened workbooks have are named like 1.asp and 2.asp. Both contains only one spreedsheet. I need to copy these spreedsheets to the Workbook, from which they are opened.
View 4 RepliesI already have code that I believe is supposed to do what I'm trying to accomplish. However, it doesn't fully work. The Macro is below:
VB:
Workbooks.Open Filename:="C:Documents and Settingsplp138DesktopExample 1.xls"
On Error Resume Next
With Workbooks("Example 2.xls")[code]....
Basically, what I'm trying to accomplish is to have a certain range from one file copied, and pasted in the next available (empty) row of another file. While debugging I see that everything is working except for the actual pasting.
I am looking for a macro that i can store in my personal.xlsb. what i need is pretty much is something like this
private sub workbook_open
if workbook.name "inventorysummary.csv"
then application.run "personal.xlsb!capacity"
end sub
I only need it to run just for this file and i cannot place it in the file due to it gets replaced every day. Which if it didn't get replaced. I know how to do auto opens when the file stays the same I am just unsure for this.
I am using the following code and putting it into a module.
Private Sub Workbook_Open()
MsgBox "Hi, thanks for opening me", vbInformation, "Ozgrid.com"
End Sub
when i open the file and click on allow macros it does not run.
I have a current macro that saves each sheet as their own file. The first 10-15 sheets are not really necessary, so I typically delete them once the macro has run. I am looking to add two things to my current code:
1. Add a tab called Start. Make the macro look for 'Start' tab and then just save each sheet to the right of that as its own file.
2. Save each sheet in the macro as just a copy/paste value. They current have a lot of links and it makes each file close to 3MB. That makes it incredibily difficult to paste all in one email.
Here is the current code:
Sub CreateWorkbooks()
'Creates an individual workbook for each worksheet in the active workbook.
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sht As Object
Dim strSavePath As String
On Error GoTo ErrorHandler
[Code] ..........
I am trying write code withVBA to save separate file in a different folder with Loop.
Write a code with "loop" till it finds empty cell in the column and for every change in number a separate file needs to be saved in specified folder with file name as "10010, 10011,10012... and so on with data copied in the file saved.
Below is the data.
10010
10011
10011
[Code] ....
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)?
I have a folder containing 40 single sheet excel workbooks and I would like to automate following tasks:
- Open each excel file (need to open the file so as to update it since it gets the data from another workbook through =formulas)
- Copy paste as values
- Save this as excel html in the same folder as original excel files (keeping the original file name)
- Close (original excel file should not be changed ie formulas should remain in place, only the html file will contain values)
- Since there will always be xHtml files with same name need the macro to replace the excisting file
My abilities with excel are limited to functions, no VBA knowledge other than finding ready codes and pasting them in the module.
Since this routine is to be run almost daily the macro should run all files, instead of one by one.
I just hope that I am not asking too much for excel to handle and I hope that explanation is clear.
I want to open a specific sheet and refresh only said sheet when i open another sheet for example x.xls
So opening x.xls will automatically open y.xls
I've tried this in the workbook code area but it doesn't do anything.
[Code] .....
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?
I have one workbook that needs two macros.
On the "Complete Backlog" tab of my workbook, I want users to enter in the requested information based on the column header. Then I would like a Macro attached to a button that says "Refresh" that the user would click after they have entered in all of the information. This macro should look in Column M (WIP Status) and if any of the cells say "Close", it should Cut the entire row from the spreadsheet(Ex. A2:M2) and Paste it into the speadsheet titled "Closed Jobs".
This is so that as jobs are closed/finished, they are removed and stored on a separate sheet. The items would have to be pasted so that it pastes into the next available row - not just on top of each other.
I also need another macro that i can put into a button that doesn't "delete" a row from the sheet, but just copies over to another sheet - so that there are two instances in the workbook.
If would look something like: If a cell in "Column G / Director" of the "Complete Backlog" speadsheet is equal to "Snodgress" then copy columns A-L of the same row to the spreadsheet titled "Snodgress" - of course skipping down the rows to the next blank row.
.....is equal to "Herr" copy row to "Herr" spreadsheet.
....is equal to "McCormick" copy row to "McCormick" spreadsheet.
and so on.
I am trying copy and paste data from an external file. The steps I am using to get data are as follows:
1. open external file
2. copy desired data from external file (any number of rows & columns)
3. close the external file
4. paste data starting with designated cell.
The code works fine up until the part of the code where I want to paste special (I need to preserve cell formatting). Error is "PasteSpecial method of Range class failed". No sure why code doesn't work at this point.
Is there a way to create an outside log of who has opened an excel file and when?
I am looking to add some accountability to a project.
I am wondering if I copy a list from a pdf file and paste it all into column A1...is there a way to paste the list going down in rows like A2 A3 A4?
View 1 Replies View RelatedFeedback.xls is the file I want to have my macro run in. By pushing a button to run the macro, it will prompt the user to select where their file is on their computer. This file will have different file names based on the end user. For example sake, I've included CAP.xls.
After the user selects their file, it will prompt them to choose which tab to copy and paste data from. Typically, there will be 30+ tabs on their worksheet. For example sake, I've created 5 tabs on CAP.xls. The tabs in their workbook will be labeled as I have labeled mine, M1 CAP, M2 CAP & M3 CAP. Therefore, it should prompt the user to select which tab to choose from.
After the user selects their tab (M1 CAP, M2 CAP, M3 CAP), the macro should prompt the user to choose which row of information to copy. In CAP.xls, I have a few rows of information to choose from. They all start with FY10A1, FY10A2, FY10A3, but this information could change. Once they select the row, it will copy each of those fields onto the respectively labeled fields on Feedback.xls.
I work for a chicken hatchery. So, a couple preliminary things by way of explanation:
1. The day we place our eggs in the incubators, we fill in an excel file containing a list of all the farms from which those eggs came. This is called the "Egg List" workbook.
2. After 21 days have passed, and the eggs have turned into chicks, we open that original file. We copy cells from one column of that file (the workbook is called the “Egg List”, we copy “Sheet1” cells B7:B50), and paste them into another Workbook called “Chick List.”
My question is this:
Is there a macro I can put into the “Chick List” workbook that will automatically locate that particular “Egg list” workbook file that is 21 days old, and paste the contents of its cells B7:B50 into cells A7:A50 in my “Chick List” Workbook. It is preferable if this can be done without opening the old “Egg list” file. But if this is not possible, I’ll take whatever I can get.
Here is what I have, which works okay as far as finding the file. I bootlegged this from another part of this helpful website. But I cannot figure out the syntax for getting cells B7:B50 from the Egg list to copy into cells A7:A50 of the Chick List.
Dim FSO As Object
Sub ProcessFiles()
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim this As Workbook
Dim cnt As Long
Dim filenew As Object
Is it possible to insert the filename automatically in formula after the input from the user?
I want to ask the user to input a filename and afterwards, call that filename in an Index-Match
I have the below formula with the sample filename:
ActiveCell.FormulaR1C1 = _
"=INDEX('[filename1.xlsx]DATA'!C9,MATCH(RC[1],'[filename1.xlsx]DATA'!C32,0))"
Range("A2").Select
Opening it manually the dates in column C are all consistently date numbers. i.e. using the =ISNUMBER() function to test them, they all return True
However when I include the VBA instruction:
I would like to have a message box pop up when a file is opened requiring a user to enter information (Name), that would then put this name into a cell for later use when they are printing.
View 6 Replies View RelatedI have excel vba code to open .xls files on my server. Files to open are chosen by the user via a UserForm within my app, it shows them all their files in their directory on my server. How can I open their files (.xls) that they select but DISABLE the VBA from being able to run/execute, if they have any attched to the file?
I now have all code in place to open the file no problem, I just need to know how to disable the VBA part from being "turned on" when the file opens.
I run a macro which runs saves the active file to a different location to ensure that I retain the original like so -
If ActiveWorkbook. Name = "KEY_DATA_CFT77" + ".xls" Then
ActiveWorkbook.SaveAs Filename:= _
"H:HOME imcEXCELKEY_DATA_2006KEY_DATA_2006_01KEY_DATA_CFT77_01.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End If .................................
Is it possible, in vba, to copy two sets of cell values to two clipboards (clipboard1 and clipboard2?), open another workbook and paste those two seperate clipboard values into another two cells?
View 3 Replies View RelatedI want to copy and paste from one sheet to another based on column a using a macro copy button.
E.g. if column a value = apple then copy that row into the apple sheet.
the small code i'm writing just needs to do the following:
1. open an .xslx file selected by the user
2. copy 2 worksheets from that file to the current one
3. close the selected file without saving it.
This is my code:
VB:
Sub GetData()
MsgBox ("Please select a file"), vbOKOnly
Master = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Please select a file")
[Code]....
Opening the file works, but the last 3 lines don't because i don't understand how to declare "Master": however i dim it (workbook, object, variant...) i get an error on the GetOpenFilename line. If i don't declare it, i get an error while trying to copy the worksheets.
The Macro asks the user to point at the location of a report, it then copies information out of that report and pastes it into a master sheet. The part I am having trouble with is closing the file that data has been copied from.
I have tried different things to close it such as
can group all opened file in 1 excel tab instead if many tabs?
open thru window the file u want...
http://i12.photobucket.com/albums/a209/tancfc/11444.jpg
http://i12.photobucket.com/albums/a209/tancfc/fhfh.jpg
right click on task bar > properties > under Taskbar Appearance, select Group Similar taskbar buttons > is not my answer
While opening one Excel file I get an error message saying that a 2nd file could not be opened because it cannot be found. I do not want this 2nd file to open. WHere do I find the "command" that is telling this 2nd file to open.
View 3 Replies View RelatedI have a Userform in Excel.
My requirement, is whenever I open the Excel file, it should open the userform and should not show the excel file at all. And the form is closed, the excel file should be saved and closed.
Also, I need the minimize button on the form and in the taskbar, it should not display the Excel file, it should display only the Userform.
I was working on a tax file yesturday, just a long long list of receipts. and the file is password protected. However, I went to open it today, and I get a message that says: "Filename.xls cannot be opened. the file may be read only, or you may be trying to access a read only location. Or, the server the file is located on may not be responding."
It's on my USB Drive. I've tried copying it to my HD so I can play with a copy, but no dice. When I try that, I get another error message, : Cannot copy Filename.xls. The file or directory is corrupted and unreadable. I've tried the various fixed suggested in the excel help files to no avail. I did a search on the forum, but didn't find anything that seemed to apply or help me. The computer I'm trying to open it on has Office 2003, the one I most recently worked on the file on has office XP. Not sure if this was a source of the problem. Doesn't seem like it should be. Hasn't been in the past. I'm not even getting to the dialogue box asking for my password yet.
how to alert and force second user to quit (i.e. even cannot open the file as "read-only) if the excel file is being opened by other?
View 3 Replies View Related