Free Microsoft Excel 2013 Quick Reference

Learn excel macros vba Results

Hi,
I'm new to VBA and macros. I've tried following instructions in various posts but nothing seems to work the way I need to.

I'm working on a vocabulary practice workbook in Excel 2007 (XP platform). The first sheet contains my first round of practice. Sheet2 is using the formula =IF(Sheet1!C13=1,"",Sheet1!A13) to copy the word in Sheet1 that I didn't translate properly. The formula is repeated down each row to call all words found in Sheet1. But as you see, if the value of the formula = 1, than the word does not appear. This allows me to concentrate on the words that I'm having a hard time to learn.

My posting is in regard the possibility to hide/unhide the rows in which the word is not "called". If the value changes to 0 and the word is copied from Sheet1, than the row would need to "unhide" automaticaly.

I have tried using some of the macros similar to what I'm trying to do but without success. I have inserted a secondary formula (lets say column D of each row of Sheet2) that will switch between 1/0 to see if it would cause the macros to work but without success.

For any possible macros describe what to do step by step please.

Your help is much appreciated

Hello all, I'm a new user on here. I am doing some ad hoc reporting for my boss and I'm trying to automate something that if done manually could take up to 4 hours (assuming there were non stop work done).

Some background:

We have a report that we download from our business system into excel. As it stands, most of our sales reps use this report to check stock on items so that they don't have to call us so much. They travel to customer sites and print off this report to take with them to use throughout the day....They don't carry laptops and the excel report is too hard to read on their Blackberries. It is a snapshot report that of course could possibly change from the time it's printed but this gives them a rough idea and we send it to them every week.

Here's the problem:

The report is only 5 columns of data, but is 25,000 rows. That's alot of trees that we're killing. So in an effort to reduce the number of pages printed out, we are changing the font size and margins, but we are also copying/cutting the data within a page break and placing it on the previous page adjacent to the data that is already there. So now we have a report that reads more like a book, and has 11 columns on one page (5 columns of data in sequence, a space, and the another 5 columns of data continuing in the same sequence).

Obviously if I manually cut and paste to get this done until I have successfully paired down 25000 rows to half that amount, it's going to take forever to do that. I need a way to automate this with a macro, but I have no idea how to do it. I am moderatly familiar with VBA and the Excel Object library (more familiar with Access) and I have been told that this could be done using the Offset property, however when looking at what's written about that property, I can't seem to wrap my head around it to write something to do what I need it to do.....I'm at a total loss.

To help you visualize what I am trying to do, I have made some references below that should plot out a picture of what I am trying to do. I have used a smaller set of data so that it won't take forever to see the pattern but essentially, if you can help with this small example, I can transpose the logic and write it for the larger report that I am working on.

32 rows of data starting in cell a1 and ending in row a32

select a5:a8 cut/paste to b1:b4
select a9:a12 cut/paste to a5:a8
select a13:a16 cut/paste to b5:b8
select a17:a20 cut/paste to a9:a12
select a21:a24 cut/paste to b9:b12
select a25:a28 cut/paste to a13:a16
select a29:a32 cut/paste to b13:b16

I want to automate this task using some type of relative reference method without having to hard code the cell references into the script.

I hope I have done a adequate job in explaining it ( although I feel like I'm totally confusing things ) I am by no means and expert and I freely admit this, and I have plenty to learn so I'm open to different approaches.

Thanks in advance for any help you can provide

just starting to learn vba and copied and modified some code from jelen's vba and macro for excel:

Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("Zero Acc Orders")
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
FinalRow = WSD.Cells(65536, 1).End(xlUp).Row
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, 8)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)
Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets("No Acc Rep Cnt").Range("a2"), TableName:="PivotTable1")
PT.ManualUpdate = True
PT.AddFields RowFields:=Array("Sales Rep"), ColumnFields:=Array("Net/Rep")
With PT.PivotFields("Sales Rep")
.Orientation = xlDataField
.Function = xlCount
.Position = 1
End With
PT.ManualUpdate = False
PT.ManualUpdate = True

i am getting a run time error '1004' "AddFields method of PivotTable class failed". the portion of code that is highlighted when clicking Debug is:

PT.AddFields RowFields:=Array("Sales Rep"), ColumnFields:=Array("Net/Rep")

what would cause this problem?

Hi,

I'm very new to the whole macro excel thing, if this is even a VBA thing. I'm learning I guess is what I'm saying.

Anyways, I'm after help really, I don't know if its possible but I have a template for excel which I want some way of when the user double clicks it, they have a drop down box or some way of inputting data so the name of the workbook which opens has the option they choose/typed in as the end name.

So for example, they open the template and it prompts them to choose a, b or c from a drop down. This then names the work book a, b or c.xls.

Is this at all possible?

Regards

Adam

Every week I get some updated files and need to summarize the information. I have been trying to make this with a macro, but all the help I can find is about data in only one column, and I have not only different columns but rows as well.
I need to get in a new worksheet the total quantity per item per site without duplicates:
This is a sample of the data, basically it indicates the items and their quantity per day and can be up to three different items in one day.
******** ******************** src="http://www.interq.or.jp/sun/puremis/colo/popup.js">*********>Microsoft Excel - Book2___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA5=
ABCDEFGHI5 Date1Date2Date5Date6Date7Date8Date96Site152D38352D38352D39352D38252D38252D38252D3937854588838 46D3A12 46D324 9 3 8 10 52D382 52D393 11 8 3 12Site252D38352D38352D39352D38252D38252D39352D39313775775514 52D393 15 5 16 52D393 17 5 Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

the new list should look, something like this:

******** ******************** src="http://www.interq.or.jp/sun/puremis/colo/popup.js">*********>Microsoft Excel - Book2___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB21=
BCDE21 ItemQty 22Site 146D3248 23 46D3A123 24 52D38232 25 52D38313 26 52D39351 27 28Site 252D38214 29 52D38314 30 52D39325 Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR. Hope I made myself clear.
I'm not really a pro on excel matters, just trying to make my job easier, I'm eager to learn of the benefits of VBA, and have done some routines mostly modifying recorded macros. Any help to point me in the right direction is greatly appreciated.
Thank you!

I am enjoying learning VBA, but I don't want to create VBA code unnessesarily (over complicate things), if there is a practical built in method that might be getting overlooked.

Question 1
Is there a built in way in Excel 2003 to sort a large number rows and columns in date order based on the date on one of the columns without repetitively having to manually select the range of cells.
Or would I need to record a macro to do this?

For example, when a new row of data is entered, can the row / column range be auto sorted in date order automatically when the date is entered?

Question 2
Any suggestions relating to sorting data that would be worth investigating?

Hi all

I am learning my way round VBA in excel at the moment and one of the areas I would like to improve is the following:

We do a download from the payroll system every month listing all sites staffing data and postings.

I am looking to create a macro that will sort the data by the site column and then every time the row within the column changes to another site it will paste all data relating to each site into a seperate workbook.

i.e

row 1 Birmingham £20.00 F Smith
row 2 Birmingham £16.00 E Taylor
row 3 Leicester £120.00 M Robins
row4 Leicester £110.00 F Jacks

I would like the macro to copy row 1 and 2 to a seperate sheet and call it Birmingham, then copy rows 3 and 4 to another sheet and call it Leicester, and keep going until the end of column A.

Is this possible.

Any help much appreciated.

I have a VBA application that users in my company use to relay project information to each other. As of now, I have the data being saved out in .csv file format to be brought back in for editing. The app also outputs html files for other users/managers to access to keep informed on these projects.

I have been reading through Mr. Excels "VBA and Macros for Microsoft Excel" and just read about XML being the new "universal file format".
My questions is: Would I be better suited to switch my application from outputting/inputting CSV files to doing so in XML file format? Would this make creating the html files/updating to future iterations of the app./etc... easier?

Note: I have no experience with XML but am always willing to learn more.....
Thank you,

I have come up with a bit of an issue lately (well...i seem to have one every few days lately - what a life on a learning curve, huh!)

I have been developing a program with about 20 different user forms in it.

It also has about 20 different reports. The reports are basically Excel Spreadsheets using the SUMPRODUCT function to total the data in different ways. The data is collected through the 20 different UserForms.

The way the program works is...there are 30 copies of the Program on a Network Drive - One for each User (total of 30 Users). I choose this method, because Excel only allows one user to edit a file at a time....and I would have had "read only isues".

I then run a "Sweeping Macro" from a Master File which opens all the other 30 Files, copies and pastes the data from the 30 individual files into the Master File...and then I run my Delete Duplicate MAcro, and then my Reports.

NOW TO MY QUESTION:
What I have noticed is, the more User Forms and VBA Coding I create, the slower my MAcro's are running.

Even though each User Form and Report are seperate from each other ( I have even used different modules for each different Report VB Code)....my Program is running slower and slower.

WHY IS THIS???

Is it because I copied/pasted and edited data/macros so many times that it is all stored in memory as junk ??

Or is it because the Larger the Program, the Slower everything becomes??

Do I MAke any sence?

P.S. sorry for all the "rambling"...its been a long day....and I think i have a few to many coffees.

Hi All, I've got a basic knowlege of Excel, formatting, macros etc etc. Learning on VBA at the mo while making a project. I have come accross a small problem.

What code do you use to go to a new row in a table of data. I've tried recording a macro, which consisted of going to the table and hitting CTRL + Down, which only took me to the last record.. not the new line!

Any help would be much appreciated!

Tom

[ This Message was edited by: tdlynn on 2002-11-13 13:24 ]

Hi all,
Being relative new to VBA, I am still having a few difficulties writing the stuff. I am basically learning everything I can from this web site, and a couple of books I have....yet I am still on a hugh learning curve.

Can someone please have a look at my code below, and tell me what I have to change in order to get it to do a Save AS and automatically default to C:MyDocuments (its not going to the default c:MyDocuments at the moment)

Ohh, I also have a second question......I am finding if I try attempt to re-save the file....with the same name (using the below macro)....the "File already Exists" Screen pops up. Then if press cancel...the macro saves, but my code comes up saying "File Not Saved". My Question is.....How do I edit the "File already Exists" code so that it doesn't come up.....or if it does come up...write in the "IF" statement = to edit it based on the results.

My Code is:

Sub SaveAsFileName()
Dim myFile As String

On Error Resume Next

Title = "Enter Name of File to be saved"

myFile = Range("d9") & " " & Format(Range("d14"), "dd mmmm yyyy") & " Expense Claim Form"

FileSaveName = Application.GetSaveAsFilename("C:MyDocuments" & myFile, "Microsoft Excel Workbook (*.xls),*.xls")

If FileSaveName = False Then
MsgBox "Your File was not Saved"
Exit Sub
Else
ActiveWorkbook.saveas Filename:=myFile
MsgBox (myFile & " has now been Saved")
End If

End Sub

Thanking you all in advance

Greg

I have an InputBox, where the required answer to a question is either Yes or No. What do I need to do to ensure the following IF statement treats YES and yes and Yes the same way, and similarly for NO, No, no.

Thanks in advance.

P.S. I'm a rookie in VBA Programming, and learning as I go along. I'm working in Excel 2007, but need my macro to be used also in earlier Excel versions.

Hi there

Just wondering if there is anywhere in Victoria (Australia) that I could purchase the "Holy Macro! It's 1900 Excel VBA Examples CD"? (or anything else relating to Excel VBA - I'm a newbie to it and want to begin the learning process.

Cheers
Griffo

Have you tried to open the workbook with Macros Disabled? This might let
you in and edit the module, if it is still a problem I suspect that the file
has been corrupted somehow and the lesson always save a backup copy of a
previous version of working code before making changes will be learn't - we
have all done it!!
Cheers
Nigel

"Ajit" > wrote in message
...
> I have an excel sheet with forms and lot of VBA code. I modified some
code, saved it and tested it ...it worked fine. I closed it and now when i
try to reopen it. It gives me an error : Invalid Data. If i try to go in the
particular Module where i made the changes it does'nt let me go in...with
same error (though i can view all other modules and code. I also tried
exporting it ...but again Mr. Invalid Error is on the gate and does'nt allow
me to do anything.
>
> I have a slightest idea that the template might have been corrupted, but
no idea why...could anyone help me understand it(if possible). (I did not
use any new variables)
>
> And any ideas.....what should i do(except redoing it).
>

----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

Hi all,

At weekly meetings, a secretary of ours takes notes of what action
items are assigned to whom. She keeps track of these delegations on a
single worksheet per project at the meeting real-time, for the sake of
speed and accuracy. This workbook has one worksheet per project with
groups of two rows representing a task within that project being
assigned to some employee.

For instance, within ProjXYZ Kevin may have two "action items" he
needs to work on. However, in ProjPQR, another worksheet in the same
workbook, Kevin may have a third "action item." I would like to write
a macro that consolidates all action items for each person on to one
tab. This eliminates the need to attach a large workbook and scan
through various worksheets looking for one's name assigned to tasks.
One tab for Dan, one tab for Ted, one tab for Susan, etc.

Ideally, the macro takes entries in this form (the project
worksheets):

WKSHT: Othello
__________________________________________________ ____
| Dan | Issue: Resolve DSP errors on Othello3.4
Chipset |
-----------------------------------------------------------------------------------------------
| 2/27/2007 | Resolution: [HE FILLS THIS IN HIMSELF
LATER |
-----------------------------------------------------------------------------------------------

And will put it onto another worksheet like this:

WKSHT: Dan
__________________________________________________ _________
| *Project* | *Date* | *Issue* |
*Resolution* |
--------------------------------------------------------------------------------------------------------
| Othello | 2/27/2007 | Resolve... | [NOW I FILL THIS IS AS I
WORK] |
--------------------------------------------------------------------------------------------------------

I am completely aware that this may be more trouble than it's worth.
Frankly, in my opinion, it is, but since I'm so new to VBA and Excel
programming in general, I think it's a good project for me to take on
just to learn the ropes. Unfortunately, while I have extended
experience with PHP and Python, the specificity of VBA is daunting,
and I'm afraid I don't know exactly how to best approach this problem.

Any guidance would be greatly appreciated.

Thanks

Hi everyone,

Does anybody have any suggestions about a good Excel book to get to learn
how to use the functions and to learn how to write macros and pivot tables
and vba code?

Thanks

This is, I realize, a database situation, but I have to work with Excel
for certain reasons.

Q?: How do I reference one worksheet to another?

My example:
I have three worksheets.
"East", "West", "All"(master wksheet).

They all have the same headers, which live in A$1:$V$2

I want worksheet All to contain the contents of East & West. As I
update East/West, I would like All to reflect those changes. Rows may
be added/deleted from East/West, so All would need to expand/contract
appropriately.

Using the information below the column headers, is there a way to post
all data from East & West into All? I don't want to post individual
cell references, especially since cells won't stay the same on
East/West as rows are added/deleted.

This may be a VBA macro, which is unfortunately beyond my skill set
these days, though I'm learning.

I have a royalty report from SAP that I need to bring into Excel. It
includes check number, payment date, amount, and check recipient.

The problem is the "check recipient" information comes in on either 1,2,3,
or 4 lines in the report. But the rest of the record information is all on
line 1. This is random and will not be the EXACT same structure each month.

Ex.
2539893 3/14/2002 55.09 Jane Doe
Anytown USA
2539894 3/14/2002 79.99 Bob Evans
2539895 3/14/2002 5.00 John Smith
111 Main Street
Anytown USA

I need the end result to look like
2539893 3/14/2002 55.09 Jane Doe Anytown USA
2539894 3/14/2002 79.99 Bob Evans
2539895 3/14/2002 5.00 John Smith 111 Main Street Anytown USA

I have very little VBA or Macro experience, but wiling to learn. Also,
could Access handle this better?

Ken,
Wow, thanks!!

Unfortunately, I'm having a small problem. I think I've followed your
instructions to the word, but I'm getting a VALUE! error in B1 where I've
entered the filepath formula.

Here is the current formula:
=MID(CELL("filename",$A$1),1,FIND("[",CELL("filename",$A$1))-1)
I've stepped through a formula evaluation and here's what I learned.
+ First it translates CELL("filename",$A$1) into
T:GeddesResumesreslist.csv.
+ Then it changes the second CELL("filename",$A$1) into
T:GeddesResumesreslist.csv.
+ Next it tries to translate FIND("[",T:GeddesResumesreslist.csv) but
winds up with #VALUE!

I hope that makes sense. I'm not sure what's going on. I don't quite
understand the purpose of the open bracket in the FIND statement.

So then I deleted the filepath formula and the hyperlink formula
=HYPERLINK(A2,A2)
worked! I just copied the formula down the column and it worked just fine.
Is that because the default file location for a hyperlink is in the current
folder?
The only problem with this method is that I have to leave column A intact. I
can't delete it, which I'd like to do because it's redundant to have two
columns that have the exact same text in them.

So, any advice?
Thanks a lot,
Jennifer

"Ken Wright" > wrote in message
...
: One way is to use the hyperlink function =HYPERLINK()
:
: Assume I have an mp3 file for example in the folder D:4mydata called
: wow.mp3 and in an excel file I have that text of wow.mp3 in say A2. Then
if
: in cell B2 i put the following formula:-
:
: =HYPERLINK("D:4mydata"&A2,A2)
:
: then I can now click on B2 and it will link straight to the file. I can
: also use a formula to get me the filepath and then use that in the formula
: as opposed to hardwiring in the path.
:
:
: In your scenario, assuming the Excel file is in the SAME folder, then
: assuming all your filenames are in Col A, starting A2, put the following
: formula in cell B1:-
:
: =MID(CELL("filename",$A$1),1,FIND("[",CELL("filename",$A$1))-1)
:
: Now assuming your names start A2, and that you want the links in say Col
D,
: in D2 put the following formula and copy down as far as needed:-
:
: =HYPERLINK($B$1&A2,A2)
:
: This should give you a list of hyperlinks in about 30 seconds vs doing
them
: one by one in 6 hours or so.
:
: --
: Regards
: Ken....................... Microsoft MVP - Excel
: Sys Spec - Win XP Pro / XL 97/00/02/03
:
: ------------------------------*------------------------------*------------
----
: It's easier to beg forgiveness than ask permission :-)
: ------------------------------*------------------------------*------------
----
:
:
:
:
: "Jeni Q" > wrote in message
: ...
: > Greetings,
: >
: > I have a user who is doing a repetitive action in a spreadsheet
: > and I'm wondering if there's a better way to do what she's doing. Let me
: > explain the process.
: >
: > Sue has a folder that contains 250-300 *.PDF files that have a naming
: > convention like such: GR005843.pdf. In this folder she also has .xls
file
: > that contains fields like Filename, Date, Last Name, etc.
: > Each file listed in the Filename column corresponds to a PDF file in the
: > folder and displays the exact title of the file, e.g. "GR005843.pdf".
: > Currently, she goes to the first data cell in the column, A2, and
inserts
: > a
: > hyperlink into the XLS file by right-clicking and choosing Hyperlink. In
: > the
: > Insert Hyperlink dialog box, Link to: Existing File or Web Page is
: > automatically selected. Look in: defaults to Current Folder (which is
good
: > because that's where the files are). The Text to Display: defaults to
the
: > text that is already in the cell. She types in the file name
: > (GR005843.pdf)
: > in the Address field and chooses OK. Now the text that was in that cell
is
: > replaced by a hyperlink to the corresponding file in the same folder.
Then
: > she moves to A3 and repeats the same steps. She does this about 250-300
: > times, depending on how many files are in the monthly batch.
: >
: > I feel like there should be a better way to do this but can't figure out
: > how
: > a recorded macro could do so. It's the getting to the next cell part
that
: > confuses me. I'm guessing it'll take some VBA code, but I'm not well
: > versed
: > in that. Can anyone provide some suggestions or advice for me? Can you
: > tell
: > me if what I'm trying to do is impossible?
: >
: > I'll be happy to answer any questions or clarify something I did not
: > explain
: > well.
: > Thanks in advance for your help.
: >
: > Jeni Q
: >
: >
: >
:
:

I just learned how to add in Excel a couple months ago and, although I'm doing the best I can with google, VBA is not easy for me to work with yet. So, while I certainly appreciate any sort of help, explanation-heavy answers would be doubly appreciated!

The excel file I've attached should explain it far more clearly than I can in words, but I'll try to summarize. I need to take a selection of cells and insert them into a table wherever text from a range show up.

Now, I should be - and have been - able to select the cells I want to insert and use the find method to find where to put them. Basically, find all instances of "packageA" and insert; I have a macro for this in the example worksheet. The problem is, I have a lot of different things to find. It needs to also insert on all the packageB's and C's and so on for a good 50 more, AND more are being added continuously. So, I can't just duplicate the macro for packageB and be done with it.

Here's what I'm using to find and insert:


	VB:
	
 FindandInsert() 
     
     
    Dim rFoundCell As Range 
    Dim LastCell As Range 
    Dim FirstAddr As String 
     
     
     
     
    Sheets("Sheet3").Select 
     
     
    With Range("B1:B1195") 
        Set LastCell = .Cells(.Cells.Count) 
    End With 
    Set rFoundCell = Range("B1:B1195").Find(What:="PackageA", After:=LastCell) 
     
     
     
     
    If Not rFoundCell Is Nothing Then 
        FirstAddr = rFoundCell.Address 
    End If 
    Do Until rFoundCell Is Nothing 
        Debug.Print rFoundCell.Address 
         
        Set rFoundCell = Range("B1:B1195").FindNext(After:=rFoundCell) 
         
        Range("InsertRange").Copy 
         
        rFoundCell.Select 
        Selection.Offset(0, -1).Insert Shift:=xlDown 
         
        If rFoundCell.Offset(-4, 0).Address = FirstAddr Then 
            Exit Do 
        End If 
    Loop 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Simply, instead of using find method to find one thing, I need to find anything that is in a range.

I imagine I can figure out this next part on my own, but, after it's done doing that it needs to go back through and clear out blank rows. Some of the inserts leave one or several blank rows at the top, depending on which type of package they have been inserted on top of.

Seems like it should be so simple but I've spent ages without figuring it out...guess I need more grounding in VBA first.

Crossposted here http://www.mrexcel.com/forum/showthr...54#post3107254, hope that's ok!


No luck finding an answer? You could always try Google.