Computer Support Forum

Macro: Email Range/Selection to group from Excel

Question: Macro: Email Range/Selection to group from Excel

Hello,

My first post, sorry to be a leech right off!

I'm trying to automate some of my day to day emails. I'd like to get a macro set up that will send an email from Excel and paste the range I select in to the body of the email. Would be great if it would send to a list of addresses listed on the page as well.

Using the attached example, I'd like the macro to take whatever range I highlight on the table, copy it to the body of an email and send it to the group of emails listed on the right side.

I'm learning excel, this would help me a lot! I'll be editing this to fit a variety of other tasks. I hope it can be flexible. Thanks in advance.

Chris

Relevance 100%
Preferred Solution: Macro: Email Range/Selection to group from Excel

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: Macro: Email Range/Selection to group from Excel

http://www.rondebruin.nl/mail/folder3/mail4.htm

I have done the looping attached.

2 more replies
Relevance 77.72%

Hello,
I have a macro which performs a series of tasks on a document I receive from another company. It works fine, but I want to add in a sort order.

The file can have differing numbers of rows in it, so I figured the easiest thing was to use a selection range that would always be higher than the maximum number of rows (in this case 5000 rows).
However, when I sort, I get a whole bunch of empty rows ahead of my data.

Another point is that it's not the tidiest file and can have stray cells with fill colours or borders on them and if you CTRL+END the last cell can be thousands of cells away from the data.

So my problem is twofold:
1) How do I reset the last cell within my macro?
2) How do I limit the selection range not to 5000 cells, but to the proper cell range that contains the appropriate data?

Here's my macro as it stands:

Sub PrepareDICO()
ActiveWindow.FreezePanes = False
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
Cells.Select
Range("A1").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="_", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("D").S... Read more

Answer:Excel macro selection range

Hi Gram.

Re: "the easiest thing was to use a selection range that would always be higher than the maximum number of rows (in this case 5000 rows)."

Can you ID a column in which the last used row = number of rows you need to work with?

For example:

x = Range("A65536").End(xlUp).Row
ActiveSheet.Range(Cells(1, 1), Cells(x, 5)).Select

First line figures last used row in column A ; second line selects from A1, down x rows & across 5 columns.
 

2 more replies
Relevance 86.1%

Please Help - ASAP - Thank you in Advance.
(I did also post this under Business Applications, before realizing it should probalby go here - sorry about that)

I'm just STUCK on this one last bit of my code to finish off this macro for a meeting today

I am Copying a Range of data, from a specific sheet on about 36 Workbooks
I need to paste only certain columns from that copied info, into a new worksheet.

Maybe I'm making this sound too difficult... at this point all I need is:

Columns A-E, K, P, S-U, W, AB-AC, AE-AH, AJ-AK, from the "TargetData" (Defined Range) on Customer Targets worksheet of Test1, to Paste into Columns A-S of the Build Worksheet

This should be easy right? I'm having a mind block...
 

Answer:Excel Macro - Move Specific Columns from Range to New Range - ASAP If Possible

There have been a bunch of views, but not replys or suggestions. Am I not including the right information? Is this not posted in the right place? Can someone please help me?
 

2 more replies
Relevance 86.1%

Please Help - ASAP - Thank you in Advance.

I'm just STUCK on this one last bit of my code to finish off this macro.

I am Copying a Range of data, from a specific sheet on about 36 Workbooks
I need to paste only certain columns from that copied info, into a new worksheet.

I think I've attached a sample of the code, and some test workbooks I've been using.

Any advise would be helpful. I know this should be easier than I'm probably making it...
 

Answer:Excel Macro - Move Specific Columns from Range to New Range

16 more replies
Relevance 81.18%

Having difficulty with the following. I'm thinking it should be simple, but am over-complicating it.

Basically I have a range of data(numbers) that could be any numbers of columns wide and any number of rows deep. There would be no data to the right or below this data and the data normally starts in B10, but depending on the user could be B9 or B11. There are no blank cells in the data, it will be some number or zero.

What I want the code to do:
evaluate the data and conditionally format each cell based on the cell to its immediate right for the whole range of rows and column - 1 (no sense in evaluating last column against blanks). Example: if cell B10 < C10, highlight in light green.

I also want to produce a total at the bottom of each column that would conditionally format the lowest total(s) in yellow

