Computer Support Forum

Solved: ACCESS: Show second latest data and export data to excel from macro

Question: Solved: ACCESS: Show second latest data and export data to excel from macro

Hello,

I have two problems:

1.
I have a lot of scanning data, all date and time stamped. All the scans show different status of shipped packages (all shipments have unique ID#, but there are multiple scans on route for all of them to track the packages). I want to find all the packages, for which there was "misdirected" scan, then for all of them, show the scan before. So, the end result would be all the scans that show for each package where it was misdirected (the terminal# of the scan before the misdirect scan).

2.
I want to automate this: run 5 queries every two hours between 8am and 10pm, copy and paste all results into Excel to specified areas (e.g. first query results to A1, second to A5000, third to A10000, etc. or onto different tabs), save as .csv to a specific folder, then send it as attachment to a given e-mail address. Even partial solution can be good.
I only know macros (somewhat), not as much VBA, so I tried that with the TransferSpreadsheet command, but can't get it work.

Any help is greatly appreciated.

Relevance 100%
Preferred Solution: Solved: ACCESS: Show second latest data and export data to excel from macro

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Solved: ACCESS: Show second latest data and export data to excel from macro

14 more replies
Relevance 94.83%

Hi,

I want to know how i can export the data from Access to excel using Access VBA for the specified sheet using data linkage with access database. Like we used to do it manually in excel as external data from access.

So that the excel size won't be that big and also it saves processing time.
 

Answer:Solved: Access data export into Excel as the data linked to excel.

16 more replies
Relevance 84.97%

I'd like to create a macro which creates a new MS Word document in the same directory as the excel book which copies specific data from an excel worksheet into the new document. The items contained within the cells should appear as a bulleted list on the word document, not a table.

Is this possible?
 

Answer:Macro to Export Excel Data to New Word Documet

I'm not a Word macro person at all..But I'm thinking (quick and dirty) you could do this with mail merge. Don't use the address part just 'insert merged' fields...Your 'cells' would have to be in the same row in excel.

There are also excel to word conversion programs(google). I have never used one. If your data was in Access a 'Report' would suit your needs. Also a script that reads the excel file and writes to file and saves as .doc might do this also.

Going to word 'for the sake of being in Word' seems counter productive but management wants what management wants.
 

1 more replies
Relevance 82.36%

When i export my inventory from my DB into an excel file, my trailer brands are listed as they are in my DB (of course). But when i bulk upload them with XML feed to trailer inventory sites (HTW.com & HTT.com for our example here). My inventory is listed on their site, but it's not searchable because the brand search is a drop down menu on these sites.

Say i goto HTW.com and do a search for 'Blue Ribbon' trailers none of my blue ribbon trailers show up because all of mine are listed as 'BLUE RIBBON TRAILERS, LTD' and the site is searching for 'Blue Ribbon' and although they are the same trailer brand HTW.com does not see them.

What i'm looking for is maybe a Macro or even formula that once i tell it what site i'm working on, will go through my list and replace all my spellings with the proper ones for that site...

Attached is an XLS file with 3 columns, the first is my list as is from my DB, the other 2 columns are the proper spelling for each corresponding site...

Excel's find and replace feature is a VERY crude fix to this, but since it's so time consuming it's NOT a viable solution.

Any takers??

Slurpee? Zack? Anyone??
 

Answer:Solved: Excel -Macro or Formula? Replace my data with similar required data - sample

for the record, a complete solution would be GREAT, but not necessary...
I just need someone to point me in the right direction and maybe a small working sample of like 5 brands and i'll expand the list from there...

cheers
 

3 more replies
Relevance 79.46%

Hi,I want to export a data into excel from Access to the specified range in excel sheet. I have the below codes to open and paste the data into excel, but i want to how i can specify certain range to it.DoCmd.TransferSpreadsheet acExport, , "Salary Master", "C:\Book1.xls", False, "NewSheetName"can you please anyone help me on this.
 

Answer:Export Access data into excel

16 more replies
Relevance 78.59%

Many of our company reports are generated in an Access database. Several of the users will print the reports in Access and export to Excel using the "Analyze with Excel" in order to manipulate the data in the manner they choose.

Problem: The data in one column is not correct. Example: In Access, the data will read 001d, 0001, 001h etc (which is correct). In Excel, the same data will appear as a 0 (zero). It is very inconsistent and does not happen to each record. I tried formatting the column in the spreadsheet as a text column and then exporting / overwriting, but that did not work either.

Any ideas?

We use Windows XP OS and XP Office.
 

Answer:Lose some data when export from Access to Excel

14 more replies
Relevance 77.72%

I am working with a client that has old computers and old software. I've developed an Access application in 2003 to replace a reporting system they have always done in Excel. I would like to keep the data in Access but they are adamant about color coding their information and they require more than three colors which is the max under conditional formatting. I would like to try to export the query directly to Excel in as few steps as possible and would like to assign a button to do this. Can this be done?
 

Answer:Access 2003 - Want to Export Data to Excel with a Button

14 more replies
Relevance 77.72%

Hi, I created a form that allows users to filter records and with a click of a button displays filtered data in a report. If no filter is selected, the report shows everything. So far, it?s working as planned. However, I?m struggling to export the filtered data to excel. My attempt exports everything. Any suggestions?My form is based on MyQuery which consists of multiple tables.TIA,

More replies
Relevance 77.72%
Answer:Ms Access Database & Excel - Import/export Data???

This one has really got me stumped....please help me..I need to Attach a button to a form in access (Called CarDetails, that displys individual data about each car) to transfer specific fields from the database (SUCarSales.mdb) to a spreadsheet (SUCarStickList.xls). The fields and references are as follows. "RegNo" - Cells A7:A21 in excel"StockNo" - Cells B7:B21"Mileage" - Cells C7:C21"Make" - Cells D7:D21"Price" - E7:E21I need to transfer the appropraite details from the database to the spread sheet. Also a button (in the spreadsheet) that transfers these details back to the database. If it would be easier the files are located here:click here(in the speadsheet, the markup is 9%)I realise that this is a bit specialised, but there has to be someone who has had experience woth this. Thanks agian Rja

10 more replies
Relevance 75.98%

I'm working on a Bill of Material creation automation project that requires some expertise in VBA, and I have none.
The objective is to run a macro from an excel spreadsheet called "PGE BOM", to do the following:
1) Go to the folder "C:\Documents and Settings\Desktop\Auto Project"
2) Find all the .mdb databases in this folder
3) Find "HistoricalMaterialItemsAll" table in EACH of those databases in step 2, and import the data from the columns listed below into PGE BOM.xls's columns C through G:
DrawingNumber
ItemNumber
Quantity
PgeCode
Description
The following is a VBA code that my friend had written in Excel 2007. Unfortunately I have an older version (2000) and the code does not seem to be compatible with Excel 2000.
Sub ImportAccessData()
Stop
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
sRow = 2
bFile = False
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath)
strFlNm = Dir
If strFlNm = "" Then bFile = False
Loop
End Sub
Sub GetData(fl)
Stop
strSQL = "Select HistoricalMaterialItemsAll.* From HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, xlCmdTable
Set WB = Application.ActiveWorkbook
Set WS = Applicatio... Read more

Answer:Excel Macro - VBA code to import access data to excel

Hello, and welcome to the board!

When posting code, please use CODE tags, which extremely helps with readability.

Perhaps you could try using the code constant???...
Code:

Option Explicit

Sub ImportAccessData()
Dim dPath As String, sFile As String, strSrch As String
Dim TargetWB As Workbook, TargetWS As Worksheet
Dim sRow As Long, bFile As Boolean, strFlNm As String, strPath As String
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
sRow = 2
bFile = False
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath, TargetWB, TargetWS)
strFlNm = Dir()
If strFlNm = "" Then bFile = False
Loop
End Sub

Sub GetData(fl As String, WB As Workbook, ws As Worksheet)
Dim strSQL As String, iRow As Long, sRow As Long
strSQL = "SELECT HistoricalMaterialItemsAll.* FROM HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, 3 'xlCmdTable
iRow = 2
Do Until ws.Cells(iRow, 1) = ""
ws.Cells(sRow, 7) = ws.Cells(iRow, 5) 'Get the Description
iRow = iRow + 1
sRow = sRow + 1
Loop
Application.DisplayAlerts = False
WB.... Read more

1 more replies
Relevance 103.32%

Hello again,

I have a word doc that has a form in it. The form may have any number of fields (boxes) to type text answers into, or be a drop down list to select an answer from. I need an excel macro that extracts all data from the form, but with every five fields starting a new row in excel. So all data from the form will appear within 5 columns in an excel spreadsheet.

field 1 field 2 field 3 field 4 field 5
field 6 field 7 field 8 field 9 field 10
etc

Is that possible? I have had no luck to do it myself.

Sample form attached, thank you
____________________________
 

Answer:Solved: Excel macro to extract Word form data into Excel

7 more replies
Relevance 102.91%

Hi, i would like to be able to copy the values under the peak list in excel as tab separated or a table from the .rtf file attached?
Cheers
 

Answer:Solved: Formatting data in an .rtf file into a table like in order to export to excel

Like attached ????
Using Word, I replaced all the multiple spaces with one space.
... Replace "Space Space" with "Space" several times ....
then replaced "Space" with a Tab and pasted to Excel
 

2 more replies
Relevance 101.68%

good day.I have data which I need to create report from it, sample: A B C 1 EXPIRY DATE BALANCE ITEM 2 04/24/12 10,000 RED 3 01/25/12 50,000 BLUE 4 02/26/12 30,000 BLUE 5 03/11/12 10,000 WHITE 6 02/07/12 90,000 GREENSHEET1 -REFERENCE(DATA)JAN FEB MAR APRBLUE BLUE WHITE RED GREENSHEET2 - REPORT TO CREATEAbove are the sample of my reference and the report I should create.I have data that has DATE, BALANCE and ITEM, my boss want a report that summarized all the items that will expire on a monthly basis. Is this possible?Thank you very much!

Answer:Show data in a particuar data entry in excel

Please click on the following line, read the instructions found via that link and then repost your example data. Thanks!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

12 more replies
Relevance 101.27%

Hi,

I need to export data from a quite complex database (which I didn't build), the problem I'm facing is that the data is structured in a way that it would require me to "merge" contents from different tables, and I haven't been able to do it properly.

Lets say I have 3 tables with this content:
Code:
tblCustomer
UserID - Name - Phone

tblRepairsA
IdRepA - UserID - Date1 - DescriptionA - PartsA

tblRepairsB
IdRepB - UserID - Date2 - DescriptionB - PartsB
And I need to extract something like

Code:
UserID - Name - Phone - Date - DescriptionA - PartsA - DescriptionB - PartsB
Now, here is the problem: customers do not get Repairs A and B done on the same day/visit and a record is stored only in the table corresponding to the performed Repair, then when a customer does have both repairs the IdReps do not match because of the offset introduced earlier.
Code:
- IdRepA-1 - 03/03/2008 - is assigned to Customer-001
- IdRepA-2 - 06/06/2009 - is assigned to Customer-002
- IdRep[I][U][B]A[/B][/U][/I]-[B]3[/B] - [B]01/01/2010[/B] - is assigned to Customer-003
IdRep[I][U][B]B[/B][/U][/I]-[B]1[/B] - [B]01/01/2010[/B] - is assigned to Customer-003
So far the best I could get with my queries is something like
Code:
UserID - Name - Phone - Date - Description - Parts
0001 - John - 555-5555 - [B][I]01/01/2010[/I][/B] - some-descrip - some-parts
0001 - John - 555-5555 - [I][B]01/01/2010[/B][/I] - another-descrip - another-parts
meaning that I get two entries f... Read more

Answer:Solved: Export combined data from different tables in Access 2003

16 more replies
Relevance 100.86%

What would be the best way to find a specific Column Header, and copy the data found in the column until the column header is found again?
 

Answer:Solved: Excel Macro Copy Data From Columns

8 more replies
Relevance 100.86%

I'm in a bit of a pickle.

I have an excel document with a summary page ("Tracker"). This worksheet has a table in it with a whole heap of customer information, and at the end there are columns for revenue summaries.

Every month I run a report that I add in a new worksheet which is called "[Relevant Month] Revenue Report", and then I do a vlookup to transfer the revenue and product information in columns "C" and "D" of the report into the relevant month columns in the "Tracker". This has been working well for me, but i'll be handing this role over soon so I was wondering if there was a macro that I could use that would do this vlookup automatically for the relevant month?

The information in the reports is not in any particular order so i can't do a simple copy/paste into my "Tracker" summary.

Any help would be greatly appreciated!!
 

Answer:Solved: Excel Macro to transfer data from several worksheets into one

8 more replies
Relevance 100.86%

I have a list of branches. We have a closed branch that I need to have inserted onto this report. There are times that the closed branch shows up becuase they had warranty work to do, but most times it does not show up. This is branch 046.

How do I write a macro to check for the branch and if not there add it in?

To add more to this - each branch should show up in 2 different rows in the same report. So I really need to do this for both Branch 046 and for Net Sales - Branch 046

Example
Branch 045
Branch 046
Branch 047
Branch 049

Net Sales - Branch 041
Net Sales - Branch 045
Net Sales - Branch 046
Net Sales - Branch 047
 

Answer:Solved: Excel Macro to insert row if data does not exist

12 more replies
Relevance 100.86%

Hi,

I need a help with a macro. I have two separate sheets that I need to combine together. Sheet 1 contains combinations of 2 PROCESSES, e.g. 1-2 and 1-3 (columns B and C) and sheet two contains additional data (PRGRAMS) for each of the process (each program on separate row - the process number linked to sheet 1 is in column A). So for process 1 there are 5 programs rows, for process 2 there are 3 rows and for 3 there are 8 rows. I would like to create a third sheet, that combines PROGRAMS (from Sheet 2) based of given combinations of PROCESSES from sheet 1 and attaches LHS (for the left side of the rule and RHS for the right side of the rule). I attached a shortened example. I need this for 600 combinations.

There could be a way in inserting number of rows into sheet 1 e.g. for the combination 1-2 (there would be the number of rows added that equales the count of "1" + count of "2" programs from Sheet 2 and the programs would be pasted).

I hope its not too complicated.

Thanks a lot for your help!

Marketa
 

Answer:Solved: Macro for combining data from two excel sheets

6 more replies
Relevance 100.86%

Hi,

I have a worksheet containing various data including an expiry date, this is created by adding 364 to the start date and then then using an auto fill to fill in all other rows (over 50,000).

I would like to make a macro to do this alongside a number of actions but when making the macro I am specifying only a certain range to fill (A1:A50000). The problem I have is this list is consistently growing and so this range would then not include any new records added. Is there a way that I can make it so it auto fills from A1 to the last present row?

Thanks for your help

Dan
 

Answer:Solved: Auto Fill Data in Excel Macro

8 more replies
Relevance 99.63%

Hello,
I'm finally getting near to the end of my mission to get my trading website online but I have one nutty task left to crack with my data, and I could do with a bit of help please.

I have about 6000 products (graphic novels - I run a comic shop) and I have painstakingly put together my related product info in a spreadsheet laid out as in the 'current layout' below. For most of the products there are 4 related products (0,1,2,3) but for some there are only 2 (0,1) or 3 (0,1,2). The 0,1,2,3 simply pertains to the order from left to right the related products are shown on the site.

What I need to do is get them into the 'required layout' shown below so I can just cut and paste them into a specific Access table that already exists in the online trading software.

However, I'm struggling with this as my Excel skills don't really go in macros and loops and whatnot. (I'm pretty chuffed with myself when I use something like concatenate!)

I get the impression this should be reasonably straightforward though, which is very frustrating. Can anyone recommend an available macro or bit of code that does this sort of thing? Or am I just missing some very obvious functionality in excel?

many thanks in advance,
Jonathan
CURRENT LAYOUT (this is meant to be 5 columns - Product Reference, 0, 1, 2, 3 but it doesn't want to tab nicely)

Product Reference 0 1 2 3
9780867196818 9781593073558 9781593075330 9781593077778 9781560979494
978156... Read more

Answer:Solved: Is there an available macro to do the simple rejig of my excel data I require

6 more replies
Relevance 99.63%

Hi everyone,

Let me just start by saying this forum is awesome. Nothing like getting a new perspective to solving a problem!

Here's the scenario:
I have 3 sheets: Input Sheet, Output Sheet, Translation Table

Both the Input Sheet and Output Sheet has columns of data with a header row in row 1. I need to move entire columns of data from the Input Sheet to the Output Sheet based on the header row.

This is where the Translation Table comes into play. I need to move the columns of data based on what is defined in the Translation Table.

For example, all data in column "Column A" of the Input Sheet should be moved to column "Albert Column" of the Output Sheet.

A sample file is attached to better illustrate.

Thanks in advance for the help!

Simon
 

Answer:Solved: Excel Macro - Cut/Paste Data via Translation Table

6 more replies
Relevance 99.63%

Hi, I need a macro to use in Excel 2003 that will select all data in a column except the header row. At the moment i am using the following command which works well in most cases

Range("A1").Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select

The catch to this is that if the column contains only one row of or no data then the command selects the entire column! Eek, can anybody please help me to fix this?
 

Answer:Solved: Excel macro to select data in a column with a header row

Dim lastRow as long
LastRow = worksheetfuntion.Max(2, Range("A" & rows.count).end(Exlup).Row

The variable will contain the last filled row in column A, the Max function will make sure that it will at least be row 2 just in case the column is empty

Then all you need is Range("A2:A" & lastRow).Select
Or if you need more columns then Range("A2:H" & lastRow).select
 

3 more replies
Relevance 99.63%

Hi,
I have an employee list excel file (vacation) including the details about empno, emp name, last vacation, next vacation, etc in sheet 1.
Now, I would like to write a macro where I will enter the name of the month in a dailog box and the macro should display the list as in sheet 2 of the attached file.

Windows 7 with MS Excel 7.

Kindly help me, its urgent.
 

Answer:Solved: Excel Macro to display data based on month

I've made a quick macro that does as you requested. It is not "smart" at all so if you change where the data is located (by adding a column for example) it will no longer work. It will handle more rows of data no problem. See the attached.
 

2 more replies
Relevance 99.63%

I'm a novice excel VBA macro user. I've manipulated already created macros, but I'm not sure how to create one from scratch. Can someone please help me create a macro.

I'd like to move the values highlighted in yellow to the cells highlighted in green...and then delete any rows that are empty.

Thanks in advance for the help.
 

Answer:Solved: Excel Macro to Move Data in rows to columns

9 more replies
Relevance 98.81%

Hoping to get some help on this question please. I have reviewed the forum and found a similar post, but the sample data is more predictable than mine and the Macro posted doesn't work on my data. See post "Excel Macro to Move data in Rows to column" 28-Aug-2008, 05:47 AM".

On my data, I would like to be able to take the yellow row, and move the data up to the previous row starting in column "R". The rows I would like on the same line, all begin with a date. I don't know how to make a looping Macro that will identify the "date lines", and take the "second date line", and move it to the "first date line's" row.

Any help would be appreciated.
I have attached some sample data. (I hope that's okay?!)

Thank you!!!!
Christine

PS - The range on data varies.
 

Answer:Solved: Excel Macro to sort data by date, then move to new cell

Christine,

this macro below works well with the sample data.
Code:
Sub sorting()
Dim Rng As Range, c As Range, i As Long
Set Rng = Range("A:A").SpecialCells(xlCellTypeConstants, xlNumbers)
For Each c In Rng
i = (i + 1) Mod 2
If i = 0 Then
With c.Resize(, 15)
.Copy .Offset(-1, 17)
.ClearContents
End With
End If
Next
End Sub

How it works:
1. Takes all those cells in column A of the active sheet that contain numbers. (Dates are actually numbers, only formatted to look like dates.)
2. In a loop, it takes every second cell of the set of cells selected in Step #1.
3. Takes a 15 cells wide region starting with the cell selected in Step #2, and copies it to the region starting with the cell positioned one row up and 17 columns to the right.
4. Deletes the content of the original 15 cells wide region.

Caution:
1. If there are any numbers in column A besides dates, then the algorithm is wrong and the macro will fail.
2. If the dataset in the second date line is wider than 15 cells wide then the rest will remain in place. But the macro can be easily updated, just change the Resize line.
3. Don't run it twice on the same worksheet.
4. Always make a backup copy of your data before running the macro, because it is not Undo-able.

Jimmy
 

3 more replies
Relevance 98.81%

I need to write a macro that will copy data from one spreadsheet into a separate spreadsheet (not in the same file). Here is the scenario...

On the first spreadsheet I have a customer's name in one column and a customer's address in another column. On a separate spreadsheet a have those same customer's names without the addresses. On the second spreadsheet the customer's name may be listed more than once. What I need to do is add the corresponding address to the second spreadsheet beside the name of the customer (identical data from both spreadsheets). The reason copy and paste won't work is because the customer may appear numerous times on the second spreadsheet and their are approximately 50,000 rows of data.

The macro needs to basically say IF Spreadsheet 1, Column A (customer) equals Spreadsheet 2, Column G (customer) then ADD/REPLACE info from Spreadsheet 1, Column B (address) to Spreadsheet 2, Column H (currently blank). I just can't figure out how to make it do that. ANY HELP ANYONE CAN GIVE IS APPRECIATED!
 

Answer:Solved: Need Help Writing a Macro for Excel to Move Data From One Spreadsheet to Anot

7 more replies
Relevance 98.81%

I have an issue if certain cells are blank (not sure if they would be null, blank, empty) to have a macro to input "0" in the cell instead. When I look at the format in excel it's under general.

I currently have the following and it puts nothing in the cell. (or seems to be nothing in the cell) I can manually enter a "0"(zero) in the cell and it shows.....

Sub Addifblank49()
Sheets("BPS").Select
Range("c16").Select
Var = Selection.Value
If IsNull(Range("C16").Select) Then Var = "0"
End Sub
I've tried using val instead of var and it won't let me. I'm not real familiar with VBA so any help would be awesome!!
 

Answer:Solved: Excel Macro - enter in data in cell if Null/Empty

10 more replies
Relevance 97.58%

Hi,

I have several completed templates that I need to summarize into a spreadsheet.

Is there a way for me to list down the files and the location of the excel spreadsheets and run a macro for copying the data I need from the files listed and pasting them into a spreadsheet format?

Here's what I am doing now, manually.

1. Open file1
2. Copy B2
3. Paste to SummaryFile on B1
4. Copy C2
5. Paste to SummaryFile on B2
6. Copy D2
7. Paste to SummaryFile on B3
8. Close file1

9. Open file2
10. Copy B2
11. Paste to SummaryFile on C1
12. Copy C2
13. Paste to SummaryFile on C2
14. Copy D2
15. Paste to SummaryFile on C3
16. Close file 2

I hope somebody can help.

Thanks,
Monica
 

Answer:Solved: Excel Macro needed for creating a spreadsheet from data on several but similar files.

7 more replies
Relevance 97.58%

Hello my fellow databasers.

The Information:
I am currently working for a company that is tracking their part(s)
expenses per each customer job.

All customers' expenses are entered into the same 'primary' workbook so entering information is easier than going to each customer workbook. The column headings are as follows.

Customer Name - Date - Part Used - Cost - Invoice # - Additional Notes

There is 1 primary workbook and then each customer has their own workbook.

I have researched for a week now and am not savvy enough with Excel to know exactly what I should search for. I believe a macro is what I want though.
The question:
How, or what would I do, to have each workbook search for its relative 'Customer Name' and paste (auto populate) the information into the workbooks?

ex. I entered a part for John Doe within Primary Workbook. The second workbook sees John Doe, copies the row of all parts that have John Doe in the first column and populates it in the second workbook.

Also, would I need to create a separate Vlookup first to add a value to my customers so it wasn't text based to help the above equation work?
I know I could copy and paste each one, but sometimes we retroactively change values of costs, and for the amount of time I could save by creating ... a macro? or some formula would be well worth continued research. Any help is appreciated.

Application:

It's important for my employer to be able to see what parts were used ... Read more

Answer:Solved: Excel 2010: Macro or substitute to automatically copy data between workbooks

hi
A macro can do what you need but have you considered using filters on the primary workbook where you can select the customer name and only those records will be displayed? This will eliminate the need for multiple workbooks and make your life much simpler. An added benefit would be the ability to filter on other criteria like Date Or Part # so you can see across Customers what occurred on a specific date or which customers ordered a specific part.
Alternatively, this would be an ideal application for Access which would make data entry simpler and provide the information you need in the format you need.
 

2 more replies
Relevance 96.76%

I am trying to hide rows of my worksheet that show a date in the completed column.
The orders that are complete have a date in column F up to this point I highlight them green so I know they are completed but it would be nice if they highlighted green and were hidden when a date was entered into the completed column.
Can someone assist me with creating a macro that will do this?
I am very new to macros so please forgive my lack of knowledge.

I have attached a sample of what I am doing however inf has been changed.

I am using Excel 2007

Thank you,
 

Answer:Solved: Macro for excel 2007 to hide rows based on any data entered in a column

Hi, welcome to the forum,

I put some simple code in the Sheet's vba

Make sure you allow macro's to be run

Just enter a date in the last row of column F for testing.

For the other green rows, just click in column F and update the date by retyping it
 

3 more replies
Relevance 94.71%

My boss would like me to capture data from the "details" area of MS Project 2000 and take it into an excel document. The purpose is to make easy to follow graphs for senior management showing Earned Value on a weekly basis. I can directly copy the info and paste it into excel, but as I have to do this for multiple projects, I am wondering if there isn't a way to link the info so it updates itself when i access the worksheet. We use Excel 97. Thanks for your insights.
 

Answer:Export data from MS Project to Excel

7 more replies
Relevance 94.71%

Hello,

I have created an excel file which provides some important results for my time tracking. Now that I have the results on several cells, is there a way to EXPORT them automatically to specific fields on a website?

Thanks

Answer:Export data from excel to website?

yes. there's a few ways but not easy for an average user

Fill web form from Excel

3 more replies
Relevance 94.71%

Hi
I am trying to export data from word or excel form into a spreadsheet. I have tried the macro at http://forums.techguy.org/business-applications/257309-exporting-word-form-data-excel.html and it worked for all text fields but not the drop down list options that I have in my form. Can anyone please help/

I have never done this before, hence the struggle. Any help would be much appreciated.

I attach the word form and the excel form which also includes the excel spreadsheet that will collate the data for analysis.

I would prefer the form in word and have it set up as that indicated in the forum thread above but picking up the data from the drop down lists as well.

Thanks
LAD786
 

Answer:Export data from word or excel into Excel spreadsheet

Hi there, welcome to the forum.
It seems this post you added on August 12th hasn't given you much answers.
You mention the two files, OK, But what I don't understand
I am trying to export data from word or excel form into a spreadsheet. ...
Click to expand...

You probably forgot to attach the macro's with it, that is if you did anything with the link you mentioned.
If you could attach the files with the macros, I could take a look and see what I can do for you.

Another thinng From Word or Excel.

Which one is it going to be?
 

1 more replies
Relevance 94.71%

Im wondering if it is possible to do this before i start trying...

I recieve an email containing an excel sheet containing several peices of info such as:
Name
Surname
ID
Etc Etc... Now when i open this.. i need the information posted into specified columns in a new excel document where it will have
NAME | SURNAME | ID |
-------------------------------------------
STEVE NORTH 01874
Any help given appreciated.
 

Answer:Excel - Auto export data into specified excel sheet

6 more replies
Relevance 93.89%

I am wanting to get the data from Column "A" to be the title of the text file. NOTE: there are different Plugin ID's in column "A" each to have its on text file. Next from Column "G" I need the name of the computer system that is associated with the Plugin ID from Column "A" in the text file. I'm not to sure how to do this. any guidance would be helpful.

Answer:export data from excel to text file

You may be better off using a free program as we don't know your version of Excel.Excel Converterhttp://www.softpedia.com/get/Office...http://excelconverter.svelte-projec...Exportizerhttp://www.softpedia.com/get/Intern...http://www.vlsoftware.net/exportizer/message edited by Johnw

2 more replies
Relevance 93.89%

Greetings,

I created a form whereby users answer to a prompt and click a button to display a report based on selections.
This works as planned. I then created a second button to export the filtered records to excel using the following code which gives me everything (not filtered) as this doesn?t have the ability to filter record:

Code:
Private Sub cmdExport_Click()
DoCmd.OutputTo acOutputQuery, "My_Query_Name", acFormatXLSX, , True
End Sub
Someone in this forum had similar question and the following code was posted as an example (I failed to copy the link to the discussion, only copied the following code)




Transferspreadsheet does not have the ability to filter records.
If you elect to use vba, here?s an example:Click to expand...


Code:
Private Sub yourButtonToImportName()

Dim db As dao.Database
Dim qrydef As dao.QueryDef

Dim strSQL As String
Dim bolWithFilterOn As Boolean
Dim strTempQryDef As String
Dim strRecordSource As String

strTempQryDef = "__zqry"

bolWithFilterOn = Forms("yourMainFormName")("yourSubFormName").Form. FilterOn

strRecordSource = Forms("yourMainFormName")("yourSubFormName").Form. RecordSource

If InStr(strRecordSource, "SELECT ") <> 0 Then
strSQL = strRecordSource
Else
strSQL = "SELECT * FROM [" & strRecordSource & "]"
End If

' just in case our sql string ends with ";"
strSQL = Replace(strSQL, ";",... Read more

More replies
Relevance 93.89%

I am wanting to get the data from Column "A" to be the title of the text file. NOTE: there are different Plugin ID's in column "A" each to have its on text file. Next from Column "G" I need the name of the computer system that is associated with the Plugin ID from Column "A" in the text file. I'm not to sure how to do this. any guidance would be helpful.

More replies
Relevance 93.89%

I am trying to build an application {using visual basic ?} which will look at a folder containing many spreadsheets, somewhere between 15 and 20,000.

It will need to look in the individual spreadsheets
look at column "a" and go to the last entry,
then go capture the number in that column but in row "g"
Now export that number into another spreadsheet, along with the name of the file.
If the row "g" is 0, then place a "1" in the new cell.

Example
Open spreadsheet "plane 1"
Go to column "a" scroll down to last entry [say it's row 24]
move over to column "g", get number [say it's 58]
now export the number [58] and the spreadsheet name [plane 1] to a new spreadsheet called

[inventory]

Go to spreadsheet "plane 2"
Number is 38

Spreadsheet "plane 3"
Number is 12

Spreadsheet "plane 4"
Number is 0

At the end of this you would have a spreadsheet called "inventory" which contains

plane 1 58
plane 2 38
plane 3 12
plane 4 1 <---- this changed because "0" was in row "g" in the plane 4 cell.

Is this do-able and where would I start or read on how to proceed.

Thanks for your help.

Mike
 

Answer:Export data from multiple excel spreadsheets

11 more replies
Relevance 93.89%

Hi
I have Bank stt in Txt format & my Ledger in Txt format. I have been able to convert these into excel files in Excel 2010.
Now I want to use Ledger as Base document & findout if the cheques deposited/issued are actually credited or not.
So I wil compare the Ledger & Bank stt & export the tallying data to 3rd sheet.
Many Data values will have same value as Bills have same amount. So they can get filtered by Date colum,
I can post a sample data if that helps
 

Answer:Search data in Two Excel workbooks for export to 3rd

Hi, wlecome to the forum.
Yes, I do think a sample with non-confidential info and just a small number of rows of your source file as well as targte file will help to picture your question.
 

1 more replies
Relevance 93.07%

Hello, I have a table set up in Excel, the first column is a column of six digit numbers used to identify a product in our company. The next six columns contain information about that product. I also have an SQL database that I use from Access. I extract the data from the database either by copying it or by using the Access On-line collaboration process. My problem is the same six digit number I use from the database is not recognized in Excel when I do a Vlookup on my existing table. One quirky detail is that if I go to a cell containing the number from the database and edit it (hit F2) make no changes and hit enter, the Vlookup function then recognizes it and extracts the data from my table. I have tried reformatting the cells. I have used the "clean" function, the "right" function, and even "concatenate" with another blank cell. Nothing works exept edit - enter. Any ideas???
Thanks
 

Answer:Solved: Data from Access to Excel

6 more replies
Relevance 92.66%

hi, i want to about how to copy/export data in excel spreadsheets into form format that can be save in pdf or word format and also can be print.

is there any tutorials form any website or youtube that i can refer. thank you.
 

More replies
Relevance 92.66%

I have been given a last minute assignment to create a survey to send to a few end users and I want to compile the data of all the forms I receive into one excel workbook. I tried a few macros, but they aren't 2010 macros and so I am running into issues; I just don't have to time to re-write and could use a little assistance. I think the main difference is that I am using radio buttons instead of text fields.

ALSO I want to automate the entire thing. Meaning I want to open the workbook and run the macro to pull unprocessed forms from one folder, scan for data, import data, and then move them to another folder for archiving.

Also, in my form I don't think I made the comments section into a text field; I am sure I am going to have to correct that in order to capture the data... Right now I am too tired to think on it. At the end of each section I there is a comments field and I want to collect that data onto different sheets in the workbook. I already labeled the sheets.

I have attached the drafts for reference; I only shortened it for upload; the original draft has about 30 questions total. I need to get this done by 1/15/15 so that I have time to receive and compile data by 1/20/15.

Please help!
 

Answer:Export Word Form Data into Excel Workbook

7 more replies
Relevance 92.66%

Dear all, could you show me plz how to create a batch file can export data from excel to text file and save this text file in (C:\) driveplz i need your help

Answer:export data from excel to text file by batch

Post this in the Programming Forum here:http://www.computing.net/forum/prog...Home Page http://ewen.uuuq.com/

5 more replies
Relevance 92.25%

Hi,

I was wondering if anyone could help me. I need to extract data from multiple excel files into a new excel file and sum the values. The data comes from excel files that are made from a template so the data is always in the same place or same cells. For example, i have data saved daily in excel files with the following naming convention 080204, 080304, 080404,..... and so on. What i would like to do is have a macro that will create a week ending sheet by importing and summing the data from that week.

Thanks for reading!
 

Answer:excel macro for copying and pasting data from multiple excel files

thanks for all those who read!!!! let me know if anyone needs the code!
 

1 more replies
Relevance 92.25%

Excel 2003 SP3
Could anybody suggest a way, using whatever method you prefer to copy existing data to another tab, but have the data go transparent by 50%(or thereabouts, so you can just see it), but keep the headings normal font.
Thanks
 

Answer:Solved: Excel 2003 help to copy data to new tab and make data transparent

6 more replies
Relevance 91.84%

I have 2 reports. One store report with say 10 stores, and one report that lists out 4 items. I need to combine the reports so that it lists out the 4 items for each store. So when it is done it will list store1 4 times with a column that lists the items, store2

Example
Store1 - item1
Store1 - item2
Store1 - Item3
Store1 - item4
Store2 - item1
Store2 - item2
etc...
 

Answer:Solved: Excel/Access - combining data

Have you tried combining the data in a Query?
That s what queries are for.
 

2 more replies
Relevance 91.84%

I'm having trouble updating a table in Access. I have a 2 tier project. Using Excel as the front end and Access as the backend. This project is to track file boxes. When the box is full It is closed and the current date is assigned as the closure data and the closure data plus 3 years is assigned as the date to destroy the files.

I need to develop the VBA code to close out the box and to update the table in Access with the Closure data and the destroy data. I can add a new row of data by:

Sub Append_NewboxData()
' exports data from the active worksheet to a table in an Access Database

Dim cnt As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim stDB As String, stSQL1 As String
Dim stConn As String
Dim wbBook As Workbook
Dim wsSheet1 As Worksheet

Dim lnField As Long, lnCount As Long
Dim Dat As String
Dim A As Object

'Instantiate the ADO-objects.
Set cnt = New ADODB.Connection
Set rst1 = New ADODB.Recordset

Set wbBook = ThisWorkbook
Set wsSheet1 = wbBook.Worksheets(2)
'Path to the database.
stDB = "J:\Case_Closure_DB\Closed_Case.mdb"

'Create the connectionstring.
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & stDB & ";"
With cnt
.Open (stConn) 'Open the connection.
.CursorLocation = adUseClient 'Necessary to disconnect the recordset.
End With

With rst1
.Open "tblBox", cnt, adOpenKeyset, adLockOptimistic 'Create the recordset.
.AddNew 'Insert... Read more

Answer:Solved: Need to update data in Access from Excel

6 more replies
Relevance 91.84%

I need to read data from a database into a spreadsheet based on variables on the sheet. I've found several examples of how to read data into Excel, and none seem to work.

Here's one for example:

Code:
DatabaseName = "Northwind"
QueryString = _
"SELECT * FROM product.dbf WHERE (product.ON_ORDER0)"
Chan = SQLOpen("DSN=" & DatabaseName)
SQLExecQuery Chan, QueryString
Set output = Worksheets("Sheet1").Range("A1")
SQLRetrieve Chan, Output, , , True
SQLClose Chan
I get an error on the SQLOpen - Sub or function not defined.

My hope is that I can read specific data into an Excel spreadsheet from my Access database. In case you're wondering why...my client has hundreds of spreadsheets that they need converted to one with that one sheet reading it's data from Access. I need to be able to use VBA to change the query and requery based on an event (Button Pressed)

Let me know if you need me to toss a non-working sample together.

Thanks in advance.
 

Answer:Solved: Read Access Data into Excel using VBA

11 more replies
Relevance 91.43%

Hi there ... I want to write a macro that would take the user to the next new unique data in a column. I have a list of account numbers that may be listed 3 times back to back in the column or up to 50 times back to back. I have a particular cell pointed at the top of the column, (ie, "=A1") and I want a macro to take the user to the next new account number in that column, which could be any number of rows down.

Suggestions? The cell I want the reference to change is in sheet1 and the account numbers are in a different sheet, same workbook.

Thanks!
 

Answer:Macro for Excel - next new data

14 more replies
Relevance 91.02%

Hi guys,

My buddy's small office has about 20 MB worth of database data (mainly contact information for about 5000 people) in an old, lousy MS Access database. He wants to pull the data out and into something like Outlook or even MySQL.

Is there a way to do extract that information and place it directly into SQL or Outlook?
Thanks for the help!
 

Answer:Export MS Access DB data to Outlook or MySQL

A 20MB Access database isn't lousy. You will realize what lousy is when the mdb file grows to nearly a gig

Export to Outlook? You'd have to proxy this process and construct a Access dump to CSV or Excel file, and try importing that.

As for MySQL, you can try looking into the "MySQL Migration Toolkit".
 

2 more replies
Relevance 91.02%

Is there any way to increase the limitation of the amount of data can be exported from Acces?. Also how can i increase the autonumber field limitation?. Presently its set to be approx. 9600 for autonumber data and 16000 for export of data.
 

Answer:Export Import Data Limit in MS Access

Export to . . . what?
Make sure you're using the correct version to export to.
 

2 more replies
Relevance 90.61%

I have a spreadsheet with person identifier in column A, date in column C and location in column D (column B is irrelevant to my query)I would like a macro with would infill columns C and D when person identifier in A is equal,A C D234 12/5/11 GP surgery234345So I would like the macro to infill C and D with 12/5/11 and GP Surgery for A=234 but not for A=345I would like the macro to loop through my worksheet

Answer:I'd like a macro for excel to in fill data

What do you mean by "hen person identifier in A is equal"?what does 234 stand for? is this a constand number or will this change,im sorry i dont understand what you have written can you please explain a little better?

4 more replies
Relevance 90.61%

Hello - I want a macro in excel 2003 or (workspace pro 6.5 or Happy Harvester2) that collects info from the web sub page, one case after another case, till done. It should put selected info in excel, each case on a different line. However, the excel "repeat" under "edit" that I am using, fails to accomplish moving to the next line; instead it repeats the first line. I have made the macro with mouse clicks, and dont understand VBA:

Desired excel result"
ADDRESS CITY OWNER LENDER CASE DATE
123 Maple | Xenia, Ohio | Able Jones | Bell Mortgage | cv1223 | 11/04/2010
380 bell st | Dayton, Ohio | Blule | Bank America | dv 3348 | 10/03/2010

Web page listing cases URL: http://www.co.greene.oh.us/pa/pa.urd/PAMW6500
Able jones cv 1223
brown cv 129
blule cv 3348

Sub page with details on one case
URL: http://www.co.greene.oh.us/pa/pa.urd/pamw2000*o_case_sum?473007
Bell Mortgage vs Able JOnes cv1223
address: 123 Maple
Xenia, Ohio 45385

filed 11/04/2010 status open
Thanks, thanks so much.
 

Answer:Excel macro geting web data

What program did you actually create the macro in? Can you post your existing code?

Rollin
 

1 more replies
Relevance 90.61%

Hello,I'm trying to create a macro to do a variety of functions. The data set I'm working with is generate from a Fuel Injection ECU. Once a test run is done, the data is exported into Excel. I end up with four columns of data, Time Stamp, MAP, RPM, and O2 Reading.The Time Stamp column is deleted, then the MAP value is rounded to the nearest multiple of 10 using MRound, RPM is rounded to the nearest multiple of 100 using the same function, and the O2 Reading is rounded to the nearest 2 decimal places.I then swap columns A (MAP) and B (RPM) so RPM is in column A, followed by MAP and then O2 readings.I then pull off each set of RPM data and insert it into a new worksheet labeled with the RPM data.Once the data set for a certain RPM range is in the new worksheet I then sort the data according to the MAP data and separate out the initial columns into the different MAP ranges. Finally I average the O2 reading for each MAP range at that RPM.The data sets are very large with anywhere from 1000-5000 entries so as you can imagine it takes awhile to sort through this by hand. Any help, sample codes that I could piece together, etc would be appreciated.

Answer:Help with Excel Macro, Data Sorter

re: sample codes that I could piece togetherThe Time Stamp column is deletedColumns(1).Delete
or
Columns("A").Deletethe MAP value is rounded to the nearest multiple of 10 using MRoundFor rwMAP = 1 To 1000
Range("A" & rwMAP) = _
Application.WorksheetFunction.Round(Range("A" & rwMAP) / 10, 0) * 10
NextNote: MROUND can be used in VBA if you have the Analysis ToolPak installed and you set a reference to atpvbaen.xls in the VBA editor.O2 Reading is rounded to the nearest 2 decimal placesFor rwMAP = 1 To 1000
Range("A" & rwMAP) = _
Application.WorksheetFunction.Round(Range("A" & rwMAP), 2)
Nextswap columns A (MAP) and B (RPM)
Columns("B:B").Cut
Columns("A:A").Insert Shift:=xlToRight
Everything after that is going to need a little more explanation. I'm not sure what you mean by each set of RPM data or MAP range at that RPM.

2 more replies
Relevance 90.61%

I would like to write an Excel macro that includes pausing for data entry from the keyboard. For instance I have a spreadsheet for a simple bookkeeping project. I would like a macro that, when invoked, would do this:
Type "Current income" in the cell the cursor is on.
Move two columns to the right.
Stop for me to (1) enter a dollar amount and (2) hit the "enter" key.
Move two columns to the right.
Stop for me to (1) enter a dollar amount and (2) hit the "enter" key.
End of macro.

My thanks for any help!!
 

Answer:Excel macro for entering data

11 more replies
Relevance 90.61%

HiMy orginial excel 7 data looks like this.Name UserID OrderedDate RequestedDate OrderNumber FoodItem Quantity Email Phone Data deleted by Moderator due to possible plain text email addresses and phone numbers.I then run this macro against it.Option ExplicitSub Consolidate()Dim LR As Long, i As Long, MyVal As StringLR = Range("A" & Rows.Count).End(xlUp).RowApplication.ScreenUpdating = FalseFor i = LR To 2 Step -1If Cells(i, "A") = Cells(i - 1, "A") ThenIf Cells(i, "F") = "" ThenRows(i).EntireRow.Delete (xlShiftUp)ElseMyVal = Cells(i, "F").ValueRows(i).EntireRow.Delete (xlShiftUp)If Cells(i - 1, "F") = "" ThenCells(i - 1, "F") = MyValElseCells(i - 1, "F") = Cells(i - 1, "F").Value & ", " & MyValEnd IfEnd IfEnd IfNext iColumns("F:F").AutoFitApplication.ScreenUpdating = True[J:J].Delete[I:I].Delete[H:H].Delete[E:E].Delete[B:B].DeleteEnd SubWhich gives me the following results.Name OrderedDate RequestedDate FoodItem Quantity Data deleted by Moderator due to possible plain text email addresses and phone numbers.I need to edit the macro so it does the following-Each item needs to be listed on a separate line in that row.-The quantity number needs to show up next to each separate item.-I would like to have the data go to a new sheet instead of wiping out the original data once macro is run against it. (I know you did this in the macro you sent me but I could not get it to work with this macro).Thank you very much in advance.Mark edited by moderator: email & phone number data delete... Read more

Answer:Need Excel Macro Help with Data Manipulation

I have deleted the data that you posted because it appeared to contain email addresses and phone numbers. Since I can't tell if these were fake or not, I had to use caution and delete them.Please try to post generic data that shows your layout without being a possible security risk. In addition, please refer to the How To in my signature line before you post anymore data or code. The How To will explain the proper way to post data and code to make it easier to read in this forum.DerbyDad03Office Forum ModeratorClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 89.79%

I am new to VBA and am trying to create Macro for Excel 2003. I understand the logic behind it but when I recorded the macro it didn't capture what I wanted. I want data pulled & deleted from Worksheet A (TASKS) to Worksheet B (COMPLETED) depending on value entered in colum F (worksheets are exactly same). My rows start with A3 and end I3 all the way A500-I500. If 100% is entered in column F... I want the row associated to be deleted from TASKS and moved to COMPLETED in the same cells so that it's under the same section.

Answer:Excel Macro - Move & Delete Data

Hi,Here is a macro that moves rows from the TASKS worksheet to the COMPLETED worksheet based on the value 100% being entered in a cell in column F of the relevant row.The Change event is used to detect the change in a cell value on the worksheet and then the changed cell is tested to see if it is both in column F and contains '100%'.If that is the case, it copies the whole row, to the same row on the Worksheet named COMPLETED and then clears the contents of the row on the TASKS worksheet.Note that for this to work automatically, the 100% in column F must be entered by hand - the change event will not trigger if the 100% is generated by reference to another cell.Test this out on a copy of your workbook - see warning note at end of this post.To add code, right-click on the name Tab for the TASKS worksheet.Select View code.In the window that options paste the following:Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHnd
Application.EnableEvents = False
'test if changed cell is in column F and that it contains 1 (100%)
If Target.Column = 6 And Target.Text = "100%" Then
'copy to same row on 'Completed' Worksheet
Target.EntireRow.Copy _
Destination:=Worksheets("COMPLETED").Range("A" & Target.Row)
'Clear source row data
Target.EntireRow.Clear
End If
Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
Application.EnableEvents = True
End SubUse ALT+f11 (Alt key and function key 11 pressed together) to... Read more

2 more replies
Relevance 89.79%

I would be very grateful if someone could help. I have a worksheet ("Master") which contains information on patients undergoing operations. The sheet has around 2000 lines and 56 columns (A:BD). Each row represents one patient and column C contains the name of the surgeon who carried out the operation I need to create separate worksheets for each of the 23 surgeons involved, each conatining a subset of the data on the "Master" worksheet. To give an example, I might have a worksheet called "Mr Smith" which would correspond to a surgeon's name in column C of "Master". On this sheet I need details of any patients who he operated on, but I do not need information from every column - probably only about half of them. They data fields are a mixture of text, dates and numbers.Is it possible to write a macro that will be able to do this? I would be very grateful for any help, as I have never tried to write a macro before. I am using Excel 2003.Thanks

Answer:Using a macro to select and move data (Excel)

I suggest you try this code in a backup copy of your workbook since macros can not be undone.This code will create a list of the surgeon's names in a new sheet ("MD List")and then create a sheet for each surgeon.It will then copy each row that contains that name in the Master sheet to the specific sheet for that surgeon.Since I can't see your spreadsheet from where I'm sitting, I had to make some assumptions.On your Master sheet, I am assuming that you have column labels in Row 1 and that your data starts in Row 2. I am assuming that you want the same column labels on each individual sheet.You said that you didn't need to copy the entire row, but you didn't say what you really needed, so the code copies the entire row. This can be modified.Let me know what you thinkOption Explicit
Sub MD_Sheets()
Dim numShts, numItems, srcMD, mdSht, nxtRow As Integer
Dim mdName As String
'Add a new worksheet for filtered list of MD Names
ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "MD List"
'Create a list of unique MD Names
Sheets("Master").Columns("C:C").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ActiveSheet.Range("A1"), Unique:=True
'Count number of MD names so we know how many sheets to create
numShts = WorksheetFunction.CountA(Sheets("MD List").Columns("A"))
'Create new sheets, named for each MD, add Column lables
For mdSht = 2 To numShts
ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name ... Read more

3 more replies
Relevance 89.79%

I need assistance with creating a macro. I need to create a lot of different reports but I want the macro to create them for me. Is there way to create a macro by: having a list of data, sorting it all by column A, then copying whatever is the same name in column A (and whatever is associate with column A- to the right) and inserting it into a new worksheet and then doing it for all the different data that is in column A. Then once it is put into the worksheet, to save it with whatever the name is from Column A in the first worksheet.

I figured that there needs to be a loop statement in the end so it will get all the grouped data together into seperate worksheets.

Thanks for your help. I have attached a spreahsheet to get a better idea of what I was looking for.
 

Answer:EXCEL MACRO- sort, copy different data

6 more replies
Relevance 89.79%

I have a macro that searches 4000 rows of data which returns many results. The macro stops when a result is found and I have to copy and paste the criteria and the result to the next blank row in another sheet. The criteria is in sheet A range G2:K2The Result is in sheet A range H6:K6 and H10:K10How do I record these 3 ranges sequentially on a row on sheet B without stopping the run to do it manually?Please can you help.

Answer:excel macro to Find & Copy Data

It might help us help you if you would post the code you are currently running.Since we can't see your code from where we're sitting, it's a bit difficult to suggest modifications.

6 more replies
Relevance 89.79%

I have a column that will have values changing but not in consecutive order. For example if I had range A1:A10, only A1, A4, and A9 might have values, but the rest of the cells would be blank. I need to be able to generate A1, A4, and A9 into the next column consecutively as B1, B2, and B3. The A column values could change (including the number of values) and I need the B column to react accordingly. So the next time I run the data, I might have A2, A3, A4, A6, and A10 with data in them - so I need column B to be able to react and put the values (in order) as B1, B2, B3, B4, and B5. Oh and I need all this done in a MsgBox. Can you help? Thanks so much.
 

Answer:Excel Macro Advice - Data Consolidation

9 more replies
Relevance 89.79%

I have been scouring site after site and googling like crazy to find the type of info I have seen here. Great help guys.I am attempting to sort data from worksheet 1 and copy it over to 26 existing different worksheets. I want to keep the info on sheet 1 available and have it copy and pasted based on information in column E. Column E would reflect names of workplaces for those 26 other worksheets (already named).From what I have seen Derby and Humar have been a huge help and really appreciate any help you or anyone else can give me.(Sorry if I left room for question on the goal. 1st time posting forums for tech help.)

Answer:Excel '07 Macro for sorting and copying data

This thread here is pretty much what I am looking for (from what I read). I attempted to modify it for my needs with no luck whatsoever...http://www.computing.net/cgi-bin/my...I changed references to worksheets and source page information and probably things I should not have. (reason why I am still watching it do the same thing it has been for the last week... lol)

3 more replies
Relevance 89.79%

Hi,
I have a major problem crunching large amounts of numbers coming off an instrument I work with but the file format it puts the results into is terrible and I cannot change it. So I must export the results in a format which needs some serious formating before it becomes of any use to me.

I have used excel macros to delete rows and rows with specific text values. But now I would like to create a macro that will reorganise this data in a more user friendly format. I did use the sort asending command to partially solve the formating problem but it would still require alot of work/time to organise it into the final format I need.

This is the current format of the instrument date:

Analyte Meas. Intensity
Rh 10
Y 98
Cs 66
Co 98
Analyte Meas. Intensity
Rh 10
Y 114
Cs 72
Co 86
Analyte Meas. Intensity
Rh 20
Y 108
Cs 44
Co 90
Analyte Meas. Intensity
Rh 20
Y 68
Cs 44
Co 66
Analyte Meas. Intensity
Rh 490.008
Y 58
Cs 34
Co 78
Used the "sort asending" command to separate data into useful form but need to get the data into either of the two tables shown below

Analyte Meas. Intensity
Analyte Meas. Intensity
Analyte Meas. Intensity
Analyte Meas. Intensity
Analyte Meas. Intensity
Co 98
Co 86
Co 90
Co 66
Co 78
Cs 66
Cs 72
Cs 44
Cs 44
Cs 34
Rh 10
Rh 10
Rh 20
Rh 20
Rh 490.008
Y 98
Y 114
Y 108
Y 68
Y 58
I would like to turn the data into this final format using a macro.

Co Cs Rh Y
98 66 10 98
86 72 10 114
90 44 20 108
66 44 20 68
78 34 490.008 58

Can somebody help me please ... Read more

Answer:create an excel macro to reorganise data?

ZIP and post a sample workbook.
Rollin
 

1 more replies
Relevance 89.79%

I have a need to move data from one worksheet that has an associated date to the correct spot in another worksheet with column that runs from 1/1 to 12/21.
Worksheet 2
1/1
1/2
1/3
1/4
...
12/31

Worksheet 1 has:
1/29 45
1/30 23
2/4 13
The macro would read the date in column A of worksheet 1 and put the data into the correct row of column B in worksheet 2. So that after running the macro worksheet 2 would look like this:

1/1
...
1/28
1/29 45
1/30 23
2/1
2/2
2/3
2/4 13
...
12/31

I hope my intention is clear and understandable.

Thanks in advance for any help,
tom
 

Answer:Excel Macro to move data into correct rom

8 more replies
Relevance 89.79%

I have started recently to mess around with macros but I can't quite figure out how to do this one. I have this list of songs with three columns (artist, title and track #) but what i want to do with it is to insert a row between each artist while dropping down the track #. But when the artist is the same i want to add these track numbers. Any help would be appreciatedThanks in advance!

Answer:Excel Macro Help Insert Rows, Sum Data

I don't understand what you are trying to do.Please click on the following line and after reading the instructions found via that link, post a small sample of your data, before and after.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more replies
Relevance 89.79%

I need some help with a macro. I need to save data from certain cells in 1 worksheet and to another worksheet. Details belowI want to grab the data in worksheet "survey" from cells A3, A4 and A6 and save them to worksheet "data" in cells A2, B2 and C2. After the save is done I then want to clear the data from cells A3, A6. I created an object button in my workbook that is clickable. I just need to assign a macro to it. I have been working on this for over a week and I am new to macros so it is a struggle. I have even went as far as purchasing a program call teachmemirosoftoffice and still can't figure out the whole thing. I get portions of it to work but not all of it. I would greatly appreciate anyone who can help me write this macro. I would forever be indebt to you my friend. Thanks.

Answer:Need a macro for excel 2007 to save data

Since you do not appear to have a contiguous range of cells in your source document, you have to do each cell individually. A contiguous range could have been copied and pasted Transposed into your destination, but since you skipped A5, you can't use that method.Try this:Sub CopyThenClear() Sheets("data").Range("A2") = Sheets("survey").Range("A3") Sheets("data").Range("B2") = Sheets("survey").Range("A4") Sheets("data").Range("C2") = Sheets("survey").Range("A6") Sheets("survey").Range("A3, A6").ClearContentsEnd Subre: "I would forever be in debt to you "Forever is a long time to be indebted for a few lines of VBA code. A simple thanks and a promise to pass it forward will suffice.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more replies
Relevance 89.79%

Hey,

Posting here before, I received several prompt replies. Due to my success, I will try once again, but this time with a different issue/question.

I have a spreadsheet with 1 column and 3000 rows. In each row, there is a 1 cell block with a series of numbers/characters. I need some type of formatting/macro that will extract only certain characters from each row and place them somewhere else. In example, I have the following rows:

7r74$#@$i4 i3o45$#---$#@$#/4432438
4327$##@$#4343u3i33$$( #343432822
4324*& 345435**(&^*&^342434234324

I need to extra the 12, 13, & 14th character of each row, whether it be a letter, number or space, it doesn't matter. Now the 12, 13 and 14th character need to be placed in there own newcolumn to the right of where they were prior. So the results, would look like this:

7r74$#@$i4 i3o45$#---$#@$#/4432438 i3
4327$##@$#4343u3i33$$( #343432822 343
4324*& 345435**(&^*&^342434234324 435

The post here will not let me space it correctly, but to the right of the first entry would be " i3", the second, "343" and the third "435".

So, it seems pretty simple, but I am not knowledgable enough with formulas, macros or vb to be able to figure this out. I also need to be able to adjust the formatting/macro/vb script every couple months as the "character extraction" place could change.

Thank you, hopefully someone is familiar with what I am trying to do and can understand this basic i... Read more

Answer:Excel, Formatting/Macro Creation for Data

Are you okay with just a formula? Try this if your data is in A1:

=Mid(A1,12,3)

12 is the starting position, and 3 is the number of characters to return.

Let us know if you do need it in a macro.

HTH,
 

3 more replies
Relevance 89.79%

Hi,
I am trying to run a macro which will insert a row of formula to the last row of my data. Say for example.

A1: Formula
A2: 1
A3: 2
A4: 3

I wish to set the macro to always insert into the last row of column A, instead of at Cell A4. Something like,

A1: Formula
A2: 1
A3: 2
A4: 3
A5: Formula

something to do with lastrow formula? Any help would be greatly appreciated.

my current macro is written like this,
'
Rows("13:13").Select <- the formula row
Selection.Copy <- copying the formula
Rows("17:17").Select <- i wish this to go to the last row instead**
Selection.Insert Shift:=xlDown
End Sub

Any help would be much appreciated.

Thanks
 

Answer:Excel Macro : inserting copied data to the last row

Howdy, and welcome to the board.

Something like this:
Code:

Sub test()
Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox lngLastRow
End Sub

then you could use for the next formula row:

Cells(lngLastRow + 1,1)
 

3 more replies
Relevance 88.97%

I have an entire column of data that I need in one cell. When combined I need each piece of data to be surrounded by " " and a , between each one.

151
183
2001

results = "151","183","2001"
 

Answer:Solved: Excel Column of data place in one cell with "data", "data" results

ASAP's "Merge row data" ("Columns and rows" category) will do 99% of the work for you.

http://www.asap-utilities.com/

(edit: "entire column" -- do you mean full from top to bottom? )
 

2 more replies
Relevance 88.56%

I'm manually entering payroll data and I feel certain this process could be automated.In one spreadsheet I have the list of employee names and IDs. In another sheet I have the payroll schedule. Each employee ID needs to be copied next to the 21 pay dates in the calendar year. That process repeats for every employee. I understand how to record a macro (what to click) but don't know how to identify mistakes when my recording doesn't do what I intended.How do I create a macro that copies information from one cell in spreadsheet one 21 times down a column in spreadhseet two before moving onto the next cell in spreadsheet one?

Answer:How to write an excel macro that copies data between sheets

re: I understand how to record a macro (what to click) but don't know how to identify mistakes when my recording doesn't do what I intended."As you probably realize, the macro recorder creates very specific code which will basically do nothing more than repeat the steps you took while creating it. To make matters worse, the code is often bloated and inefficient. It just about always need to be cleaned and modified.One place to start is by reviewing the steps detailed in this tutorial:http://www.computing.net/howtos/sho...These debugging techniques will not only help you "identify mistakes" but it can also help you learn how to write VBA code. By using the debugging techniques discussed, you can reverse engineer code that you find in forums such as these as means to understand how the code does what it does.re: "How do I create a macro that copies information from one cell in spreadsheet one 21 times down a column in spreadhseet two before moving onto the next cell in spreadsheet one?"Without the specifics related to your spreadsheet layout, the best we can offer is a generic solution. Perhaps the following code will help you get started, especially if you use the debugging techniques from the tutorial to help you follow along as the code does it's thing.Let's say you start with this in Sheet1:
A
1 Tom
2 Bob
3 SueThis code will place Tom in A1:A21 of Sheet2, then place Bob in A22:A42, etc.
Sub Copy21Times()
'Determine last Row with data in Sheet 1 Column A
lastR... Read more

2 more replies
Relevance 88.56%

Hello,I am new to macros. I am not sure if this can be done, but I hope it can. I would like to create a macros that will create a new sheet for each day of the week automatically. Then pull certain information from the previous day to carry over. I would like it to automatically generate 5/1/2010, then 5/2/2010, etc. for example. I would like it to then take those days of the week and compile them into a new sheet labled 5/1 - 5/8 (looking @ my calendar for May). I would like for the info from the days of the week to be compiled into a summary for the week.I would then like it to compile the data from the weeks of the month into a new sheet labled month (May for example). And again, compile me a summary for the month based on the weeks and days information.Finally, I would like it to take all the months of the year, and compile that data into a new tab for the year (2010) for example. And the automatically summarize certain data for the entire year. Then, start over for 2011 doing the same thing. Days, weeks, months, year. I would like the months of the year to automatically go under the year tab, so that if I click the year tab it exapands to months, if I click on a certain month it expands to weeks and if I click on a certain week in the month it opens the days of that week.Hope that make sense. Is this possible to do?

Answer:Excel macro to Create Sheets, Copy Data

Hi,If you are asking for macros to do all of what you are asking for, then I suggest that you hire a programmer!Most of what you want is possible in Excel, although some things don't work the way you suggest:I would like the months of the year to automatically go under the year tab, so that if I click the year tab it expands to monthsI don't know what you are thinking of when you refer to tabs, such as 'the year tab'. Are you referring to the worksheet name tab, or are you thinking of custom forms with a series of tabs. Worksheet name tabs don't lend themselves to 'expanding'.create a new sheet for each day of the week automaticallyThis will result in 365 worksheets just for one year's data, excluding the month and other summary tabs.It is far better to start with all your data in one block - on one worksheet, then you can extract data for specific periods from it.Having daily data on separate sheets makes finding it and analyzing it a lot more difficult, even using visual basic macros. If you have a column containing dates, Excel stores dates as numbers which can be used to find a range of dates. If you have dates on worksheet name tabs, the 'date' is stored as a string, not as an Excel date number, so selecting worksheet names based on text representations of dates becomes quite complex.If you add 1 to a cell containing 01-Jan-2010, you get 02-Jan-2010, but there is no direct way to add 1 to a worksheet named 01-Jan-2010 to get 02-Jan-2010.If your monthly data is on separ... Read more

4 more replies
Relevance 88.56%

Sirs,I have a workbook in excel with 3 sheets. Sheet 1 ("COLUMNAR")ID DATE PAGE NO OR NO NAME OF PAYEE AMOUNT SECTION PARTICULARS 1 1/5/11 PAGE 1 0000701 JOEY BANTOG 26.00 DRY GOODS Sheet 2 ("SAMPLE") Page 1 Serial No. Amount(From - To) PayEE Acct. Code Description A B C D EI need to automatically transfer Serial No, Payee and amount to Sheet Named "Sample" which is the report form leaving Acct Code blank however using the Page No as the ref cell Sirs please help me my job is at stake with this report. Thanks

Answer:Macro Code to copy range of data in excel

Please repost your data after reading the How To referenced in the following line:Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 88.56%

If you check the file attached.

please can you help me in moving data with duplicate names to have the 2nd and 3rd email address moved to a new column and delete the row

need an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.
 

Answer:Excel Macro to Move data in Rows to column

I'm unable to understand the entire requirement, but for moving the records with duplicate names, I've written few lines. This will cut the duplicate record rows (Consider "Pupil Forename" for removing)from the "Report Data" and will post into "Sheet3". Before running this macro please insert "Sheet3". Let me know your requirement, hope I will be able to resolve it out. Thanks!

Code:
Sub test()
Sheets("Report Data").Select
Dim trow As Long
trow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For i = trow To 1 Step -1
If i = 1 Then
Exit Sub
End If
If Cells(i, 1).Value = Cells(i - 1, 1) Then
Rows(i).Select
Selection.Cut Destination:=Sheets("Sheet3").Range("A65536").End(xlUp)(2)
End If
Next i
End Sub
 

2 more replies
Relevance 88.56%

Hello. I'm facing the following problem:Every day I have about 56 Excel files where the filenames fit the following 2 patterns:- half of them are named STAGE1_PAID_DD_MM_YYYY: these files would each have 2 columns (Column A is in Text Format with no headers + Column B will also be in Text Format with no headers, but the entries will be a bunch of standardized error codes repeating themselves from one row to another).- the other half are named CRON_DD_MM_YYYY: these files would each have 2 columns (Column A is in Text Format with no headers + Column B will also be in Number Format)I will always have 56 files because I will always be interested in pulling out data from the Excel files starting with DD_MM_YYYY = TODAY()-29 up to DD_MM_YYYY = TODAY()-1.What I need is a Macro that I can run on daily basis from an empty workbook which must:- 1st gather all the data from all the CRONs into one unique 2 columns auxiliary list1 and arrange the data by Column A; if there will be values on column A which appear more than once in the CRONs, they must also appear more than once in the auxiliary list1.- on a 2nd phase, this auxiliary list1 must be processed as follows: * the already existing unique values by column A must remain as they are * the values appearing more than once by column A must be turn into a unique vale by comparing the values on colum B and keeping only the row with the MAX number on column B- on a 3rd and phase, all the rows where t... Read more

Answer:Excel Macro to find data that fits Patterns?

unga msg a padichu enaku paithiyame pidichuduchu ya.... very breaf

2 more replies
Relevance 88.56%

Hi I am new to Excel macros, I need helpwith the following automation:I am trying to copy data from the main spreadsheet name "PMO Report" where we have 5 columns D-H and rows 10-114 paste it into different sheets based on the project name in column H. All projects except for the project name TM needs to be copied to the spreadsheets. There are a total of 6 different projects in column H. Project names are (FCS,FCD,FCT,FPD,MSS,TM). So basicly i want to extract whats in "PMO Report" columns D-H into seperate sheet based on the project name except for TM. Thanks in advance for your help...Regards. Moe

Answer:Excel Macro to move data to separate sheets

Hi Moe!When posting in a help forum, please try to use a subject line that specific to the contents of your post.If everyone used generic subject lines like "Excel Macro" or "Excel Help" (something we see a lot!) then we couldn't tell one question from another and things would get very confusing around here.I fixed the subject in this thread, but we'd appreciate it if you could keep that in mind for future posts.Thanks!DerbyDad03Office Forum Moderator

4 more replies
Relevance 88.56%

Hi, I would like to thanks in advance for someone who help me.The raw data on my excel file is a list of pilots taking different training courses. I would like to filter in terms of the name of each course (column F - Description), and copy the filtered data to new sheet. Then rename the new sheet created based on the name of the course.So, if there are 15 courses, there will be 15 new sheets created.The course number may be increased or decreased in the future.Is there anyway for a macro to do that? Here is the sample dataCol1 Col 2 Col3 Col4 Col5 Col6 Col7 Col8RECRU EMPLOYEE_NAME EMPLOYEE_ID GROUP_TYPE REG_QUAL DESCRIPTION LAST COMPLETED NEXT XXX XX 100 XXX XXX Abc XXX XXXXXX XX 100 XXX XXX Abc XXX XXXXXX XX 100 XXX XXX Def XXX XXXXXX XX 100 XXX XXX Gih XXX XXX

Answer:Excel Macro, Filtering, Copy data, Rename

Hi,Here is a macro which will create new worksheets for each course and will copy the relevant records to the appropriate Course worksheet.The macro starts with a pop-up asking if you want to run this to just add new data, or to re-run the whole thing from the start - appropriate if you have updated source data that has already been copied to individual course worksheets.The macro identifies course worksheets by using the word "Course" and adding the text from column F to it.The Word "Course" allows the macro to identify worksheets that contain Course data.When the macro has run, it puts the word "Copied" at the end of the data that has been copied.Add new data to rows following "Copied"When the data is updated, the word "Copied" will automatically be moved to the end of the data.I suggest that this macro is attached to a button embedded on the worksheet containing your source data.You haven't said what version of Excel you use, so I have included instructions for Excel 2003.Excel 2007 or later is slightly different, in terms of adding the button, and the name of the button inside Visual Basic.If you have Excel 2007 or Excel 2010 and need more help, please ask.(Note: VB macros don't work in Excel 2008 for Mac)On the source data Worksheet, create a command button from the Control Toolbox toolbar.(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).Select the button Icon and draw a buttonRight-click the button and select Command Button - Edi... Read more

2 more replies
Relevance 88.56%

I have a spreadsheet with 14 columns. Twelve of the columns contain categories of data that correspond to the entries in the other two columns. I need to change the list so that each item in each category occupies its own row, as follows:Eg.From this:col1 col2 category1 category2 category3 etc...aaa bbb $55 ccc ddd $44 $66To this:col1 col2 category amountaaa bbb 2 $55ccc ddd 1 $44ccc ddd 3 $66Can anyone assist?Many thanks,jeannie

Answer:Excel macro - column data to multiple rows

You could write a macro to do this, but if it's a one time excercise, it might be easier to do it manually. Turn on filters, one by one for each of the category columns; choose non-empty then hide non-relevant columns, copy and paste the results to a new sheet. Let me know if it requires further explanation (or a macro).

13 more replies
Relevance 88.56%

I have a excel file like below.

Column1 |Column2|Column3|Column4|Column5|Column6|Column7
Row 1 EEEE 1 2 3
Row 2 4 5 6

I want to move data in row 2 (4,5,6) to Row 1 columns 5,6,7.

Could you help me with an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.

your assistance is greatly appreciated.

thank you
 

Answer:Excel Macro to Move data in Rows to column

8 more replies
Relevance 88.56%

Hi guys i'm hoping you can help me i'm a complete noob when it comes to macro's
 

Answer:How do i create a macro in Word that opens the data in Excel?

12 more replies
Relevance 88.56%

Excel noob needs help. I have a spread sheet that changes daily. I would like a macro that will search worksheet named "Schedule Report" column G and create new sheet for each city listed and copy the row to new sheet. file today may have 10 cities and next day may be 20"Don't hit at all if you can help it; don't hit a man if you can possibly avoid it; but if you do hit him, put him to sleep."Theodore Roosevelt New York City, February 17, 1899

Answer:Excel macro help - Create Sheets/Copy Data

re: "file today may have 10 cities and next day may be 20"If the macro runs today, it would create 10 new sheets and copy the data based on today's data.What should happen the next day? Should it just create sheets/copy data for the 10 additional cities or does it also have to copy/change/update data for the original 10 cities?If it has to work with the data from the original cities, how does that need to be a handled? What will have changed? Will it just be additional lines, etc.?

10 more replies
Relevance 88.56%

I am trying to create a macro that searches for a range of dates by prompting the user for a start and end date and then searches a specific column by asking the user to enter the column header title. I then want to copy the data from that row for 4 specific columns called LOB, Profile, FirstName, and LastName and paste it in a new workbook. The remaing data in that row should not be copied. I have limited VB knowledge and looking for somebody to help. Thanks!

Answer:Excel Macro to copy user specified data to new worksheet

re: "I have limited VB knowledge and looking for somebody to help."Does "limited knowledge" mean that you can take some generic code and modify it to fit your specific needs (change search ranges, sheets names, etc.) or are you looking for a complete and working macro?If it's the former, I can throw something together. If it's that latter, I would need some more specific information, like what columns the dates are in, exactly where should the data should be pasted, etc.As it stands now, I'm a little confused by a few of things.1 - Your subject line says "copy user specified data to new worksheet", the text of your post says "paste it in a new workbook" As I'm sure you know, those are very different things and the code required will be very different.2 - You said "by prompting the user for a start and end date and then searches a specific column by asking the user to enter the column header title."Does that mean that you have dates in more than one column and the code will need to know which column to use each time a search is done?3 - Will multiple rows need to be copied for each search or will it be a single row based on a single date that will be found based on the start and end dates. In other words, will there typically be more than one date found between the start and end dates?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 88.56%

I am trying to recreate a macro I had on my old computer (written by someone I used to work with). The macro was made to run on a large excel spreadsheet (a lot of data) and copied all rows with the same value in a certain column and put them in a new tab (and named the tab whatever the value in that column was). Then it applied a subtotal function on each tab (same for each tab) and did some other formatting (same header row for all tabs, hides certain columns on each tab). The original tab retained all of the information, but was subtotaled and formatted the same as the other tabs (although, that isn't crucial). Please help!

Answer:How to create a macro to sort data to new tabs in excel

If you can post a short example of your data, noting which column has the values to be sorted on, perhaps we can help.Please click on the following line and read the instructions found via that link before posting your data.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 88.56%

Hi,

I'm looking to set up a macro to do the following:

Open up a workbook called Works Order Register
Find my Works Order on the register by matching the WOnumber (I8) and Contractor name (C1735) from Works Order workbook.
Once found copy various data e.g cell I1735, I1737 etc across to columns D, E etc on the register against the corresponding row found in the previous step.

I've got parts working ok, but not the whole thing together.

Thanks for help.
 

Answer:Excel Macro to lookup reference and copy data

12 more replies
Relevance 88.56%

Good Morning guys,

This is my first post on Tech Support Forums!

I am not to sure how much help I can be to people here other than building gaming Rigs, so I will keep one eye on that forum to see if I can add value here.

Now, on to my question!

I have assembled an Excel spreadsheet with hundreds of lines of date. (Business Name, Address, Contact Number, Email, Website). We have a CRM system which does not allow me to enter new clients myself, so i have to complete a CRM Request form for each potential client on this list (over 600) The CRM request form is a Word Document with sections for each part of the data on excell.

The problem is, I am too lazy to copy and past all of the lines of data into individual CRM Request Forms (Word Documents)

So in short... correct me if I am wrong. I think I need to create some form of macro to do this for me. (copy data from excell, open up the word document, paste the data in the specific places, then save it, then repeat for the next line of data)

It would be greatly appreciated if you could point me in the right direction on this one people.

Kind Regards,
Anthony
 

Answer:Need Help: Excel data to individual word documents, Macro Maybe??

7 more replies
Relevance 88.56%

Ok, first of all I'm new to your site and WOW! I am incredibly impressed with the wealth of knowledge I've come across while searching through various forums! I've already found all kinds of great macros and ideas that will make my workload a lot easier. That being said, I have a problem with efficiently sorting some data and I hope one of you macro gurus can assist :)I've found several macros that do something similar to what I'm looking for, so hoping to get something a little more specific. Basically, I have some weather equipment that monitors up to 5 things in the air and then transmits data to my computer via radio, and spits out text files that I then copy into excel. It creates one text file for each thing I'm monitoring. The format is:Date (mm:dd:yy:hh:mm:ss), WhatIsMonitored, Unit#, ConcentrationSo for example if I'm using this equipment to monitor the concentration of oxygen in the air over the course of a couple minutes using just one unit (and by unit I mean the machine that is doing the monitoring), it will give me something like:09/01/2010 17:02:55,O2,U_10_O2,20.90000009/01/2010 17:03:09,O2,U_10_O2,20.90000009/01/2010 17:03:25,O2,U_10_O2,20.90000009/01/2010 17:03:40,O2,U_10_O2,20.90000009/01/2010 17:03:55,O2,U_10_O2,20.90000009/01/2010 17:04:10,O2,U_10_O2,20.90000009/01/2010 17:04:25,O2,U_10_O2,20.900000So for in this case it's not too much trouble to just copy the text into Excel, then use the "text to columns" feature to put it in columns.The problem I'... Read more

Answer:excel 2007 macro to sort data to new sheet

After you use the text to columns feature, I assume your data looks like this:
A B C D E
1 09/01/10 17:02:55 O2 U_10_O2.txt 20.9
2 09/01/10 17:03:09 O2 U_11_O2.txt 20.9
3 09/01/10 17:03:25 O2 U_11_O2.txt 20.9
4 09/01/10 17:03:40 O2 U_11_O2.txt 20.9
5 09/01/10 17:03:55 O2 U_12_O2.txt 20.9
6 09/01/10 17:04:10 O2 U_12_O2.txt 20.9
7 09/01/10 17:04:25 O2 U_13_O2.txt 20.9
This will probably need some tweaking, but it might get you started:Sub SplitSheets()
Dim ws As Worksheet
Dim lastDate, srcRw, dstRow As Integer
Dim newdate, wsName As String
'Determine last row with data
lastDate = Range("A" & Rows.Count).End(xlUp).Row
'Loop through data
For srcRw = 1 To lastDate
'Remove slashes from dates
newdate = Month(Cells(srcRw, 1)) _
& "-" & Day(Cells(srcRw, 1)) _
& "-" & Year(Cells(srcRw, 1))
'Build worksheet name
wsName = newdate & " Unit " & Mid(Cells(srcRw, 4), 3, 2)
'Create sheet if it doesn't exist
On Error Resume Next
Set ws = Sheets(wsName)
On Error GoTo 0
If ws Is Nothing Then
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = wsName
End If
'Copy row to next open row in corresponding sheet
dstRow = Sheets(wsName).Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets(1).Cells(srcRw, 1).EntireRow.Copy _
Destination:=Sheets(wsName).Cells(dstRow, 1)
Set ws = Nothing
Next
End SubClick Here Before Posting Data or ... Read more

10 more replies
Relevance 87.74%

HELP PLEASE !!!!

I have attached a sample excel document
Basically each morning I receive a .txt file and I export it to excel.
I have a macro which deletes blank rows and therefore tidies the file up somewhat as the size is humungous!
There will be approximately 1000 brach i.d.'s on a daily basis within my file, but I have provided a few in the sample excel doc.
Within my attachment I have highlighted a specific branch and the information that comes with it. I only need part of the information (therefore many rows can be deleted within each branch i.d.

Please review the attached sample and keep in mind the following:

1. I need to keep the branch i.d. at the top of each section
2. I really only require the information that relates to "outstanding" and the few subsequent rows beneath

Can someone please assist??? I would be very appreciative
 

Answer:Need Help with wriitng Macro/code to rid unwanted data and retain specific data

I think this macro will work for you, though with dates involved, you never know.

BTW, I suggest that next time, Excel related threads should be started in the Business Applications section. You get help there sooner. Maybe better help as well.

Jimmy
 

1 more replies
Relevance 87.74%

I need an Excel macro to print a set number of rows from a spreadsheet based on user input. I have attached a sample input file. Each ten rows represents one label. For each 10 rows of data, I want the macro to prompt the user and ask: "How many labels for 5/4X6X12' GROOVED (the description field for each set of data) do you need? So, even though this item shows a quantity of 16, the user may only need to print 2 sets of this data. If the user answers "2", then I need to print 2 pages each having the 10 rows of data that goes with that description:

Type: Delivery
PO #: 38505 SO #: 121046
Ship to: WORK SITE - LOT 3
Address: LOT 3 DALE SUBD
Item: ZZAT0000030621
Desc: 5/4X6X12' GROOVED
QTY: 16 EA
Created by: Rsmith
Ship Date: 11-SEP-14

Any help on this macro would be greatly appreciated. Thank you.
 

More replies
Relevance 87.74%

Hi,New to VB and would appreciate some help with the following.I am trying to create a sheet where by i can input my capacity/demand on any new day. However, i want to be able to store the historic data and be able to bring it up again by inputting a date for a specific days data that i wish to view.Lets say that A1 is the cell where a date can be inputted.B1 is where i input todays capacityC1 is where i input todays demandD1 is where i input expected WIPWhen i open up the document tomorrow, i would love these fields to be blank ready to be populated. However, i would like to be able to view yesterdays data by inputting yesterdays date in A1. If the date is not a valid date i.e no data was recorded for that day, a message box could pop up saying 'not a valid date'.Can anyone help?

Answer:Excel: Macro that will display data based on a inputted date

I believe that these 2 sets of code will do what you asked for. However, there is one big piece missing. There is nothing here that will add the current date's new data to the historical data.Since there are so many variables involved with inputting the data, I didn't want to waste time writing code that won't do what you want.For example, when do you want the data copied to the historical data - as soon as the last piece of data is entered into D1? Just before the workbook is closed? Just before the workbook is saved? Do you want to click a button to save today's data? etc.Let's say you enter today's data and then decide to search for another day's data. Would you want to be able to pull today's data back again during that same session?Would you want to be able to change today's data assuming it's still "today"?Until it's clear how you want to deal with the new (current day's) data, it's tough to offer any suggestions.As far as your other 2 requirements, try this in a backup copy of your workbook. This code writes data and deletes data, so you don't want to test this your only copy of your workbook since it cannot be undone.Note: This code makes 3 assumptions:1 - The data entry cells you mentioned in your post are correct, i.e. A1:D1.2 - The data entry cells are on Sheet 1.3 - The historical data is stored in Sheet 2 Columns A:D.You will have to modify the code if it doesn't fit your workbook layout.Right click on the sheet tab for Sheet 1.Paste this code into the pane that... Read more

5 more replies
Relevance 87.74%

I am working on making an auto updating spreadsheet. I pull a report every week called the weekly.xlsm and on that workbook i use the tabe Delinq_detail i then filter the data to my specifics. Then i manually copy and paste certain columns to another workbook book1.xlsm on the weekly tab. Is there anyway to have a function or macro auto pull this data from weekly.xlsm and insert into my cells on book1.xlsm weekly tab? If so what would the function or macro look like?

Answer:Excel Macro or Function to pull data from other saved wrkbk

Try using the record macro function in excel. Turn the record on and go through your normal process of copying, pasting, and filtering. Once you are done stop recording the macro. From there you can edit the macro you recorded and see what the code is for the different operations you are preforming and adjust to meet your needs.Hope that helps

2 more replies
Relevance 87.74%

i was hoping if anyone can provide me code for the macro i need . in my case i have to select the data manually from the source sheet. the selected data range needs to be copied from the active worksheet to the destination workbook. the data should go into specific columns in the worksheet of destination workbook. I have very limited knowledge in coding. If it can be done it would reduce the data entry time drastically.

Answer:Excel Macro to copy user selected data to new workbook

I assume it can be done, but not without a lot more detail.VBA code needs to know where the data is coming from and where it is going. If the user selects a range, that makes it easier, but the code would still need to know exactly where the data is going. Workbook name, Sheet name/number, the "specific columns" that you mentioned, etc.If you can supply that detail, we can see what we can do for you. If you are going to post any example data, please click on the following line and read the instructions on how to post data in this forum.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

13 more replies
Relevance 87.74%

HiI have to move a list of people that have a unique number to a new tab. See example:PQR FullName UsName Description two C hart P01 Hwbssr, Lautten ZHeeeddg MtH - 3B Yes No YesP01 Birssher, Wiiyse ZKiiyydW MhH - 3B Yes No YesP01 Metoogan, Ktsren ZMtttEK MkH - 3B Yes No NoP01 GuuttsON DOyrsA ZGbbbRD MwH - 3B No No YesI have a list of about 1400 like this with different PQR identifiers. I need create and name a tab then move only the data relevent to each PQR code to its own tab.

Answer:Use a Excel macro to move data to uniquely named tabs

Check out this thread...http://www.computing.net/answers/of...Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies