The following code (blue font) finds specific worksheets in an active workbook, and copies them into a new workbook then names the new workbook the same name of the worksheet. It all works perfectly.
The second code below (red font) imports some vb into the new workbook. It works fine if I manually open the new workbook and run it in the immediate window. However, I would like to incorporate the red code into the blue code so it all runs as one. I've tried inserting the red line after the "ws.copy after" line and after the ".saveas" line, but no dice.
For Each ws In wb.Worksheets
If UCase(Left(ws.Name, 2)) = "CC" Then
Set NewBook = Workbooks.Add
I have the following sendmail macro. However I need the "To" section to be the result of a lookup in a worksheet. The specific function that i need it to be is: =VLOOKUP(A837,'Tenacity Jobs'!1:65536,5,FALSE)
How can I incorporate this function so it will insert the result of the lookup into the "To" box ?
I'm trying to write a macro that will analyze data from one spreadsheet and do a regression. The information I want to be output on the same sheet. I tried to use the record function, but I got an error. It said "Run-time error '1004': ATPVBAEN.XLA could not be found. The code read:
I have a spreadsheet containing all products in sheet 1. I have a list of promotional codes in sheet 2. In sheet 1 I want to say 'If cell A2 is contained in column B of sheet 2, then say YES, if not then leave blank'.
I've tried the following but it doesn't work:
=IF(VLOOKUP(A6,Sheet2!B:B,1,FALSE),"Yes"," ")
I get #N/A when the item is not in sheet 2 and #VALUE! when it is.
I'm trying to use loop to activate 3 workbooks "OSB1", "OSB2", and "OSB3" and their respective sheets. "OSB1" has sheet "OSB1" in it, workbook "OSB2" has sheet "OSB2" in it etc.
The digit on the end of each OSB is the variable I am using in the loop.
What I am trying to come out with is the following 3 workbooks and sheets being activated:
I have the below formula to make a simple calculation: =SUM(AC1880+AJ1880)
I would like to adapt the formula to ask IF AG1880 is empty or not. If AG1880 is empty then I would like the formula to NOT make the calculation, but instead return "--" (nothing).
If it contains information, then I want the calculation (AC1880+AJ1880) to be made. If AG1880 contains information it will be text in a text format.
I have a number of files organised in a number of folders on Dropbox. I create a macro to enable me to be able to navigate to a particular folder and then get a list of all the files in that folder to be placed within certain cells on the spreadsheet I'm working with in Excel.
I'm trying to create a dynamic Named Range using VLOOKUP in place of a sheet name. (Using Excel 2003 & Win XP Pro SP2).
To illustrate:-
Employees.xls contains employee's details on separate sheets for each department, e.g. Production, Admin, Sales, Personnel, etc. Each sheet is 12 cols. and 1 header row. Sheet 1 of this book contains a 2-column Master List of all employees and their departments. MyBook.xls has an employee's name in A1.
I can get his department by using =VLOOKUP(A1,MasterList,2,FALSE) and what I want to do is incorporate that into the following in place of 'Admin' so that the range will refer to the correct department for whoever's name is in A1.
I have only ever created macros in existing workbooks. Is there an easy way to copy a macro from one workbook to another, without having to run a new macro in the new workbook and then paste the code into it from the old workbook?
I have managed to come up with the following macro. The problem I have is that some of the data are imported in text format, while other data are imported in general format.
I have a large data set, with more then 5,000 rows, and 2,000 columns, and I would like ALL the data imported at text. How can I modify this macro to make that happen?
Code:
Sub ImportData() With ActiveSheet.QueryTables.Add(Connection:= _"TEXT;C:Users*****ATTRIBUTES-ModifiedExport.csv", Destination:=Range("$A$1")) .Name = "ATTRIBUTES-ModifiedExport.csv" .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = False
I wish to click on a button in a worksheet which then looks in a particular folder on the local drive. Any file with a .xml extension import into the worksheet.
What if 2 or more xml files are present? The xml file filename ends with the date & time the file was generated, eg BPSEvent_10033_Tam_20140116124216
Can a code look for the oldest file to import based on the end of the filenaming format - yyyymmddhhmmss
Code to then delete the last xml file imported would be good too.
I would like to have a macro that will allow me to browse to the desired file and import the data into columns. I have the macro working to the point of allowing me to browse, select the file, and pasting the contents into the active sheet.
The things that I need help with are:
1. Getting the data to paste into a specific cell instead of A1. Currently the code is
CELLS.SELECT ACTIVESHEET.PASTE
I would like the data to start in A7.
2. Getting the file parsed into columns. The format of the .DAT file is static and only has 4 columns. Currently when it imports, the entire record (row) all of the data is in the same column. I would like it separated into individual columns if possible.
1. I've recorded a macro that imports values from 6 different .csv files at once. The problem is that not all of these files are going exist upon import. Is there a way to tell the macro to only import if the file exists? And to skip if the import of that file if it doesn't exist? Right now the debugger stops the macro because of file not found.
2. I want to press a button on Sheet 1 that copies Sheet 1 into a new Sheet that's named with today's date. E.g. I press the "Archive"-button, the sheet is copied to a new sheet with the name "051308".
I have a TXT file that is properly format for a nice print out but not in any fashion to work in an excel or access file.
My txt file looks something like this Header info Row 1 Header info Row 2 Header info Row 3 ACCT# Value Descripton ~ Part number Customer Name ~ Vendor Name Customer add1 ~ Vend add1 Customer add2 ~ Vend add2 Custom city, st, zip ~ vend city, st, zip
Repeat about 5 or 6 more entries do a page bread and repeat the headers and then 5 or 6 more entries
I want to automatically copy the entries and place the data into Row 1 of Excel starting in Column 1 through Column 12
I'm trying to create a macro that will import data from another excel file, into my current excel file.
Basically, I see the process going as follows: 1. Run Macro via Button 2. Pop-Up Window asking for File Location (importBook) 3. Import Data from importBook into sheet entitled Data 4. Click OK once import is complete
I've got some knowledge of coding but macro coding knowledge is limited. If anyone has an existing code or can whip one up, I would be very appreciative.
I started out with this
Dim filename As Variant filename = Application. GetOpenFilename() Workbooks.Open filename
I did a macro the import a log file from a specific directory and now i want that the user could enter the path of the directory that contain the file Is there a way to do it?
ub ALL_Logs_files()
' ' Logssss Macro ' Macro recorded 3/4/2007 by dzaitoun '
Sheets.Add ActiveSheet. Name = "Foresight FP Data"
With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:Documents and SettingsdzaitounDesktopLog out filesforesight.fp.out" _ , Destination:=Range("A1")) .Name = "foresight.fp" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True ..............
I've got a basic handle on the application/use of macros, & personally use them for cleaning up data exported from a very very old as400esq application.
My problem is that i have managed to clean up all the blank data & piffle, but Im stupped by the header part of the text dump.
Heres an example; A B C D E F -------------------Header A------------- 1 3 john 50 piano 4 1 Bill 25 horn 6 5 Ted 60 trumpet -------------------Header B------------- 3 3 Phil 71 Cello 1 1 James 83 Viola 3 2 Earnie 62 Violin
I basically want to rid the header rows from the spreadsheet & drop the header description (Header A/ Header B) into column F for all those items below until you get to the next header.
I need to import a number of log files into a spreadsheet, each log will go into a separate tab. The logs are text files (.log) that have the same structure but slightly differ from each other.
For example all the logs will have many lines of data all starting with a date and then a number of delimited fields. In some logs the delimiter is a coma, in others is a unique charachter such as "{" or "@". Also the delimited fields are not a given quantity, they can vary too from log to log. How can I write a macro that understands what delimiter needs to be used and then imports all the file accordingly into one spreadsheet (in separate tabs)?
I tried something with the macro recorder and browsed for directions on the internet but really am clueless on this one. I am using windows 2000, and referenced Windows Scripting Runtime.
I want to automate the download process of these scan files we use. I can figure out how to do that if not for the two wildcards I'm about to throw out.
What I can't seem to figure out is how to use VBA to determine which files to import. I was thinking about possibly having VBA look for the time stamps on these files in the scan file directory, and then only import those ones which created in less than an hour from current time.
I have only a few days of VBA experience, so I don't have a clue where to start. If anyone could provide assistance/point me in the right direction.
By the way, the files that I am importing will be a comma delimited file. However, for some reason, the files are all extensionless (without .xxx)
I tried to adapt this code into my own from another thread. [url]
BUT, I am getting an error when running it. On the "For i" line. And I know just enough VBA to be dangerous. Can someone help?
I have another code setup to run if a certain cell equals "October" then it calls ImportData. Then this code is supposed to pull all files out of My documents/TechConnect/October08 and place the data into sheet 1....
I am trying to use a dynamic VBA code to import data into a xcel sheet from another sheet. I currently have it working but in the code I have the directory and the file name listed for the file to be imported. I would like to change this to a cell referance inside my sheet so that I can make changes to the name of the file or the directory location and the VBA code would use the cell reference as the source data.
how to make a button(s) which will import data. I have to make a report and import some cells from 350 other excel files. Report is attached to this post. In the yellow cells should be buttons. The code for first one is :
after importing data (data >> get external data >> import text file) and running a macro that contains
Cells(i, 5).EntireRow.Delete Shift:=xlShiftUp
i cannot insert any data into the next column below. why is this so? is it because of the excel memory that remembers that a macro has been previously ran in that cell before? example i import text file into B1 then the data will be imported into different cells using delimiters. the data will occupy from row 1 to 22. After i run the macro which combines fields with similar data the data will occupy row 1 to 15. then when i try to import data into B16. the "import text file" option will be grayed out. why is this so? how do i solve the problem?