And last, I want a countif total at the bottom of the column I specify (by placing any data in row 1 of that column) that will give me a total of all cells in the column (of data) that is not zero ("<>0") [it has to skip the "totals" row.

I've been racking my brain on this starting with recorded macro and then adding snippets I see in many other responses, and am completely butchering it.

Any help would be greatly appreciated.
 

Answer:Excel Macros - Dynamic Range Selection

9 more replies
Relevance 79.54%

Hello, I'm just joined and am trying to create a macro that will automatically email an entire workbook to a list of individuals. I also want the macro to copy and paste a range of cells within a specific sheet in the workbook and paste it into the body of the email. I am able to email the workbook but can't figure out the second part. Any help would be appreciated.

I am using Excell 2007 and LotusNotes 8.5.1
 

Answer:Macro to email centain range in body of email as well as the whole workbook

Hi, welcome to the forum.
There are several posts where I added some simple tips and tricks.
Do you have the macro you use now to mail the whole workbook?
If I see how you have done this then maybe it's a cinch to add the extra info
You may also post a workbook with fictious data if you wish
 

1 more replies
Relevance 77.9%

Hi there guys

Looking for help in excel 2007 for the macro part.

Basically in sheet1(BOM), we have configurations which our customer order to us (in the form of CP1,CP2..CPn). In Sheet2 (CP) details about CP are mentioned, basically each CP has list of items under it. Typical order from customer includes combination i.e. CP1&CP2 or could be CP2&CP3. What I want to do is based on the order in BOM sheet, need to search the details of CP's in sheet2 (CP) & copy the rows belong to that particular CP in the BOM sheet. So that I can make the BOM list for that particular order. Each CP has variable number of rows. Appreciate your help in making macro for this.

Rgds, MintC
 

Answer:Excel Macro to copy multiple rows based on selection

11 more replies
Relevance 77.49%

Need help with a macro for a tool I'm developing for work. What I'm stuck on is something to remove inserted lines in a specific range. See attachment.

I've got buttons within column I8:I14 that will copy B2:I2 and instert below the button,shifting the content down. I need an option for the users to remove these lines somehow. I was thinking of having checkboxes within the inserted lines in column B so the users can select which rows they want deleted and then can click the "Delete Checked" button in I7 to remove. My attempts at getting checkboxes to copy over have failed and I'm not sure what else I can do.
 

Answer:Need help with an excel macro to delete row within a set range

10 more replies
Relevance 77.49%

I need a macro that will compare 2 lists in Excel and pull out any values that are identical in two columns. I got the macro in this post from microsoft's website, but the problem is, I think it only goes from A1:A100 while I need it to go from A1:A800000 I tried just changing the A10 and A100 to A800000, but I got an error 6. Can anyone help me change what needs changed?
Sub DelDups_TwoLists()Dim iListCount As IntegerDim iCtr As Integer' Turn off screen updating to speed up macro.Application.ScreenUpdating = False' Get count of records to search through (list that will be deleted).iListCount = Sheets("sheet2").Range("A1:A100").Rows.Count' Loop through the "master" list.For Each x In Sheets("Sheet1").Range("A1:A10") ' Loop through all records in the second list. For iCtr = 1 To iListCount ' Do comparison of next record. ' To specify a different column, change 1 to the column number. If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then ' If match is true then delete row. Sheets("Sheet2").Cells(iCtr, 1).Delete xlShiftUp ' Increment counter to account for deleted row. iCtr = iCtr + 1 End If Next iCtrNextApplication.ScreenUpdating = TrueMsgBox "Done!"End Sub

EDIT: I posted this and I notice that the forum did some formatting. I found the macro at http://support.microsoft.com/kb/291320 It is the second macro on the page.
 

Answer:Excel Macro, changing range

Well here's the problem.

800,000 cells is a lot of cells.
The Microsoft code isn't build to deal with this many, with good reason, as on making it large enough Excel crashes (at least on my laptop).

How many rows of data do you actual have?
And can I also ask how many columns?
Hopefully nothing like 800,000 records, so reduce the amount of cells to count.
 

2 more replies
Relevance 77.49%

Hiya,
I have macros in an excel sheet that can sort by date or by name

Range("A12:v250").Select
Range("A250").Activate
Selection.Sort Key1:=Range("b12"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A12").Select
End Sub

however, after adding lots of new rows it now has an error...(from 'selection' to 'bottom' is highlighted yellow)

Private Sub CommandButton1_Click()
Range("A12:v250").Select
Range("A250").Activate
Selection.Sort Key1:=Range("g12"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A12").Select

End Sub

i need to change this to sort 4000 rows. i have tried editing macro and changing any appearances of 250 to 4000. the error came back up and i changed 250 to 4000 in the debug thing but to no avail.

any help in doing this is much appreciated.
 

Answer:Editing Macro Range for Excel

not sure - the thing i love about the macros is - you dont even have to know what your doing exactly... Record a new macro as you do it yourself, then edit the macro to be more dynamic..

i dont know office macros at all (but i know vb well) but I did that and was able to do what i needed..
 

3 more replies
Relevance 77.08%

Hello everyone,

I'm trying to create a macro that will run one set of commands if someone changes a drop down menu from YES to NO, and run a different set of commands if the drop down menu is changed back from NO to YES. I've found macros that will run when a cell is changed, but I'm not sure how to write an if statement on the macro, that will run it one way or the other, based on the cells value.

The option defaults to YES. If they change it to NO, I want it to unhide the rows and enable overwriting of the unhidden cells. If they change it back to YES, I want it to automatically hide the rows and put the formulas back in. Here is the file.

Any help is greatly appreciated!
 

More replies
Relevance 76.67%

I'm really hoping someone can help me here.I've recorded a macro (code below). The macro basically copies a formatted range and pastes it into the current cell where I'm located on the 'Wireframes' sheet. I want the marco to work on any sheet even if i create a new sheet. I'm guessing I want to take the destination out of the code but my limited knowledge can't work it out in order for the macro to continue working.Thanks KieranSub Header_One_Col()'' Header_One_Col Macro' Macro recorded 02/08/2010 by WILLIAKI'' Sheets("Headers & Banners").Select Range("B3:P10").Select Selection.Copy Sheets("Wireframe").Select ActiveSheet.PasteEnd Sub

Answer:Excel Macro to paste range to any worksheet

Hi,This code will copy the range in Headers & Banners to wherever your active cell is and whatever worksheet it is on:Sub Header_One_Col()
Worksheets("Headers & Banners").Range("B3:P10").Copy _
Destination:=ActiveCell
End SubThis is all one line - the underscore with a space before it is a line continuation character.Note that in Visual Basic it is normally not necessary to Select a cell or other object before acting on it. The macro recorder records these 'selections', but they are not required.As the only 'fixed' worksheet is Banners & Headings you probably want to store the code with that sheet. Right-click the tab name "Banners & Headings" and select 'View Code'Paste the code into the Visual Basic window.You could then attach the macro to a button on a toolbar to make it easily accessible:Right-click a toolbar and select 'Customize'From the 'Commands' tab select 'Macros' in the left pane, and then drag the Custom button icon in the right pane to the toolbar.Right click the new icon, select 'Text only in Menus' and then select 'Assign Macro' and select your macro from the list. Click OK and then close.Now wherever you are, clicking the button will run the macro and copy and paste that range of cells, starting at the selected (active) cell.Regards

4 more replies
Relevance 76.67%

I am looking for an Excel VBA macro that will select and clear the contents of the named range box on the top left of the Excel window. I have found something online previously, but I have since lost the code and am not able to relocate the code.The advantage of this macro is that I do not have to type alt+I+N+D in order to define a named range. Instead I can type a macro shortcut command and then type my desired named range name for the cells I have highlighted.

Answer:Excel Macro Named Range Shortcut

re: "I am looking for an Excel VBA macro..."Good luck with your search.If we can be of any assistance, feel free to let us know.

2 more replies
Relevance 76.67%

I am attempting to create a dynamic range based off the "Payroll" chart data so that if personnel are added, they are included in the macros powered updates. Currently, I am simply updating the individual ranges, but I am looking for a more long-term solution.

I have attempted to use the: RangeObject.CurrentRegion
Which I had changed to: Range("A3").CurrentRegion.Select
This simply selected from A3:S154. Which is not the range I wanted (A3:S82)​I have tried a few other formulas with no real luck and am attempting to not have to create individual sheets instead of a summary sheet.
I can post my current macro if needed, but it does not have any of the CurrentRegion selection in it. It is simply a range function based argument


 

More replies
Relevance 76.67%

Hi,I just want to know if its possible to do a macro wherein I can select the dates to be read in my report. For instance, I have a Table (below) where column A is the Date, column B is the name of Agents, Column C is the Section and the Column D is the Error Category. A window will pop-up where in I can select the date range the the macro will read.Usually I have a monthly report and I keep on sorting and filtering the Table if my boss is asking for a weekly report. The report usually are:How many errors does each Agents received on this particular dates?What section has most errors in this particular dates?What are the most common errors in this particular dates?What are the most common error of each agents in this particualr dates?What are the most common errors of each section in this particular dates?Date Name Section Error Category21-Nov Peter Delta Name21-Nov Jack Alpha Age21-Nov Mike Bravo Address21-Nov Cris Alpha Name21-Nov Sam Charlie Name21-Nov Noah Charlie Name21-Nov Jack Charlie Name25-Nov Mike Charlie Comment25-Nov Cris Delta Comment25-Nov Peter Alpha Comment25-Nov Jack Bravo Comment25-Nov Mike Alpha Name25-Nov Cris Delta Address29-Nov Jack Alpha Address29-Nov Mike Bravo Age29-Nov Cris Alpha A... Read more

Answer:Date range Count using macro in Excel

re: "I just want to know if its possible to do a macro wherein I can select the dates to be read in my report."Yes, it's possible.Posting Tip: Before posting Data or VBA Code, read this How-To.

5 more replies
Relevance 76.67%

Hi,
I'm relatively new to VBA, so excuse me if this is a dumb question. I have a workbook with a tab that users enter different variables into. One cell is meant to input a range that later on will be used to perform selections on other tabs. I can't seem to set my range to the text in the input cell. An abbreviated form of the macro I'm trying to use is this:

Sub Macro1()
Dim RetrieveRange As range

Set RetrieveRange = range(Worksheets("Admin").range("B1").Text)

MsgBox RetrieveRange.Address

End Sub

I keep getting this error: "Run-time error '1004': Method 'Range' of object '_Global' failed"

Can anyone help?

Thanks!
 

Answer:Excel Macro - Set Range to Text in Cell

I pasted your code as a macro, created an Admin tab, and entered a valid range (A1:A2) and it worked for me. I entered an invalid range ('before') and got the error.
 

3 more replies
Relevance 75.85%

I'm trying to build a macro for an excel spreadsheet that I've built. I need different headers for 3 of the 5 pages. To accomplish this, I've grouped rows in order to be able to hide/unhide rows 2-6, 12-16 and 18-19 as need be. It works out great when I do this manually, but when I create the macro the macro fails to recognize when I use my mouse to press the + or - button which hides or unhides a group. I'm not sure how to solve this? Below is the code that appears when I look at the macro via VBA editor. I'm new to this and this is my first post so go easy on me! Thanks!!

Sub Print_Worksheet_Shortcut()
'
' Print_Worksheet_Shortcut Macro
' This macro is a shortcut which enables the worksheet to be printed with the proper headers that the City of Sacramento requires.
'
' Keyboard Shortcut: Ctrl+Shift+H
'
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
ActiveWindow.SelectedSheets.PrintOut From:=2, To:=4, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
ActiveWindow.SelectedSheets.PrintOut From:=5, To:=5, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
End Sub
 

More replies
Relevance 75.85%

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 75.85%

Hello everyone,

I am wondering if anyone one would be able to help me. I have only just started getting into advanced excel use of macros and formulas for Excel 07. I have done ok so far except for the following macros formula needed tp change the background colour of a range of cells with more then the three options that conditional formatting allows.
I have a column of cells from C6:C80 which I have made all drop down selections from a list of four text items:
ItemA
ItemB
ItemC
ItemD
I was trying to get a macro that would colour the cells in C6:C80 automatically on selection of the drop down list based upon the four different selections available. And another macro to colour that whole row that has data in it based upon that same selection. The reason why two different ones being that I am unsure of what will work the best.

I know this is on the easy side of macros, but I am hoping someone would be able to throw me a bone none the less

Thanks for any help all.

Ads
 

Answer:Solved: Excel 07 macro to colour cells in a range

Ok turns out I didn't need macro and it can all be done with conditional formatting using the "Highlight Cell Rules". I was under the impression that you could only use three conditions but that was incorrect. Thanks for the help from Sweep at another forum.
 

1 more replies
Relevance 75.85%

Hello I'm having a problem in create a excel macro, that multiply the values from a table(for example the range is (B5:L20)) by the values that are in the column (A5:A20). The result should be place in another sheet.

There is someone who can help me, please.

Regard
 

Answer:Solved: Excel Macro multiply range for a column

6 more replies
Relevance 75.85%

Hello world!I'm a noob in VBA scripting so I decided to ask for some help on running a macro on multiple files in "current directory"So what I'm trying to do is to run a VBS file from a batch. The vbs file should then run the code created by macro record from Excel on all the xls files in the same directory the vbs file is in. I'm sure the code has to be written somewhere but after googling for an hour I decided to ask here, maybe u can redirect me:PAnother thing that riddles me:The code:Sub Makro2() Range("A6:A12").Select Selection.Sort Key1:=Range("A6"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormalEnd Sub...arranges lines from "Range" in ascending order.But what if I only know the first parameter from "Range" (A6), and the second should be decided based on the certain string found further down the A row, so the range length is different in every file?Also I found this script that crashed if after dim x there was the "as" parameter...Is that syntax inappropriate in vbs scripts?aaa, too many quiestions!:PThx for (if any) replies! Cheers!

Answer:Excel macro string dependant lines range

re: the second should be decided based on the certain string found further down the A rowOne quick answer for now:You can determine the end of your range in various ways. Here are just 2 examples:To find the last row in a column:lastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A6:A" & lastRow).Select
To base the row on a specific value, e.g. MyText:
With Columns("A")
Set c = .Find("MyText", optional arguments)
End With
Range("A6:A" & c.Row).Select(Look at Find in the VBA Help files for info on the optional arguments)There are many other ways but we'd need more info to be more specific.BTW...You rarely have to Select a range to perform an operation on it. It's more efficient if you just perform the operation:Range("A6:A12").Sort Key1:=Range("A6"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

10 more replies
Relevance 75.85%

What is the best way to determine through code if a range is selected?
 

Answer:Solved: Excel Macro -> Determine if range is selected

For Example, I want to run an IF THEN statement only if specific cells are not the ones the value is being changed or added.

The specific cells do not go in sequence.
 

3 more replies
Relevance 75.85%

Hello

I am currently working on an excel file where I want to collect some data from a sheet (sheet B) to another sheet (sheet A)

Please see doc attached in my second message right below.
example:
1. Item AB3005 is Cat. 1 starting prod in Jan 2011
2. Would like to go and get range of data in Sheet B that correspond to Cat 1.
3. Then would to paste this range of data to Sheet 1.
4. Range of data should be pasted so that data under N in Sheet 2 corresponds to data pasted under start date in sheet 1
Sheet A
Item Category Start Date May-10Jun-10Jul-10Aug-10Sep-10Oct-10Nov-10AB3005 Cat. 1 Aug-10AD3307 Cat. 2 Jul-10CD2520 Cat. 1 Sep-10

Sheet B
Categories -2 -1 N+1 +2 Cat. 1 B C C D D Cat. 2 B C C D E Cat. 3 A C C F F

Results from Example:
Item Category Start Date May-10Jun-10Jul-10Aug-10Sep-10Oct-10Nov-10AB3005 Cat. 1 Aug-10B C C D D AD3307 Cat. 2 Jul-10B C C D E CD2520 Cat. 1 Sep-10B C C D D

I tried few times already and have been able to put hte right range from sheet B in front of the right category in sheet A. But I have not been able to adjust the range so that they are aligned with the start dates.

Would you have any idea how to do that?

Thank you for the support

Bootoine
 

Answer:Excel MACRO - Copy Range depending on 2 different variables.

6 more replies
Relevance 72.57%

Hello,
I've long used a simple macro on various spreadsheets to reset the last used cell:

Sub Reset_Range()
Application.ActiveSheet.UsedRange
End Sub

This works fine, but prior to using it, I would have to press CTRL + End to see where the current last cell is, then manually delete any blank columns and rows appearing at the end of the data, by selecting them by their column letters / row numbers, right clicking and selecting Delete. Failure to do this would mean the macro didn't successfully correct the last used cell.

I would like to add this stage into the macro.
I found some code on the web that allegedly removes all blank rows and columns, so I pasted it at the top of my existing macro:

Option Explicit

Sub DeleteBlankRows()
Dim Rw As Long, RwCnt As Long, Rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error GoTo Exits:
If Selection.Rows.count > 1 Then
Set Rng = Selection
Else
Set Rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row()))
End If
RwCnt = 0
For Rw = Rng.Rows.count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(Rw).EntireRow) = 0 Then
Rng.Rows(Rw).EntireRow.Delete
RwCnt = RwCnt + 1
End If
Next Rw

Exits:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Sub DeleteBlankColumns()
Dim Col As Long, ColCnt As Long, Rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On ... Read more

Answer:Solved: Excel 2003 macro to delete blank columns & rows, then reset range

6 more replies
Relevance 70.93%

Selection box assigned to macro-button
===============================

I am looking for a way to accomplish the following in Excel (with vba).

- A macro-button called "TIME" on my worksheet.
- When you press the button "TIME" a 'radio-button-selection-box' with 5 predetermined 'hours of the day' should appear.
- The time which will be selected is to be written in the current active cell.

Instead of a 'radio-button-selection-box' another type of selection-box is alright as well.
I do not want a normal listbox in a predetermined cell, because the time that is to be written in the cell could be anywhere (not predefined place) on the sheet.

I know how to assign a macro to a button, but I do not know how to:
* make a 'radio-button-selection-box' appear and call/assign it to a macro.
* read the input once the selection has been made.


I hope my request is stated clearly enough (English is not my native language) and I hope anyone can help me with this problem.

Thanks in advance,
Fie Buls
 

Answer:Excel vba: "How to make a selection box and assigned it to macro-button"

Fie Buls,

Create a userform, put a frame on it. Within the frame place the radio buttons.

For the button, use userform.show to expose the form with the buttons on it.

Use the click or change event on the form to capture the value from the selected button to a variable and be sure to include the userform.hide call to close the form.

I think you will need focus on the cell you want to write the value, then use the activecell.value property to write to the cell. Clear the variable and close the routine.

In general, radio buttons are more difficult to work with than a combobox You could use the combobox on the user form with a dropdown of values. The form is not connected to the worksheet and can be called by the macro from any open workbook if you have the macro security set up correctly. The 'Time' button would have to be placed somewhere where you could access it. I would use a custom toolbar instead of a button.

I hope this helps you get started. If you take a stab at it and send along questions I will be glad to help you along. The requirements are a little vague to build your solution from scratch.

Stoneboysteve
 

6 more replies
Relevance 68.88%

I can trigger e-mail from a fixed date, which doesn't work if excel is not open on e.g. 60 days ahead of todays date. How can I trigger an e-mail ONCE, if a workbook is opened at any time between today and 60 days?Many thanks, Mike

More replies
Relevance 68.88%
Question: Excel Email Macro

Hi

I am trying to write a macro in Excel 2003 to automatically send an email using Outlook to a list of individuals asking them to confirm their personal details using the attached template. Could someone show me where I am going wrong with the script please?

Many thanks
 

Answer:Excel Email Macro

16 more replies
Relevance 68.88%
Question: Excel Email Macro

Hi All,

I have a spreadsheet and i am needing to create a macro that will opena new message in Outlook and populate the 'To' Field with one cell and then the 'CC' field with 2 email addresses, one from one field and the other from a different field. then the subject from another field and then the body from another. it then leaves the message window open so i can check it right and hit send.

I found the script the other day but can not find it anywhere including this site.

Any help on this will be great.

JPL

Any
 

Answer:Excel Email Macro

Hi JPL,

You could use this function, just pass the values to it
Code:

Function MailData(mSubject As String, mMessage As String, Sendto As String, Optional CCto As String)
Dim eSubject As String, EBody As String
Dim app As Object, Itm As Variant
Set app = CreateObject("Outlook.Application")
Set Itm = app.CreateItem(0)

With Itm
.Subject = mSubject
.to = Sendto
If Not IsMissing(CCto) Then .CC = CCto
.Body = mMessage
' .Attachments.Add (NewFileName1) ' Must be complete path
.Display ' This property is used when you want
' .send
End With
Set app = Nothing
Set Itm = Nothing
End Function

It's from one of the posts, I do not remember which one
 

1 more replies
Relevance 68.88%

I have an Excel model (office 2000) which has a range of cells that contain if/then statements. I'm trying to get Excel to automatically send an email if any of the cells within that specified range were to provide a value that was false. Is this possible? Thanks for any insight.
 

Answer:Email Macro in Excel??

16 more replies
Relevance 68.88%

Any way of controlling the time a email is sent via a excel spread sheet using vba?
 

Answer:Excel - macro email-

6 more replies
Relevance 68.06%

I have a database of email addresses (Lotus Notes names, actually) in excel. I can copy and paste into an email address line, but if I try to copy and paste into an email grouping, I get an error message. I can copy onto a blank word document and then copy and paste that into an email group, but can't go direct. I have tried changing the format of the cells but that doesn't work. You can probably tell I'm a complete novice! I just want to copy from excel to email group without going via a word document. (Don't ask me what kind of system I use at work, I've not a clue!!) Also, if there is a solution, could you explain in really simple terms? Thanks.

Answer:Creating an email group from Excel to Word

If you have Microsoft Outlook it can be done.First you will have to Save As your Excel file as Comma Separated Value or dBase from the drop down list in Excel.Click on File/Import & ExportSelect import from another programme and select the CSV option or dBase.Once you have it in Outlook you can easily import it then into OE.It works - best of luck!Brambles

1 more replies
Relevance 68.06%

Hi, I have a column in Excel that consists with time (minutes) from 0 to 80. I need a macro that will take each cell within a range (let say a column A1 to A6000) and do F2 and enter to each of the cells. I have too many cells to do it manualy..F2=>Enter. My lookup won’t recognize the numbers unless I do so. Can you help?

Thanks
 

Answer:Excel Macro that does "F2 and Enter" for a range of cells

6 more replies
Relevance 68.06%

My sense is this is a pretty simple request, but I'm completely illiterate when it comes to VB.

I have an excel spreadsheet with a number of data points. I'm looking to export the entire sheet to a CSV file and send it via Outlook. Any suggestions?
 

More replies
Relevance 68.06%

I have a user who wants to create a macro which will automatically send an excel spreadsheet into outlook and email it.
Any suggestions welcome. Thanks.
 

More replies
Relevance 68.06%

Hello all;

I am new to this forum and also to Excel macros. I have a large Excel file detailing financial accounts with my small business.
Anyway, I would like to email any customer who has an overdue balance as per column V in my attached sample file (any customer with a greater than zero value in V). I would like to send an email; something like this:

Joe Wilson;
As of 2/28/11 (latest date in the row that is on/after the current date), your account is currently overdue and you owe $1500.00 (G3).
Thank you.
Treasurer
I found several other threads explaining how to send an automatic email from Excel, but I haven't found one for sending an individualized email to a list of recipients. If this question has been answered, please feel free to redirect me. Otherwise, I'd appreciate any suggestions. Please let me know if I can detail my needs any better.
Thanks!
 

Answer:Excel Macro: Auto Email

welcome to the board.

I put some code in the sheet

The macro name is CheckAndMail

Let me know if this is what you need

PS: I just corrected something I came accross and have re attached the corrected sample
 

2 more replies
Relevance 68.06%

Windows 7 --------------------------------------------------------------------------------First thing...I know nothing about macros!! I do know a very little about formulasI have created "IF" formula in excel 2010, based on a date it will create a send due in column "E", =IF(D5=$A$2,HYPERLINK(mailto:"&$K$1&"?subject="&A5&-B5&"&body="&$C$3,"sendworks great but, I have to go thru 86 rows in column "E" and hit "Send Due" numorous times,then hit send again on all the emails, can we automate this some how, like a macro that engadges when I open my outlook every morning?Thank u

Answer:Need Macro to automate email from excel

I have written code in Outlook, which is essentially a template, you fill in the details, all sorts of stuff happens accordingly to what you have selected, and you press send and it will send the emails to whoever it is you want. Iv done this in Outlook, so my question is, why do you need this to be done in Excel? is Outlook acceptable?

3 more replies
Relevance 67.24%

Hi all, looking for some assistance to save time with a task. I am required to send an email to email addresses in cell E if the cell value in d is greater than 0. Would appreciate assistance, thanks
 

Answer:Solved: Excel mail a row/rows to email addres in range

Hi

Attached is an example of how you would send emails automatically from your spreadsheet. If your "real" spreadsheet is in any way different from the example the macro will need to be adjusted, Also, I used column H to identify when an email has been sent and told the macro not to re-send to that address(es). If you do want to re-send you would need to delete the notation in column H.
 

3 more replies
Relevance 67.24%

Hi - I have a list of names for a particular company where I have the first name and last name of the customer plus the email naming convention their company uses. For example the company's email naming convention is: [email protected] Is there a way I can create an Excel Macro that will automatically create the email from the available information? For example if I create three columns where one has the first name, last name and the @companyurl.com?I hope someone can help with this as it will save a lot of time!!Thank You!

Answer:Need Excel macro to creat email address out of just a name

This can be done fairly easily, but I'm not going to offer any suggestions.Since it appears that you do not work for this company, I can only assume that this will be for some sort of mass, unsolicited mailing. If your project was sponsored by the company in question, they would supply you with a distribution list and you wouldn't have to build your own.For all I know, I'm on that list of names that you have and I don't need any more unsolicited email.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 67.24%

I would like to ask for an Excel macro which can send an automate email on a certain date every Month.

The dates would be as follows:

1st Monday: Email should be sent to [email protected] automatically
2nd Wednesday: Email should be sent to [email protected] automatically
3rd Tuesday: Email should be sent to [email protected] automatically
3rd Tuesday: Email should be sent to [email protected] automatically
3rd Tuesday: Email should be sent to [email protected] automatically

Subject line: ABC123D5679_Data transfer expected today

Details of the body of the text message:

"Dear All,
This is to remind you that we are expecting the data transfer to be posted in Novartis system today . This is just an advanced intimation.

Kindly let us know if you foresee a delay in the data transfer.

Kind regards,
Rohit"
Many thanks in advance for your help guys. This means a lot.
 

More replies
Relevance 67.24%

Hello

I need to come up with a simple outlook macro (or script?) that fires off whenever a new email meeting certain criteria is received.

1. new email is received with "please refresh quotes" in the subject
2. outlook runs a macro that causes another macro to run (the second macro is located in a excel workbook which is already running in my office pc)
3. the excel (attached) is simply refreshing market prices and emails whenever the macro is triggered. because the quotes are obtained via a DLL feed I can only get it at work.

the purpose is simply to get live market quotes on my personal email from my work email whenever i need.
thanks in advance for your help.
 

Answer:Solved: Run excel macro when new email is received

9 more replies
Relevance 67.24%

Hi there,

I have a workbook which i would ideally like to send an automated mail when the date is within 30 days of "Todays date" .
I have found something similaar on past posts whichprints certain cells to an email but is triggered by a button press not date, but wondered if anyone could adjust it for me as my excel knowledge is very limited.
I really am struggling.

The password for the spreadsheet is Kalibratedbyme (capital K)

Best regards and many thanks!
 

Answer:macro to allow a date to send an email in excel

The content is different but why are you duplicating a post?
 

3 more replies
Relevance 66.42%

Hi:
I am very new to Excel 2007 and macros. I have a spreadsheet that I am trying to get to send an email reminder to the point of contact [ col b ] 5 days prior to the closure document due NLT date [ col m ]. I am looking for assistance in writing a macro which will accomplish this if it is possible. I have attached the spreadsheet that I referenced.
Your assistance would be greatly appreciated.
Thanks in advance.

desantisj
 

Answer:Excel 2007 Macro to Send Reminder Email

desantisj, welcome to the Forum.
There are already 3 or 4 posts on this forum that have the VBA code (Macro) that you can modify for your Workbook if you can read the code. Zack has written the code so it is a bit complicated, but it should be a case of substituting your Cell references that hold the data for the ones that others have used.
Otherwise it is a case of waiting for an Excel guru to come along and help. If none of them come along I can probably help you, but I normally work with Access.
 

2 more replies
Relevance 66.42%

Hi there - Using MS Access 2007.
I have a FORM with a macro that runs a query to generate ONE excel file and attaches to email using MS Out look to send out. - That Works great.

Problem: Multiple users to send email & attachment.
I would like for the macro to generate excel file (S) - based by the UID.

Can someone suggest a better way of handling this that is not time consuming?

Thanks In advance!
 

Answer:MS Access 2007 - Macro > Excel > Auto Email

Access can open Outlook and send an attachment to mutiple recipients either as a list or as individual emails.
So are the attachments all the same for the recipients?
 

1 more replies
Relevance 66.42%

Hy guys

2nd time i am posting stuff for help, and as i was helped before i will again look forward the response.

I have a file of excel, in which i am sending emails to different candidates of admission, with scan letter placed in the same folder by name.

I want to edit this code, which could select attachment based on Column A list adjacent to the email address

I am attaching the file also pasting the code

Sub Test1()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Office 2000-2013
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim SigString As String
Dim Signature As String
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "C").Value) = "yes" Then

Set OutMail = OutApp.CreateItem(0)

strbody = "We at Graduate School of Engineering Sciences and Information Technology are extremely pleased to know that you have selected Hamdard University as preferred choice for your graduate/post-graduate Studies. " & vbNewLine & vbNewLine & _
"Hamdard University is a pioneer Higher Education Institute (HEI) of Karachi producing Masters and PhDs in the fields of Engineering, Computer Sciences, Information Technology, Energy and Environment since 19... Read more

Answer:Attachment based on cell value in a excel email macro

anybody ???
 

2 more replies
Relevance 66.42%

Hi
I need help writing a macro that takes an Excel file to email.

The Excel file is a data base, I wanted the Macro to take the email from a column of the Excel file, and send out the email to all the emails in the database, if possible also adding a column of the Excel file to address the specific person.

thank you.
 

Answer:need help writing a macro that takes Excel file to email

Can you post a sample file? Please remove any sensitive data.

Rollin
 

2 more replies
Relevance 66.42%

I found this code in this forum.
i want to add recipient as CC or BCC. What is the correct code for that?
Thanks in advance!

Code:
Public Sub email()

Dim SubJ, Recip As String

SubJ = "Enter your suject"
Recip = "[email protected]"


ThisWorkbook.SendMail Recip, SubJ

msgbox "Email Sent"

End Sub

 

Answer:Send excel sheet ( email) through macro with recipient and cc

6 more replies
Relevance 66.01%

Hello,

I have a list of email addresses that have either:
[email protected]
[email protected]
[email protected]
or no corelation to [email protected]

I'm looking for a macro that will take the emails with dots in them and put the first and last names into cells next to the email. The others I can do by hand. PLEASE HELP!
 

Answer:Solved: Excel Macro to convert email addresses to names

16 more replies
Relevance 66.01%

I found how to send an email from an Excel Macro (http://forums.techguy.org/business-applications/1056070-solved-excel-email-outlook-code.html)

Here is the code I'm using:
Code:

Sub Email_Test()
'
' Macro11 Macro
' Keyboard Shortcut: Ctrl+r
Dim eSubject As String
Dim Sendto As String
Dim CCto As String
Dim Body As String
Dim app As Object
Dim Itm As Variant
Set app = CreateObject("Outlook.Application")
Set Itm = app.CreateItem(0)
'Populate variables

mSubject = "Excel EMail Test"
Sendto = "[EMAIL="[email protected]"][email protected][/EMAIL]"
CCto = ""
mMessage = "You have received Knowledge Document Feedback for your domain."

With Itm
.Subject = mSubject
.To = Sendto
If Not IsMissing(CCto) Then .CC = CCto
.Body = mMessage
' .Attachments.Add (NewFileName1) ' Must be complete path
.Display ' This property is used when you want
' .send
End With
Set app = Nothing
Set Itm = Nothing
End Sub
Is there a way to format the message section with bullets? How about paragraph breaks?

Name: John Doe

Date: 6/15/2012
Doc #: KM123456
Etc:
Etc:
Comments:
I'm perfictly willing to look at how-to documentation if someone can point me in the right direction.

Thanks
firstshot
 

Answer:Solved: Formatting Excel Macro Email Message Section

Hi, I haven't really looked myself but instead of bullets I generally use a dash and a tab
Paragraph as such are not possible but you have the linefeed and carriage return

example to add to your macro code before calling the mail proc
Code:

Dim mytext as string
mytext = vbnullstring
mytext = "Dear Jon Doe" & vbclrf & vbcrlf
mytext=mytext & "-" & chr(9) & "Date :" & the date variable & vbcrlf
mytext=mytext & "-" & chr(9) & "Doc #:" & the dos variable & vbcrlf
mytext = mytext & "-" & chr(9) & "Etc :" "etcetera"
mytext = mytext & vbcrlf & vbcrlf
mytext = mytext & "Sincerely yours," & vbcrlf & vbcrlf
mytext = mytext & "Jane Doe" & vbcrlf

vbcrlf can also be replaced wij Chr(10) or Chr(13)

See it this helps.

Maybe html formattingis possible but this works just as fine.
 

3 more replies
Relevance 65.19%

Hi all,

I need to know if is it possible that a macro in Excel sends an email containing the visible cells using Microsoft Outlook.
btw, I saw some code on the internet, that saves the visible cells in a new excel file and attach it in a new email using Microsoft Outlook, but I don't want to have attachments in the email.

thanks for any possible help.
 

More replies
Relevance 65.19%

Hi all

Looking for some help! Working on an excel document which contains a button I need to send to multiple people, that's fine what I need is for the subject field of the email to take the contents of a particular cell in the workbook and display that. My current code is;
Code:
Sub SendThis_ActiveWorkbook()

Dim Recip()
With ActiveWorkbook

ReDim Preserve Recip(0)
Recip(0) = "[EMAIL="[email protected]"][email protected][/EMAIL]"
ReDim Preserve Recip(1)
Recip(1) = "[EMAIL="[email protected]"]example[/EMAIL][EMAIL="[email protected]"][email protected][/EMAIL]"
.SendMail Recipients:=Recip, _
Subject:="Vacancy Authorisation " & Format(Date, "dd/mmm/yy")
End With

End Sub
Basically I want the subject to be "Vacancy Authorisation [cell contents] 01/01/2000" so I want the contents of a cell to appear in between the date and the "vacancy authorisation" part of the subject line. Assistance appreciated!
 

Answer:Solved: Excel email macro auto-populate subject line

Subject:="Vacancy Authorization " & Range("A1").Value & " " & Format(Date, "dd/mmm/yy")

Regards,
Rollin
 

2 more replies
Relevance 65.19%

Although decently experience with excel, I am quite new with VBA and I cannot "record" this macro, so any help writing this in VBA is much appreciated.

1) I need to create a macro that will take a number that is entered into a cell, and upon hitting a button in the excel sheet create an email where the subject line is the number that was in that original cell.

1b)I would like to do this without changing the name of the excel file though, or at least have another macro that resets the file to its original name.

2) Also, if possible, the same macro/vba as above, only with two cells, a number in one and a name in the other, which then shows up as "Number-Name" as the subject line in the email.

Thanks to anyone that can help - Mike
 

Answer:Excel Macro -> email created with field data as subject line

8 more replies
Relevance 61.91%

is there a way to select many cells using the row and column number?
the range command permits to select knowing the letter and number of a cell, in 1 cell or many cells. range("a1:c5").select, or cells(5,12).select.

what I am trying to do is range(1,1:3,5).select. which I know it is wrong.

thanks
 

Answer:range vs cell selection in VB Excell

Is there any particular reason why you don't want to use the column letters in conjunction with the row number? Also, what is the purpose of selecting these multiple cells? Will you be copying the data to another worksheet? Please provide more details on what you are ultimately trying to accomplish.
We also need to know in your example above if you are only trying to select cells A1 and E3 or are if you trying to select the entire range of cells between A1 and E3 ?

Rollin
 

1 more replies
Relevance 61.91%

1st of all, hello

So, I have 2 computers
1 of them is a desktop pc the other one is a laptop

My Desktop pc is 2yrs old, my laptop is pretty new, 2months old or something

and heres the problem: The icon selection range on my laptop is somewhat messed up, it's very large and stretched, here example:
When it should be like this:

The selection area on my pc is normal, not so stretched like on my laptop

Any way to fix this?

Answer:Desktop icon selection range

  
Quote: Originally Posted by ChrissSpirit


1st of all, hello

So, I have 2 computers
1 of them is a desktop pc the other one is a laptop

My Desktop pc is 2yrs old, my laptop is pretty new, 2months old or something

and heres the problem: The icon selection range on my laptop is somewhat messed up, it's very large and stretched, here example:
When it should be like this:

The selection area on my pc is normal, not so stretched like on my laptop

Any way to fix this?


The "Icon Selection Range", as you call it, is I think based on the largest width produced by an icon name. Check the names of icons where the name fits the entire width of the icon area, and try to rename the icon to something shorter.

It might help...

5 more replies
Relevance 61.5%

Need a formula that does the following.if a2:a50 = food then return the value of the cell in the same row.example if the formula searchs and finds a15 equals food then the formula would return f15's value of "good". Any ideasThanksmessage edited by soul_dad_5

More replies
Relevance 61.09%

I'm trying to create a macro that will copy multiple selections from a sheet in one workbook to another sheet in another workbook. The selection would be based on a date range of dates in Column A of the first workbook. This date range would need to be entered as a parameter by the user... In more detail, here's what I would like the macro to do...1.In Sheet1 of Workbook1, User runs macro... macro asks for Start Date & End Date.2. When date range is entered, rows in column A of Sheet1, Workbook1 containing dates within this date range are found.3. Columns D, E, F, I, J, L, M are selected in each row where Column A date value is within the date range.4. In Sheet1 of Workbook1, the column values of the rows found are written to the columns in Sheet1 of Workbook2. More specifically...Column F to Column A in WorkBook2Column D to Column B in WorkBook2Column E to Column C in Workbook2Column I to Column D in Workbook2Column J to Column E in Workbook2Column M to Column G in Workbook2Column L to Column M in Workbook2I hope this is defined enough to be of assistance. Any questions, please let me know. Thanks in advance.

Answer:Copy Multiple Selection by Date Range

Hi,I'm trying to create a macroOK, so you are trying to create a macro, and you have told us what you want it to do.What help are you asking for - Do you want someone to write this macro for you, or do you want some suggestions as to how this macro could be structured to meet your objectives.If you are trying to create a macro, have you written any code yet - if so, you could post it and identify what isn't working and ask for some help.If you want someone to write it for you, that's a pretty big 'want' - Everyone who answers these posts is volunteering their time and knowledge and this is not a 5 minute task.In addition:... you have not identified what happens if this macro is re-run. What happens to data already copied - is it erased first and all the new data moved to its destination.What happens when a different date range is selected - are the rows copied to the destination worksheets below the previous data.Is data moved to new workbooks for new date ranges.Are checks needed on the input dates to test for acceptable date ranges.If anyone does write some code for this - are your workbooks really named "Workbook1" and "Workbook2" and what are the file extensions. Will the two workbooks be open before the macro is run, or will 'Workbook2'.xls' need to be opened.Regards

2 more replies
Relevance 61.09%

Hi Guys,

I have a worksheet with 10 headings, under each heading is a number of questions (which I have named as a range the same as the heading). On a seperate worksheet I want to use the headings in a drop down and for the range (list of questions) to be populated under the drop down.

Please can someone advise what formula I could use to achieve this.

Many thanks
GM
 

Answer:Solved: Return a range from a drop down selection

11 more replies
Relevance 61.09%

Excel 2003a = shift (range name)b = 10/24/10 (cell reference)c = 10/30/10 (cell raference)need to count shift with occurance in Date Range 1 if >=b or <=c or Date Range 2 =if >=b or <=cSame date within to seperate range names.Cannot seem to get this to work using SUMPRODUCT or SUM array. Assuming it is a syntax problem, but not sure.Have been struggling off and on for days. Please help!

Answer:Excel 2003 Date Range with Range Names

Please review the How To referenced in my signature line and then post an example of your data.Include some input data and the expected output based on that data.With a better understanding of how your data is laid out we should be able to offer a solution.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 60.68%

Hi,

I have a problem with Outlook 2007.
I use 5 languages regularly and would like to use a macro to select the language.
I’m doing this in Word, but the macro will not work. As much as I have found out MS has chosen another programming language for their macros in Outlook, or a different set of commands.

This is the macro from Words:

Sub German()
'
' German Macro
' Macro recorded 02-04-98 by nordic123
'
Selection.LanguageID = wdGerman

End Sub

As you can see this has been in use sins 1998.

Has anyone a suggestion, please.
Windows XP pro, MS Office 2007
 

More replies
Relevance 60.68%

Hi Guys I need help with running a Macro from a selection in a List Box well actual I want to run one of about a dozen from the dozen selctions.

My VB skills are not that great and I usually make my Macros by recording them.

I have managed to make the List Box using Data Validation and a range of cells C279:C295. The List Box is located in Cell E5. I want to be able to select one of the selections in the list and then simply have it run the corresponding Macro. Could someone show me an example Code? The list contains: 2 Days, 3 Days, 4 days etc. The Macros would be named Macro2, Macro3, Macro4 etc numbers correspond for ease.
 

Answer:Run a Macro from List Box selection

11 more replies
Relevance 60.68%

Hi there,

I would really appreciate some help with a problem I am having in Excel 2013.

Since the switch to Excel 2013 I am no longer able to group in new pivot tables, although tables based on the excact same data in the previous Excel version still group without any problem. The group options are greyed out.

If I copy/paste and then edit previous pivot tables all works fine, but I am trying to take advantage of the new 'distinct count' function, which it seems can only be used when a new pivot table is created and the 'Add this data to the data model' box is checked. I have not been able to find a way to add this to existing pivot tables.

I have attached a data sample which shows the problem. I hope I am missing something obvious.

Thank you in advance,
Katrin
 

Answer:Excel 2013: Pivot Tables 'group' greyed out /unable to group

I don't think you can use grouping with the add to data model function.
You could add a further column to the data and group your regions or dates that way ...
Other info:
Workarounds: http://www.pivot-table.com/2012/02/22/unique-count-in-excel-pivot-table-with-powerpivot/
More Info on Distinct Count: http://datapigtechnologies.com/blog/index.php/distinct-count-in-pivot-tables-finally-in-excel-2013/
 

1 more replies
Relevance 60.27%

Hello,i'm trying to build a macro and assign it to a button for:- copy/paste selection valuecopy from sheet1 range A2:E4 based on a number in colomn F(cell F2)paste to sheet 2- copy/paste selection valuecopy from sheet1 range A5:E7 based on a number in colomn F(cell F5)paste to sheet 2and continue like this.if there is nothing in colomn F(cell F..) just to pass it and move forward.for copying i want to be copied like this in sheet 2for example5 times for A2:E4 range from sheet12 times for A5:E7 range from sheet1to copy like this in sheet 21.1-A2:E41.2-A5:E71.3-A8:E101.4-A11:E131.5-A14:E162.1-A17:E192.2-A20:E22Thank you very much in advance for the time and help.Best RegardsD.D.

Answer:copy/paste selection range based on a cell value

Try this...Sub Copy_F2F5_Count()
'Copy A2:E4 based on F2
numCopies = Sheets(1).Range("F2")
For copyRange = 1 To numCopies
nxtRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets(1).Range("A2:E4").Copy _
Destination:=Sheets(2).Range("A" & nxtRow)
Next
'Copy A5:E7 based on F5
numCopies = Sheets(1).Range("F5")
For copyRange = 1 To numCopies
nxtRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets(1).Range("A5:E7").Copy _
Destination:=Sheets(2).Range("A" & nxtRow)
Next
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

10 more replies
Relevance 60.27%

Hi,
I'm trying to have a box come up with a list and depending of the selection, run a macro. I have the following, but I'd like to have the list in the box instead of on the sheet. I'm new to boxes, so I hope I'm using the right terminology
HTML:
Sub test()
On Error Resume Next
InputCells = Application.InputBox(Prompt:="What Month", Type:=2)
If InputCells = vbNullString Then
Exit Sub
Else
Select Case InputCells
Case "January"
Janu
Case "February"
Febu
End Select
End If
End Sub



Sub Janu()
Application.Goto Reference:="january"
End Sub



Sub Febu()
Application.Goto Reference:="february"
End Sub

Mike
 

Answer:Solved: list box selection to run macro

16 more replies
Relevance 60.27%

I want to run macro on a worksheet when it is selected, either by clicking on the worksheet tab or if directed there by a hyperlink.

Not sure how to set it up without writing a macro for every sheet, would like to use an array if appropriate.

Thanks
 

Answer:Activate Macro on Worksheet Selection

7 more replies
Relevance 59.86%

I am trying to lookup a range of values within excel and then return a list of values within one cell.

Here is an example of the data.

Data Sheet

Acura A new york
Acura b charlotte
acura c los angeles
acura d San Francisco
BMW a San Diego
BMW b Rochester
BMW c San Antonio
Lookup Sheet

Acura a C

Basically I want to see all the acura values from the range of a to c written to a single cell on the lookup sheet. So after the lookup, the lookup sheet would look like:
Acura a c New York, Charlotte, Los angeles
I've tried using a mixture of arrays, matches, lookups, etc... but Im stuck. I think it may require some coding which I am not strong in.

any help would be appreciated.

Dave.
 

Answer:Excel Lookup Range and return range

Hi there, welcome to the board!

While someone might get a fancy formula, and I know you could do it that way, I think it'd be too difficult to manage, let alone go back to it in six months and still understand it. I would go for VBA and write a UDF. This does work, assuming the car type is in (specifically in this example I use below) column A, the letters in column B, and city in column C. This also is predecated on the assumption that column B is sorted in ascending order, otherwise it won't work. If that is going to be an issue, we would just need to put in a sort routine in the array. That being said, use this code...
Code:
Option Explicit

Const sDelim As String = ", "

Function GETLIST(rLook As Range, vType As Variant, vStart As Variant, vEnd As Variant) As Variant
Dim wsLook As Worksheet, iStart As Long, iEnd As Long, iStop As Long
Dim i As Long, arrData() As Variant, vData As Variant
arrData = rLook.Value
iStop = 0
For i = LBound(arrData) To UBound(arrData)
If LCase(arrData(i, 1)) = LCase(vType) Then
If arrData(i, 2) = vStart Then iStart = i
If arrData(i, 2) = vEnd Then iEnd = i
If iStart > 0 And iStop = 0 Then
vData = vData & arrData(i, 3) & sDelim
End If
If iEnd > 0 Then iStop = 1
End If
Next i
If iStart = 0 Or iEnd = 0 Then
GETLIST = "ERROR!"
Exit Function
End If
If Len(vData) ... Read more

1 more replies
Relevance 59.86%

hi,
i want to send one message to more than one group at a time, so how to send please help me....

Answer:Whatsapp multi group selection for sending messages

Whatsapp multi group selection for sending messages
hi,
i want to send one message to more than one group at a time, so how to send please help me....

2 more replies
Relevance 59.04%

I am using the MSN Stock Quote addin with MS Excel 2003. Prior knowledge of this should not be necessary, but I just thought I'd provide full context to my issue. I wrote a macro that essentially records daily stock prices. Because I don't want to store old quotes into perpetuity, i need to ensure that the update quotes button has been hit. I use sendkey() to accomplish this, and then the macro copies the quotes and pastes them elsewhere. My main goal is to have the task scheduler open this file daily so i dont ever have to think about this again, and i'm so close. Essentially my problem is that it takes a few seconds after the update button is hit for the add-in to fetch the new prices from the internet, and this process appears to be delayed until the macro is finished. This is a problem because the macro ends up recording un-updated quotes. I have tried the Application.Wait procedure, and I've tried time consuming loops, do while loops, etc. All of these approaches result in the same thing: Can someone please help me with this? I'm too lazy to hand update this spreadsheet every trading day for the rest of my life (or until i quit). Thank you in advance!
 

Answer:Excel Macro - Allow cells to update query before macro completes

Can't you just put the update macro code in a module of it's own and call it from the main macro?

Regards,
Rollin
 

1 more replies
Relevance 58.63%

Hello everyone! I am new to the forum and I hope I can find some guidance to design a VBA code for a very special project I am working on for my employer. Currently, my database imports an excel spreadsheet, runs it through some filters and creates a base list of files to be potentially reviewed each day. After this list is compiled, a Six Sigma tool is run which determines how many of those files should be reviewed (normally it is somewhere in between 40% to 60%). The number of files to be reviewed is inputted into a table with the rest of the data for the tool. Here is where I need some help.

I need the database to randomly select which files will be reviewed. I already have a random number generated when I created the base list along with a checkbox to mark which file was selected for review. From here is where I am lost and need help. There are up to 14 categories within the base list. There needs to be at least one file from each list and the files selected need to be proportionately selected based on how many files are in the category. Basically, if one category has 10 files, then 5 random files selected (based on an example of 50% decision by the Six Sigma tool); likewise, if there is 1 file then that 1 file has to be selected. From this point, files need to be randomly selected until the number of files to be reviewed is met (from the value in the table).

I was thinking that this could be approached as a loop with files being pulled one at a time based on which cat... Read more

Answer:Solved: Access 2007 VBA Code for Random Selection from a Sample Group

16 more replies
Relevance 58.22%

Hi There

It has been quite a while since I have asked for you expertise in Excel Macros. I have been wrecking my brains for days on this one without any luck. You are my last and only resort. Here is what I want the macro to do.

My cursor happens to be in Column B. It could be anywhere in column B. If the cursor is in column B then I want a macro that does this.

If the curser is anywhere in Column B then go over to the right 3 cells. If the curser is not in Column B then .........other code goes here.

This small code will be part of a bigger macro.

Thanks for your help.
 

Answer:In Range Macro?? Maybe??

If ActiveCell.Column = 2 Then
ActiveCell.Offset(0, 3).Select
Else
'other code goes here
End If

(Hi Mario )
 

3 more replies
Relevance 57.81%

I have an application where I want to define Name Range in a Macro, but I want to define it relatively for the active cell.

The VB code
ActiveWorkbook.Names.Add Name:="Rats", RefersToR1C1:="=Sheet7!R1C1"

Defines an absolute Name Range, but I want it to be the active cell at the start of the macro, which could be anywhere I select. I would like the macro to copy the cell contents, go somewhere else to paste it and return (via Goto) to the cell I selected when the macro began snd continue with more stuff.

I cannot find the proper coding to make this work.
 

Answer:Solved: Name Range in macro

Hi

You don't really need to define a named range. Here is an example that shows a different method as well as using a named range. Give it a try and if you have any questions just ask
Code:
Sub test()
'
Dim oRnge As String
Dim oWS As String
Dim oVal As Variant
'
'
'Establish your starting point
oRnge = Selection.Address
oWS = ActiveSheet.Name
oVal = Range(oRnge)
'
'do what you need to do ie when you want to place the original
'value in a new sheet do this
'
Sheets(2).Activate
Range("A1") = oVal
'
'When you want to get back to where you started
'
Sheets(oWS).Activate
Range(oRnge).Activate
'
'
'If you really must use a named range you can do this
ActiveWorkbook.Names.Add Name:="Ratsxxx", RefersTo:="=" & oWS & "!" & oRnge

End Sub

 

2 more replies
Relevance 57.81%

Hi Again

This is probably one of the easiest macros to solve. I have data in Column A but the data is not in every cell. I am using a macro to delete the last character from the right. The macro works fine but it stops at an empty cell. It does not continue down the list. I have attached the spread sheet. Run the macro that I have created and you will see what I mean.

Thanks

Mario
 

Answer:Selecting a range using a macro

Try this
Code:
Range("A1").Select
For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
If cell.Value <> "" Then
cell.Value = Left(cell.Value, Len(cell.Value) - 1)
End If
Next cell
Rollin
 

3 more replies
Relevance 56.99%

In excel using a macro I want to to select a range of cells starting at B11 and going down until the data in column B does not start with the letter ?Z?, then sort them smallest item first.Any suggestions would be appreciated

Answer:Macro to find a range and then sort it

I assume that the range you need to sort is all in Column B.The following code will do what you ask, although I'm not sure that what you asked for is really what you want. Allow me to explain...When you say "sort them smallest item first" I assume you mean Ascending order.For numerical values such as 2, 212, 22, 4 and 3, Excel will sort them like this:23422212However, for Text values like Z2, Z212, Z22, Z4 and Z3, "smallest" takes on a different meaning to Excel. Excel is going to sort that list like this:Z2Z212Z22Z3Z4If that is not what you are after, please provide some examples of your data so we can see what we are working with.
Sub SortByZ()
'Determine last row with data in Column B
lastRw = Sheets(1).Range("B" & Rows.Count).End(xlUp).Row
'Determine how many cells have Z as the first character
numZ = WorksheetFunction.CountIf(Sheets(1).Range("B11:B" & lastRw), "Z*")
'Sort B11 through last row with Z as the first character
Sheets(1).Sort.SortFields.Clear
Sheets(1).Sort.SortFields.Add Key:=Range("B11:B" & 11 + numZ - 1) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Sheets(1).Sort
.SetRange Range("B11:B" & 11 + numZ - 1)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 56.99%

Had previously sought help , on this thread without any success.
http://forums.techguy.org/business-applications/1142983-upload-financial-data-commsec.html
Have discovered that the site has a table of financials that I want and will now attack it in a different way.
I wish to upload the table , with company historical financials (10 years), from website which is password protected, into spreadsheet for further analysis.

Can copy and paste this information into workbook and am looking for assistance with a macro to transfer from “Data” sheet to “Template” sheet.

My first thought is to use a “named range” .
However, although I have read many threads and links I am unable to fully understand how to to create a named range for items in the “Data” Sheet, I wish to use.
Would be grateful if someone would guide me
1. in a step by step guide to “naming” the type of financial (i.e.Sales figures in Data sheet which will be used in Template
2. Steps I need to take to record a macro so that I can run it for each new company I analyse
Re 2 I had recorded a macro but each time I went back and ran the macro it just produced the original data
The attached file shows different dates and stats however the details in full Template sheet are too many to include.
Hopefully this shortened version will enable a bit of an idea of my spreadsheet
Thanks

Pedro
 

Answer:Assistance with a Named Range and a Macro

16 more replies
Relevance 56.58%

I want to know an exel formual for the following - Any help would be great

Date Range
01/12/2013 - 03/14/2013

How many days from the date range 01/22/2013 - 02/21/2013 are in the above date range

So answer for the above would be 31

Thank you for your help!
 

Answer:Excel 2010 - Count # Of Days In A Date Range That Are Part of Another Date Range

Hi LucianC - try entering this info:
Start Date in A1
End Date in B1
=DateDif(A1,B1, "D") in C1
 

2 more replies
Relevance 56.58%

I have a 2 sheet spreadsheet. In sheet "data" I have a range "allp". I need a macro that will copy and delete range "allp" then select sheet "test" and paste the copied values with the 1st value into cell B1 of that sheet. Any help would be greatly appreciated.ThanksJohn

Answer:macro to copy a range from 1 sheet and paste

Sub CutPaste()
Sheets("data").Range("allp").Cut _
Destination:=Sheets("test").Range("B1")
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 56.58%

Hi. Is it possible to have a macro select a random cell or range as needed by the user. Say the macro selects a cell or range, and then pauses to allow the user to select a random cell or range, whereas the macro then continues with whatever commands needed?

thanks,
Bob from Tucson
 

More replies
Relevance 56.58%

Hi there
Something for Excel specialists out there. Am trying to import Excel data into Access (using 2007 versions but all files saved as 2000 format) and getting 'subscript out of range' error.
I looked around for similar problems and found it mostly occurs when running macros, which I am not. I have tried renaming the Excel file to something shorter and without space, also making the active cell on saving the last one in the worksheet (as suggested somewhere) but no luck.
Any help welcome!!
 

Answer:Solved: Subscript out of range error without using macro!

9 more replies
Relevance 56.58%

I am in need to a macro that replaces a range to cell address with another for the entire worksheet. For example, $5451 with $401 and $5452 with $402, etc. I am new to macros. Any help will be appreciated. Thank you!
 

Answer:Macro to Find/ Replace a Range of Values

Try something like this:
Code:

Sub FandReplace()
Dim cell1 As Range, AllCells As Range
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
For Each cell1 In Selection
If cell1.Value = "$5451" Then cell1.Value = "$401"
Next
Range("A1").Activate
End Sub
 

1 more replies
Relevance 56.58%

I am trying to write a VB command that will run a macro when any data is changed within in a particular range of cells upon exiting any of those the cell.

For example in my workbook if someone changes data in any cell in column I in rows 7 through 497 I want my Run2 macro to run.
Here's what I have.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = I7:I497 Then

Call Run2

End If

End Sub

I think its as simple as defining the range correctly but I can't find the proper command.
 

Answer:Solved: Run Macro When Any Data Within a Range of Cells Changes

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("I7:I497")) Is Nothing Then
run2
End If
End Sub
 

2 more replies
Relevance 56.58%

see excel sheet.

There are 4 groups of rows to be hidden. As need arises, click a macro to ungroup a specific group only.
Then hide the opened group via a macro.

WB is not protected.

I tried recording it but the macro reader does not recognize the + & minus sign in the left hand col of
the sheet.

Bit of a newbie so please be kind!

Thanking u in anticipation.
 

Answer:Group and ungroup multiple macro

10 more replies
Relevance 56.17%

We recently migrated from Office 2003 to Office 2010.

Just to give some background, (and I dont' know if this is relevant to this error), the Office 2003 excel did not accurately show the correct file paths where files were linked. It would show something in the C drive.

The issue I am concerned about today is this: when some users close files in 2010 (files were created in they sometimes get the error below:

'"The name ABE2, either conflicts with a valid range reference or is invalid for Excel. This name has been replaced with _ABE2"

Can someone assist me with this? I need to stop it from appearing and I am not sure how.
 

Answer:Solved: Excel 2010 v Excel 2003 Error Valid Range

14 more replies
Relevance 55.76%

I want to capture data within a worksheet from 1 sheet to another but the position of the data is in the incremental of 58 cells.For example:-=+'Prospect Details'!C502 (1st cell position)=+'Prospect Details'!C560 (2nd cell position)=+'Prospect Details'!C618 (3rd cell position)I am currently changing the position of the cell (ie., C502 to C560) manually by typing the new cell position over the initial one.Is it possible to use macro, if yes, how? Thanks.

Answer:Macro to copy relative data range fr wrksheet

First, you don't need the + sign in an Excel formula.Second, you could do it without a macro:In a "spare" column e.g. G, try this:Enter 502 in G1Enter =G1+58 in G2Drag it down as far as you need.In the cell where you want your first formula enter:=INDIRECT("'Prospect Details'!C"&G1)Make sure you include the single quotes around the Sheet name.Drag this down as far as you need.Finally, if you want a macro, try this:Assumptions:- Your first formula goes in A1- You need 10 formulas, from C502 to C1024Sub Formula58()
For nxt58 = 502 To 1024 Step 58
myRw = myRw + 1
Range("A" & myRw).Formula = _
"='Prospect Details'!C" & nxt58
Next
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

8 more replies
Relevance 55.76%

I need a macro that would:

1. Select a specific range (e.g. A1:A10)
2. Copy that range
3. Then allow the user to select the first cell of a random range (e.g. Z1:Z10) and the macro would paste the "FORMATTING ONLY" from A1:A10 to Z1:Z10

One more thing: I see that after a macro is applied, the "Undo" function is disabled. Is there a way around this? For many times the user will unintentionally click the wrong button and need to undo his last choice.

Thanks so much!
Bob Tucson, AZ
 

Answer:Macro to copy & paste FORMATTING ONLY to a random range

10 more replies
Relevance 55.76%

Hello All,

I have got data in more than 1800 rows and applied 35 grouping's to it (rows count isn't same for these grouping's)

Now, I want to ungroup one at a time of my choice of these 35 groupings while sheet remain protected all the time...

If need be, if I put a value in a selected column "A" and in cell above these groupings for it to identify..

any advise on this would be appreciated....

Thanks
mail2khalsa
 

Answer:Solved: Selected row group to ungroup with macro/vba

16 more replies
Relevance 55.35%

Hi, I'm new to this forum (actually forums in general) I was wondering if anyone could help me with a macro code. I work with 4 Excel sheets for my shippments, 2 sets for one plant and 2 for the other but all within the same file.

What I am trying to do is set up a macro that will automatically search for changes within sheet 1 (also for sheet 3, the other plant) When I change the rows color from blank to green/red, I need the macro to automatically cut that whole row and paste it into sheet 2 on the next blank row.

This doesn't seem like it would be that hard to do, but I know know the whole macro language.

If you can answer this please send me an email to soulblazor(@)hotmail.com

Thank you for your help.

More replies
Relevance 54.94%

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 54.94%

I’m using Windows 7 and I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:

"Run-time error '445':
Object doesn't support this action.

The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.

When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearch

Can you help me with this? I've searched for a solution for a long time but could not find anything which worked.

The following is the start of the macro code:

Sub UpdateTable()
Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As Variant
Dim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As Range
Dim ServRange As Range, SrcOpen As Boolean, SourceName As String
Dim FilSrch As Object, MyFilArray() As String

'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"
'Exit Sub
'Sheets("LookUp").[a12] = Now
With Application
.ScreenUpdating = False
.StatusBar = "Counting source files ..."
End With
Set FilSrch = Application.FileSearch
With FilSrch
.NewSearch
.LookIn = ActiveWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
ReDim MyFilArray(.FoundFiles.Count)
For I = 1 ... Read more

Answer:Solved: Excel 2003 Macro Doesn't Run in Excel 2007 - Help Requested

7 more replies
Relevance 54.94%

I am trying to create a code that will autofilter data on an Excel worksheet. The code works fine to filter the data, but it does not hide the AutoFilter Arrows.

Below is the code I am using. What (if anything) is wrong with the code below?
Code:

ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>", Visibledropdown:=False
 

Answer:Solved: Excel 2007 Macro -> Hide Excel AutoFilter Arrows

I noticed that the code above (in Post #1) that the AutoFilter Arrows were beinf truned off on Field 2, but none of the others.

I have played around, and done some research to come up with the code below:
Code:

Dim c As Range

For Each c In ActiveSheet.Range("A1:D1")
ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>"
c.AutoFilter Field:=c.Column, Visibledropdown:=False
Next

Does anyone have a better solution?
 

1 more replies
Relevance 54.94%

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 54.94%

Hello,

I need help to create an Excel macro that would

1. ask users to select x number of rows to be copied from one worksheet of one Excel file
2. once users have selected the rows to be copied, then the rows are copied from the one original Excel file into X number of target Excel files

Notes:
the target Excel files are all based on the same template
there is one worksheet in each of the target Excel files

in the target files, the rows should be copied from the first available empty row, going down

Looking forward to your help!

Thanks a lot.
Mzz
 

Answer:Macro needed to copy x number of rows from one Excel file into a batch of Excel files

Hi, welcome to the board.
Not much info there to get the correct picture.
Sample? Of source and template please
And not to forget, what version of Excel are you and the users using?
 

1 more replies
Relevance 54.53%

Hi,
I want to merge two excel files into a 3rd one.

The data are in the files Book1, Book2 and the result in Book3.

I know that i can use VLookup to do the above. But what i have pasted is just an example. The real excel files contains about 9 columns with at many repeating row!!!

Any help will be much appreciated.
Thx
 

Answer:Excel - macro to merge 2 excel files using a common column

Hi welcome to the board. How are you going to treat the duble items, If you want to merge 2 files adn duplication occurs here must be one cell whihc is common to let's say Book1 and Book2.

Do you have a common value? Index? Whatever you call it?
 

3 more replies
Relevance 54.53%

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