Incorporating Macro Import Within Another Sub?

Jan 13, 2012

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

[Code]...

View 9 Replies


ADVERTISEMENT

Incorporating Lookup Value In Macro

Oct 2, 2007

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 ?

View 11 Replies View Related

Incorporating Data Analysis: Regression Into A Macro

Apr 10, 2009

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:

View 2 Replies View Related

IF Incorporating VLOOKUP

Aug 10, 2009

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.

View 9 Replies View Related

Incorporating Variable Into Loop

Jun 29, 2013

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:

OSB1.Sheets("OSB1").Activate
OSB2.Sheets("OSB2").Activate
OSB3.Sheets("OSB3").Activate

I've given up the code as I can't work out the inverted commas:

VB:
Dim x As Integer
For x = 1 To 3
OSB" & x & ".Sheets("OSB" & x & "").Activate
Next x

Is it possible to do this at all? The bit after "Sheets" is correct. It's part with the first "x" after the first "OSB" which is incorrect now...

( In my actual code I'm trying to do more than just activate the 3 sheets but need to work out the syntax for this to be able to edit the remainder).

View 9 Replies View Related

Incorporating An IF Statement To Basic SUM

Dec 3, 2009

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.

View 9 Replies View Related

Incorporating DropBox Files Within Excel

Oct 31, 2013

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.

View 2 Replies View Related

Dynamic Name Range Incorporating VLOOKUP

Aug 27, 2009

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.

=OFFSET('[Employees.xls]Admin'!$A$2,0,0,COUNTA('[Employees.xls]Admin'!$A:$A)-1,12).

View 9 Replies View Related

Incorporating Variable Into Average Function To Locate Cells

Nov 12, 2012

I am trying out VBA to write a macro in order to average every 25 cells in a column. My attempt so far is

Dim myrow1 As Integer
Dim myrow2 As Integer
myrow1 = 2
myrow2 = 25
Range("G2").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R " & myrow1 & " C3:R " & myrow2 & " C3)"
myrow1 = myrow1 + 24
myrow2 = myrow2 + 24

So I am hoping the first ActiveCell.FormulaR1C1 gets read as =Average(C2:C25)

However I just get Run-time error '1004' Method 'FormulaR1C1' of object 'Range' failed.

View 3 Replies View Related

Macro To Export And Import A Module And Assigning Different Macro Automatically

Jan 27, 2010

Macro to export and import a module and assigning different macro automatically ...

View 9 Replies View Related

Import Macro ..

Jul 1, 2009

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?

View 7 Replies View Related

Import CSV Macro?

Oct 31, 2011

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

[Code]...

View 1 Replies View Related

Import XML File Macro

Jan 29, 2014

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.

View 14 Replies View Related

Macro To Import .DAT File

Dec 20, 2006

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.

View 9 Replies View Related

Macro To Import From .csv Files

May 13, 2008

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".

View 9 Replies View Related

Macro To Import TXT Data

Aug 24, 2008

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

Then the 2nd entry would be in row 2 etc

View 9 Replies View Related

Import/Copy Macro

Jun 16, 2006

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

View 8 Replies View Related

Import Log File With Macro

Mar 13, 2007

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 ..............

View 4 Replies View Related

Import Macro To Personal.xls

May 24, 2007

I have found several examples of excellent macro code on this site.

Is there a procedure to import a macro code ( saved as a TXT file) into the Personal.xls file?

For example, the following code removes blank spaces and converts text to proper case.

Is there an easy way to save this as a macro in my Personal.xls file?

'Macro Code to apply TRIM and PROPER functions on the selected range ..

View 8 Replies View Related

Macro - Report Header Import

Jul 11, 2008

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.

View 14 Replies View Related

Macro To Import Log Files With Different Delimiters

Sep 29, 2009

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.

View 3 Replies View Related

Macro To Import CSV Files Into Workbook

May 17, 2011

I'm looking for a macro that will import multiple .csv files from c: emp each into their own separate worksheet.

View 5 Replies View Related

Macro To Import Tabs From A File?

Dec 13, 2013

I need a macro that import tabs from a file into another file.

View 1 Replies View Related

Import Macro With No Extensions By Timestamp

Mar 25, 2007

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)

View 9 Replies View Related

Import Macro - Getting An Error When Running It

Apr 24, 2009

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....

View 9 Replies View Related

Macro Code To Import Data

Oct 19, 2006

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.

View 9 Replies View Related

Macro Button: Import Data

Oct 30, 2006

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 :

Sub CopyRanges() ....

View 4 Replies View Related

Import Text File Using A Macro

Dec 11, 2006

Can we create a macre that can convert txt file into excel.

View 5 Replies View Related

Import Data And Delete Macro

Jan 15, 2007

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?

View 4 Replies View Related

Import Text File 4 Macro

Mar 27, 2008

I'm trying to import a text file with an Excel 4 macro. I tried the Open.Text function but I cant assign a date (YYYYMMDD) format to the first field.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved