Over 1 million tech questions and answers.

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

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

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 on each job when settling up with clients. It will make his life and mine easier if I can get this function to work. Otherwise we are looking through hundreds of lines of parts, using the 'find' command.

Thank you for all help and comments.

RELEVANCY SCORE 200
Preferred Solution: Solved: Excel 2010: Macro or substitute to automatically copy data between workbooks

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

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

Read other 2 answers
RELEVANCY SCORE 108

I am very new to VBA and have a scenario that I could use some assistance with.

At work, I have 300 individual excel files, and I need to copy certain cells from each of those files and paste into a summary file. Since it is extremely time-consuming to do this manually, I was wondering if there was a macro that could open the file, refresh pivot tables (I have formulas in the 300 workbooks which are linked to pivot tables in the summary file), copy cells and paste values in the summary file (in the next available row, not to over-write previous data) and save workbook. Then repeat this process for the rest of the files in the folder. I would prefer not to have to write specific file names, as there are 300 of them and growing.

I am using Excel 2007.

I have attached the Summary file and 2 property files as examples. I need to make sure that the formulas in the Qtr1 & Qtr 2 workbooks (column D) are updated with the pivot tables in the Summary file. Then I need to copy cells B2727 from 'WS' worksheet and paste values to the next blank row in the range P2:R4 in the Summary file, and also copy cells B3131 and paste values to the next blank row in the range S2:U4 in the Summary file. Then save property file and close. Then open next file in the folder and repeat.

Please let me know if you need more info. I appreciate any help or feedback!
 

A:Help with Macro to copy & paste between Excel workbooks

Hi, welcome to the forum.
The Qtr1 & Qtr2 workbooks column D are unclear to me, but the B2727 and B3131 okay.

I put in a vba module with a macro and a funtion the macro Update will ask for confirmation of the Excel sheet and if you answre yes it updates the date.

Nothing with the pivot tables just the two rows into P-R and S-V

test and tell me if this is a beginning of what you need.
 

Read other 1 answers
RELEVANCY SCORE 103.2

HI

I have 3 workbooks - A (Mary), B (John) & C (Master)

Workbook's A & B have the following on worksheet 1

Columns B & C contain cell data
Columns D, E, F & G contain combobox data

All data is in rows 6 -34

Workbook C has the same as above, yet on 3 different worksheets

What I want to be able to do is -

Code a button on Workbook C to copy the data from Workbook's A & B to Workbook C's worksheets "Mary" & "John"
Then have it copy the data from these worksheets to a "master" worksheet contained in workbook C.

In otherwords I want to be able to keep Mary & Johns data on seperate worksheets, yet still have an overall view of all of the data - is this possible ??

Any help would be greatly appreciated
 

A:Copy Data between excel workbooks

Hi
It is most definitely possible. We'd need a lot more information to start helping you but you can do a lot to start yourself off by recording a Macro. Try that and see how far you get and come back to us where the macro is failing or needs customisation and we can provide specific help.
 

Read other 2 answers
RELEVANCY SCORE 99.2

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?
 

A:Solved: Excel Macro Copy Data From Columns

Read other 8 answers
RELEVANCY SCORE 89.6

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.
 

A:EXCEL MACRO- sort, copy different data

Read other 6 answers
RELEVANCY SCORE 88.8

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.
 

A:Excel Macro to lookup reference and copy data

Read other 12 answers
RELEVANCY SCORE 84.8

My first issue is that my users will be entering in item numbers with spaces. These items #'s are the same for my pic files but without spaces.

I''ve tried this....=SUBSTITUTE(D7," ","") - But my issue is I have the code below to look at a cell to grab a picture and when it uses this it does not see the item number in the cell but sees the code - didn't work.

How do I add to the code below to take cell A21 and do the substitute for any spaces.

Sub ShowPicture()

Dim DirPath As String
Dim FileName As String
Dim Pic As StdPicture

DirPath = "\\bssmas01\APPS\MAS90\Images\001"
FileName = Worksheets("CutRequestForm").Range("A21")
FileName = DirPath & "\" & FileName & ".bmp"

If Dir(FileName) <> "" Then
Set Pic = LoadPicture(FileName)
With Worksheets("CutRequestForm").Image1
.Picture = Pic
.Height = Pic.Height / 25.4
.Width = Pic.Width / 25.4
End With
Else
MsgBox "The Picture File was not found -" & vbCrLf & FileName, vbExclamation
End If

End Sub
 

A:Solved: Excel VB/Macro to substitute space with no space

I figured it out!!!
I should of worked harder before I started to ask.

FileName = Trim(Replace(FileName, " ", ""))
 

Read other 1 answers
RELEVANCY SCORE 82

Trying to link two workbooks in Excel 2010. Worked fine n Excel 2007 but can't get it to work in 2010. Anyone else had this problem that can share a solution? Thanks.
Tech Support Guy System Info Utility version 1.0.0.2
OS Version: Microsoft Windows 7 Home Premium, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM) i3 CPU M 380 @ 2.53GHz, Intel64 Family 6 Model 37 Stepping 5
Processor Count: 4
RAM: 5814 Mb
Graphics Card: Intel(R) HD Graphics, -1316 Mb
Hard Drives: C: Total - 595017 MB, Free - 402994 MB;
Motherboard: Acer, Aspire 5742
Antivirus: Norton Internet Security, Updated and Enabled
 

A:Can't link two workbooks in Excel 2010.

If it worked in 2007 it works in 2010.
More information would be helpfull.
 

Read other 1 answers
RELEVANCY SCORE 81.6

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.
 

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

Read other 14 answers
RELEVANCY SCORE 81.6

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

A: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
 

Read other 3 answers
RELEVANCY SCORE 81.2

Hi all,

I would be most grateful if someone could assist me with a workable solution for a problem I have in determining duplicates within two workbooks.

The problem seems fairly simple but I can't seem to find the solution. I'm still getting my head around Excel so please bare with me.

I have two workbooks containing data. For the purposes of this example, let's assume Workbook 1 contains data for August 2012. Workbook 2 contains data for September 2012.

The data logged in each workbook may be company name and/or invoice number, for example.

What I'd like to do, is to determine whether a company name has appeared in both these two workbooks. Or alternatively (a different example being), does an invoice number appear twice in both workbooks.

I'm happy to do this via the FIND function, if the search was possible across both workbooks, however it would be even better if I was able to record the duplicate (or unique) data in a separate worksheet - or colour cells green (for unique) and red (for duplicate).

Does anyone have a solution for something like this?

Many thanks
Brandon
 

A:How to search across multiple workbooks in Excel 2010

Hi,

Welcome to the forum.
It would be simpler if you could supply the workbooks for looking at.
You can redact the info.
 

Read other 1 answers
RELEVANCY SCORE 81.2

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
____________________________
 

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

Read other 7 answers
RELEVANCY SCORE 80.4

Hi - This is a problem occurring at work.

We use Windows XP and Office 2007 (although our managers are on Office 2010, they have also experienced this same issue). We use two monitors, so we can use two programs side by side. One fairly common use is to have two instances of Excel open, one on each monitor. We want to be able to copy a range of cells from one workbook to another. This sometimes works, but more often than not, on the second C&P action, the paste only selects the same range of cells, but pastes nothing.

I'm really hoping this question makes sense.

Can anyone suggest why this is happening? It is confusing in its inconsistency.

I can't think of any other technical information to provide, but if there is something extra that is needed to resolve this, please let me know and I will check when I get back to the office tomorrow morning.

Thanks!
 

A:Excel 2007 Copy & Paste between Workbooks Problem

Read other 11 answers
RELEVANCY SCORE 80.4

I am trying to come up with a macro that once executed will go to a defined name, insert a row, copy what is in row one and then paste the copy in the row that was just inserted.
 

A:Solved: excel macro copy

Read other 7 answers
RELEVANCY SCORE 80

Hi, I have approx 25,000 rows of data relating to parts held in inventory, on a spreadsheet in Excel 2003 SP2 .

One common cell on each line is the Supplier Stock Code and the data in it is duplicated on different rows as the same Supplier Part is held under several different Part ID's on our system, ie,

Cell A2 0-320311-00
Cell A3 0-320311-00
Cell A4 0-320311-00
Cell A5 0-330317-29
Cell A6 0-330317-29
Cell A7 0-330317-29
Cell A8 03400BK08345
Cell A9 03400BK08345
Cell A10 03400BK08345
Cell A11 03400BK08345

I have sorted the data into Supplier Stock Code order and need to find a way to automatically insert a blank row (or two) after each change, ie, in the example above it would be between rows 4 & 5, and 7 & 8.

Does anyone know of a Macro which will do this?

Many thanks,

MarG38
 

A:Solved: Excel Macro to automatically insert rows

First things first -- welcome to the board.

2nd thing:

"insert a blank row (or two) after each change"

Hmm: for what purpose? The "official" line is "DON'T do that, it's VERY bad design". K?

So what I did was drag your last entry down to r25000, to generate 03400BK08346,
03400BK08347, and so on. "Test data".

Then I ran the code at the bottom of this post. It ground away for 12 minutes (make coffee, visit bathroom, yada yada) & then fell over on account of "ran out of worksheet" (bear in mind I had c.25K unique values, you have far fewer).

Last (but most definitely not least) thing: back up your work before trying the code.

HTH

Sub test()
Application.ScreenUpdating = False
LastVal = Range("A" & Rows.Count).End(xlUp)

Do Until LV2 = 1
x = Range("A" & Rows.Count).End(xlUp).Row
x = Range("A" & x).End(xlUp).Row

If Cells(x, 1) = LastVal Then
LV2 = 1
Else
y = Application.Match(Cells(x, 1), Columns(1), 0)
z = WorksheetFunction.CountIf(Columns(1), Cells(x, 1))
Cells(y + z, 1).Resize(2).EntireRow.Insert
End If
Loop

Application.ScreenUpdating = True
End Sub
 

Read other 2 answers
RELEVANCY SCORE 80

Can anyone help?

I've been asked to look at an excel file at work and they want a button that when clicked will put a new row at the bottom of the current records (i.e. the next empty row) and autopopulates the first cell in the row with a unique number (preferably sequential) so this can be used as an identifier for that particular record.

any help would be greatly appreciated!
 

A:Solved: excel macro new record that is automatically numbered

Read other 16 answers
RELEVANCY SCORE 79.6

Hi,

I have been looking at your threads and see that you have covered a similar topic a few years back.

I am looking for away to automatically back-up excel 2010 workbooks into a specific folder, so that when I complete filling in the information sheets for my clients and click on the 'save all' box on the spread-sheet, all the back-up excel workbooks can be found in the same folder. I had some luck with a VBA macro but it keeps making a new folder every time I save. If I add the name of the folder, that I want the workbooks to save in, it doesn't work at all. Any ideas? Is there a shorter route than the VBA macro?

Thanks.
Beth

Tech Support Guy System Info Utility version 1.0.0.4
OS Version: Microsoft Windows 7 Professional, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz, Intel64 Family 6 Model 42 Stepping 7
Processor Count: 4
RAM: 8080 Mb
Graphics Card: Intel(R) HD Graphics, -1988 Mb
Hard Drives: C: 232 GB (168 GB Free);
Motherboard: Hewlett-Packard, 1495
Antivirus: Sophos Anti-Virus, Enabled and Updated
 

Read other answers
RELEVANCY SCORE 79.2

Good morning. I consider myself a novice when it comes to Macros. I have written several simple macros using the macro recorder and VBA. My current dilemma is I have a template (worksheet #2) in which I wish to copy a range of rows several times within the same worksheet with a blank row between each copied range. The number of copies is dependent on the number of rows of data (cost centers)within worksheet #1.

I have seen some macro language that should accomplish the copy range, however, the next step is what throws me. For each copy of the template range, I'd like to insert the data (cost center) that comprises the rows on worksheet #1.

Essentially I want to create a copy of the template range for every cost center listed on worksheet #1. See the attached example.

Once the copies are made and the cost centers are inserted, my database will refresh the worksheet #2 and bring forth the data for each cost center. Macro language is not needed for the last step.

Thanks in advance for your assistance. Please let me know if the inquiry is not clear at any point.
 

A:Solved: Copy & Insert Macro - Excel

Read other 10 answers
RELEVANCY SCORE 79.2

Hello,

I know nothing about programing an excel Macro but I need help createing a macro to do the following:

file 1
col a ffffcol b
1 ffffffffff48
2ffffffffff 26
3ffffffffff 53
4 ffffffffff8

File 2
col c fffffcol d
1
1
2
3
3
3
4

I want to fill the rows in col d in such a way that 1 coresdponds to 48 and so forth.

Also, the file has over 7000 rows of information so doing it by hand is not a viable option
 

A:Solved: Excel Copy paste macro

Read other 6 answers
RELEVANCY SCORE 79.2

Hi

I'm really new to writing code and I'm trying to develop some stuff in Excel 2007.

I want to record a macro for a command button that when clicked, will copy the data from Cell A6, and past it with formatting and everything into whatever cell I selected.

I can get it to copy and paste, but only into a certain cell.

Help required!

Thanks

C
 

A:Solved: Excel Copy and Paste Macro

MacShand, welcome to the forum.

Try This:
Code:

Range("A6").Copy
ActiveCell.PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False

What the code does is it copies the contents in cell A6 to whatever cell you have selected at the present time. So, make sure before you run the code the correct cell is selected or you may copy over needed data.
 

Read other 3 answers
RELEVANCY SCORE 79.2

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
 

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

Read other 1 answers
RELEVANCY SCORE 78.4

I need to create a macro in Excel that will look for a certain text within a column (Column A), Copy the data in another column (Column B), and paste the data in another column (Column C).

For Example....

Column A
ABC1
ABC1
ABC1
ABC1
ABC1
DEF2
DEF2
DEF2

Column B
1000
1500
78
562
235
89
456
258

So, I would like to copy all the data in Column B that correspond with "ABC1" in column A, and paste that data in Column C.
 

A:Solved: Excel Macro Search and Copy Rows

Read other 16 answers
RELEVANCY SCORE 78.4

I have been reading up on creating log files with Excel Macros.

I am trying to figure out if it is possible to have a Macro output the contents of the currently selected Cell to a text file?
 

A:Solved: Excel Macro Copy Cell to log file.

Read other 16 answers
RELEVANCY SCORE 78.4

Hi. I am trying to run a macro to copy items on 1 sheet and have it paste them on the 2nd and 3rd sheet. I am trying but can't quite seem to get the excel code to do what I want. I can't post a copy of it because I accidentally deleted it.

Can anyone help me?
 

A:Solved: Excel: Copy/Paste Macro Code Help

Read other 9 answers
RELEVANCY SCORE 78.4

Hey all,

Here's my situation, any help is greatly appreciated:

I have a list of 13 items in one column. A couple of the cells will be hidden by the macro, but then what I need the macro to do is copy the first 10 unhidden items onto the clipboard.

What row(s) will be hidden will vary from week-to-week. If I were to simply have the macro copy a cell range, there would be a chance of it copying a hidden cell. I believe what I need is a way to have the macro do a count of how many unhidden cells it has selected, and stop when it reaches 10.

Forgive my ignorance but I am literally just picking up Visual Basic as of this week.

EDIT: Just to clarify, I have the hiding of the cells taken care of. I just need some help figuring how to copy the top 10 cells that aren't hidden.
EDIT 2: Found that I could delete the hidden cells. Worked perfectly.
 

Read other answers
RELEVANCY SCORE 78.4

Hello,

As per the title I want to compare the data between two excel files not work sheets in the same file (if that males a difference).

So I want to compare the old data with the new data and highlight the differences in red or any color on the new sheet.

Thanks!
 

A:Compare data in two excel WORKBOOKS and highlight difference

Read other 12 answers
RELEVANCY SCORE 78.4

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
 

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

Read other 6 answers
RELEVANCY SCORE 78.4

I have a macro I wrote in excel 2003 (by recording small steps as a chain of macros), I just upgraded to a new machine and Office 2010.

When I try and run the macro it comes back with error message "Run time error 438, Object doesn't support this property or method."

When I run debug the code line that is flagged is:

Selection.End(X1 to Left).Select

Are there any commands or options which have changed between 2003 and 2010 and is there a conversion list anywhere?

George intermediate user running Windows 7 64bit, Office 2010
 

A:Solved: Excel macro 2003 to 2010 error 438

Read other 16 answers
RELEVANCY SCORE 77.6

Is there a way to use a combination of SUMIF and VLOOKUP on more than one sheet?
For example if I want to feed some data into another workbook is there any way to do this from multiple sheets to one cell/column in a third and separate workbook?
Ie:
To be more clear - I want data to feed into column C or d or f, etc. of sheet 1 from both sheets 2 and 3. Sheet one is in another workbook than 2 and 3. Additionally, I want this data to be from the first to the last day of a month. So that my data from different biweekly paysheets is automatically entered into the monthly report sheet (sheet 1).

 

A:Feeding data from separate workbooks in excel - within a date range

Hi, welcome to the Forums.

I guess your post remained ignored so long because noone really understands the question.
I certainly don't. Can you tell it in other words? You should have an example workbook attached as well.

Attaching files to your post can be done with the Manage Attachments option, below the post editor window.

Jimmy
 

Read other 1 answers
RELEVANCY SCORE 77.6

Hi,
I was wondering if it is possible to trigger a macro when an particular custom tab is clicked.
This is part of my XLM code from the Excel file:
Code:

<!-- Add Custom group to the Home tab in the ribbon with one button-->
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon startFromScratch="false">
<tabs>
<[COLOR="Red"]tab id="customTab" label="Inzet Planner" insertBeforeMso="TabHome[/COLOR]">
<group id="customGroup1" label="Planner Maand Selecties" >
<button id="cG1B1" label="Huidige Maand" size="large" onAction="BtnOnActionCall" imageMso="ArrangeByAppointmentStart"/>
etc ....

It all works perfectly for the buttons, that's not the issue.
What I am looking for is that if CustomGroup1 is pressed a macro is triggered. An then of course I have a series of groups and that can be a different macro or no macro at all.
I tried sendKeys and so but that does not work well.
Like the Button Id has an onAction function, is there something similar for when a Group is selected?

Thanks for any answers
 

A:Solved: Excel 2010 trigger macro when a ribbon tab is selected

Read other 15 answers
RELEVANCY SCORE 77.6

HI,

I have a huge product listing that i need to find and replace on a mass scale. I have one spread sheet that has 3 columns (see example below)
Column A****************Column B**********************Column C
1************************ M2123*************************M3455
2************************M3455*************************M3455
3************************M3433*************************M3455
4************************M6543*************************M3433
5************************M8793*************************M3433
6************************M6543*************************M7832
7************************M7832*************************M7832
8************************M4893*************************M7832
9************************M3938*************************M2123
10**********************M4837*************************M2123
*********************************************************M2123
*********************************************************M2123
*********************************************************M4837

In essence Column A is the id for column B (A=B), column C is longer than column a and b, because it contains duplicate (M)numbers. I would like to use the defined id in column a to replace the (M)numbers in columns c

So the spread sheet with the end result would look like this

Column A****************Column B**********************Column C
1************************ M2123*************************2
2************************M3455*************************2
3************************M3... Read more

A:Solved: Macro Multiple Find and Replace in Excel 2010

Read other 11 answers
RELEVANCY SCORE 77.6

I'm trying to create a macro that will sort on numerous fields. The macro will be stored in my "personal" file as I need to use it in a new report every month. I think I know what the probelm is. It looks like it hard coded the name of the worksheet where I originally created it. I need the highlighted piece to be flexible depending on whatever file is open.

Sample portion of macro starting at the beginning...

ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Add Key:= _
Range("O2:O15173"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Add Key:= _
Range("D215173"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Add Key:= _
Range("E2:E15173"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
 

A:Solved: Sort Macro for multiple files- Excel 2010

Read other 6 answers
RELEVANCY SCORE 77.2

I have recorded a macro to autofil another xcel workbook this works fine and opens the workbook and completes all the fields. My problem is I want to use the same macro in workbooks with different names?? ie in the code it refers to the workbook "blankquote" and I want it to run from any workbook that I save from the original "blankquote"
Is there a code I can use ie "current workbook" ???
Here's hoping someone can help?
 

A:Solved: macro using in different workbooks

Read other 16 answers
RELEVANCY SCORE 76.8

Hello Everyone,

First time user of the forum here and it does seem everyone is very helpful! I did a search and could not find search macro for a list of values so I'm adding a new post. My apologies if this was answered before but hopefully someone can point me in the right direction.

Here's what I have:

I have 3 worksheets:
1. List of Search Criteria (List of 100 or so countries in column A)
2. Huge Data Set (Sales Data; column K, L, or M will contain country name)
3. Blank Output sheet

My goal is create a search button that will look in the Huge Data Set for sales transactions occuring in the list of countries specified on Sheet #1. The country info could be in any of the 3 rows (K, L, M) on Sheet #2. I would then like any row on the Huge Data Set with a matching country to be copied to the 3rd worksheet.

I hope my explanation makes sense.

Any help?

Simon
 

A:Solved: Excel Macro - Search List of Values, Copy, and Paste

Read other 13 answers
RELEVANCY SCORE 76.8

I have a spreadsheet that is used by a number of users and has a macro built into it to collect updates from several other sheets. I want to add to the macro so that once it has been updated and saved the macro then saves another version of the sheet in a seperate folder with the date and time it was saved as part of the file name.

I have tried

ChDir"H:\Resourcing Teams\Placements\Weekly activity\Archive"
ActiveWorkbook.SaveAs Filename:= _
"H:\ResourcingTeams\Placements\Weekly activity\Archive\" & Now.Day & Now.Month &Now.Year &" .xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

But get errors

Any suggestions greatly appreciated
 

A:Solved: Excel Macro to save a copy of the file in new location with the date

Read other 9 answers
RELEVANCY SCORE 76.8

Hello all. Not a regular user of Excel; but do need help in creating something that would be useful to me and a few others at work. I suppose the best way of explaining what I'm after is by giving an example.

I have a directory C:\Users\Tekko\Desktop\Maintenance Project\Cape Nelson
In Cape Nelson are a number of folders named alpha beta charlie delta echo and foxtrot and so on.

I would like to have an excel template in "Cape Nelson" with a macro that when activated names the file as whatever folder name might be in say cell A1 and whatever ever date might be in cell B1. Eg charlie_15-mar-2013.xls
This then is saved in the relevant folder. So in the end I would end up with
C:\Users\Tekko\Desktop\Maintenance Project\Cape Nelson\charlie\charlie_15-mar-2013.xls

Also the macro script would ensure that the macro was disabled in the saved file.

Hoping this is achievable and look forward to replies.
 

A:Solved: Excel 2010 - Macro to name and save file to a specific folder

Read other 16 answers
RELEVANCY SCORE 76.8

Here is part of my code where it errors out in 2013 for the sort .apply but not 2010 and can't figure out why. Help appreciated. I have recorded same in 2013 with same results.
Code:

Application.DisplayAlerts = False
Workbooks.Open Filename:="C:\Temp Data\mydata.csv"
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A:$AM").AutoFilter Field:=33, Criteria1:="<>"
Cells.Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Range("A1").Select
ActiveSheet.Paste
Sheets("Mobility").Select
ActiveSheet.Range("$A:$AM").AutoFilter Field:=33, Criteria1:="="
Application.CutCopyMode = False
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Range("A1").Select
ActiveSheet.Paste
Range("AC2").Select
Application.CutCopyMode = False
Sheets("Sheet2").Select
Cells.Select
Range("U1").Activate
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range( _
"Q:Q"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").Sort
.SetRange Range("A:AM")
.Header = xlYes
.MatchCase = F... Read more

A:Solved: Excel 2013 Sort Macro errors out at .Apply but not in 2010

Read other 13 answers
RELEVANCY SCORE 76.4

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
 

A:Solved: Auto Fill Data in Excel Macro

Read other 8 answers
RELEVANCY SCORE 76.4

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
 

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

Read other 12 answers
RELEVANCY SCORE 76.4

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

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

Read other 8 answers
RELEVANCY SCORE 76.4

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
 

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

Read other 6 answers
RELEVANCY SCORE 76.4

Hi,

Excel 2003

I need to write a macro to do the following things. Any help is greatly appreciated and Thanks in advance!

I have attached a sample workbook.

Range(A2:AJ65536) will have data. Column J has names
this is what I require.

1. Count the used range in column A
2. Divide the used range in column A by the number of used cells in column J. (excluding the header (Names)
3. Round the value. e:g 22/3 = 7.33 I don't want it to round it to 7. but round it to 8.
( Now based on the above number which is 8. This mean the data has been equally divided. )
4. Now assign/add the names in column D based on this number(8) and column J, so J2 will go into d2:d9, J3 will go into d10:d17 and so on.
5. After the names have been assigned I want excel to do calculations (f9) because alot of the cells in this workbook have formulas that need to be updated after assigning the names.
6. once excel has completed calculations, create workbooks based on values in column D and J. (copy range(A2:G9) open workbook called "sample.xls" select column A2 paste-special , values only. Save As the name in J2 So first workbook will be You.xls. Now copy A10:G17 open sample.xls, select a2, paste-special, values only. Save As name in j3, Me.xls ...and so on.

A few things to remember,
range in column A will vary on a daily basis.
number of names will vary on a daily basis.

Thanks once again for your help in advance
 

A:Solved: Macro to create several workbooks

Read other 16 answers
RELEVANCY SCORE 76

Hello everyone,

I am completely stumped and I hope to find some help from someone here... I am trying to format an excel spreadsheet that has over 10k records is a very strange format. Please see attachment...

I have created the logic to grab the numbers to the left of the "carrot" in cell E3 and replicate that number in cells B3 B4 B5 B6 B7 B8 B9 B10. That process is replicated to pull the numbers to the left of the "carrot" in cell E13 and replicate that number in cells B13 B14 B15 B16 B17 B18 B19 B20...And so on and so fourth. The problem I am stumped on is how do i replicate this process all the way down the spreadsheet?

One FYI is that each record "bock" is not always (as in the example spreadsheet) 10 lines it can be 11/12/13 or more lines. Basically the record starts at the letters "MSH" (see A1) So I the logic I am thinking of is to push the code in red in the "E" cells to the "B" column after the "EVN" (in cell A2) and to stop before the "MSH" (in cell A11) and continue that process down the spread sheet.

I hope this is not too confusing but I have been banging my head on the wall for hours and just cant figure it out. Thank you all for your help
 

A:Solved: Very hard macro in Excel (copy part of one cell to several areas with logic)

Read other 8 answers
RELEVANCY SCORE 76

Hi all,

I'm having trouble writing a macro that'll help me automate this process. In the attached, I have a list of countries in the Countries tab, and 10 line items in the Data Set tab. I want to look for each of the countries in columns K to M in the Data Set tab, and for each line that hits, I want the macro to copy that line and paste it in the Output Sheet tab. Then the macro should go down the list of countries and repeat this process until it finishes looking for the last country in the Countries tab.

I'm thinking some kind of For loop is required, but any help would be greatly appreciated! Thanks!
 

A:Solved: Urgent: Excel Macro - Search List of Values, Copy, and Paste

Read other 9 answers
RELEVANCY SCORE 76

I am trying to create a macro that will select the text contents of a cell andcopy it into another cell. I know recording a macro isn't the best way to go,but I'm still pretty fresh in coding VBA.

When I record a macro on the following set of data (shown below) I cancreate a row above the first row containing "CASH" and copy the word"CASH" into the cell above it.

(sample data)

CASH 83 521.01
TRES.CARD 11 54.11
CREDIT2 0 0
HELP 0 0
SUBTOTAL 0 0

(recorded macro)

ActiveCell.Select
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "CASH"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveSheet.Paste

If I run the macro on the row containing "Tres.Card" it willreplace &#8220;TRES.CARD&#8221; with "CASH".

Essentially I am trying the make two rows for each of the existing rows. Iwould add "#" to the label in column 1 on the first line and add"$" to that label on the second line. The first line would thencontain the data from column B and the second line would contain the data fromcolumn C. So that the end result would look like:

(Example of end result)

CASH # 83
CASH $ 521.01
TRES.CARD # 11
TRES.CARD $ 54.11
CREDIT2 # 0
CREDIT2 $ 0
HELP # 0
HELP $ 0
SUBTOTAL # 0
SUBTOTAL $ 0

If anyone has any suggestions of the best way to do this that I'm not thinkingabout or aware of, I would appreciate it.

Otherwise, does ... Read more

A:Solved: Creating an Excel macro to select text content of a cell and copy it into ano

Read other 9 answers
RELEVANCY SCORE 75.6

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?
 

A: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
 

Read other 3 answers