Computer Support Forum

Excel and word macro about month end dates

Question: Excel and word macro about month end dates

=IF(15-(LEFT(TEXT(AN2,"DDMMYYYY"),2))>=0,CONCATENATE(15,RIGHT(TEXT(AN2,"DD-MM-YYYY"),8)),CONCATENATE(30,RIGHT(TEXT(AN2,"DD-MM-YYYY"),8))) ----- this was my excel formula, its a macro created with word link, my question was in this formula only I need change that the month end 30 because the macro created like this, because according to month end its 30 / 31 and 28 every time we have to change the month end, in 30 there is no need to change but in 31 and 28 we have to change mannually in word. So this I want to change Can u help me in this

Relevance 100%
Preferred Solution: Excel and word macro about month end dates

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: Excel and word macro about month end dates

Don't forget Leap Year February has 29.

3 more replies
Relevance 86.51%

I have a strange problem I hope someone can shed some light on

I want to make a new column of months from a date
I have a detached with column A as a normal date format 01/12/2006 DD/MMM/YYYY
I then in column B use the month function - so = month(A1)
it displays 12 - and if I go onto the Fx to see the expression helper / wizard
I get told that i get a number returned 1-12 here 1 = Jan and 12 =dec

so that i format the =month(a1) into a MMM-YY format and
get Jan-1900 - I'm cool with the 1900 as i have not specified YYYY but why do i get Jan returned instead of Dec

But what I'm after and maybe a simpler way is to have a dropdown on my pivot table of MM-YYYY from my date field rather than DD-MMM-YYYY

so I can choose and show month data

I'm sure I did this in the past just by using format - MMM-YYYY on a date field and it worked in the picot table OK

any help appreciated
 

Answer:Excel: Dates: Month

7 more replies
Relevance 82.82%

Hi everyone

I am new to excel and am experimenting to learn. I am trying to make a formula to count the amount of times a day of the month happens (eg. 5th) between two dates (eg. 26/09/2011 and 16/01/2012)

Its something im doing with bills i need to pay and stuff

Thank you
 

More replies
Relevance 81.18%

Hello,I am trying to write a macro to determine the percentage of dates in a column of cells (format: 30-Sep-10) that occur before the end of the current calendar year.I have tried using the DCOUNTIF function, but I think I am having problems with excel recognizing the date and working with the date in the context of the current calendar year. This is what I have, which doesn't seem to work at all. The C[-2] is the column with the dates in it:For i = 2 To Sheets.Count Worksheets(i).SelectRange("J8").Select ActiveCell.FormulaR1C1 = "=DCOUNTIF(C[-2]:C[-2],<DATE(year(now())+1,0)" Range("K8").Select ActiveCell.FormulaR1C1 = "=DCOUNT(C[-3]:C[-3])" Range("L8").Select ActiveCell.FormulaR1C1 = "=((RC[-1]-RC[-2])/RC[-1])" Range("J6").Select ActiveCell.FormulaR1C1 = "Ground Task IDs" Range("J7").Select ActiveCell.FormulaR1C1 = "due this year" Range("K7").Select ActiveCell.FormulaR1C1 = "Req" Range("L7").Select ActiveCell.FormulaR1C1 = "% complete"NextThank you for any help.

Answer:Excel Macro working with dates

Hi,I don't think that you need a macro to do this.As a test I had 1000 dates in column A in cells A2 to A1001The dates ranged from 01 January 2009 to 31 December 2011The method I used is based on counting the total number of dates and the total number of dates that are on or before 31 December 2010.Then calculate the percentage.=COUNT(A2:A1001) counts all cells containing numbers (Excel stores dates as numbers - 40373 is 14 July 2010)I used =COUNTIF(A2:A1001,"<=" & DATE(2010,12,31)) to count the number of cells containing a date on or before the 31st December this year.Putting all this into one formula I get:=COUNTIF(A2:A1001,"<=" & DATE(2010,12,31))/COUNT(A2:A1001)Then format the cell with a percentage number format.Or if you prefer the value directly use:=(COUNTIF(A2:A1001,"<=" & DATE(2010,12,31))*100)/COUNT(A2:A1001)Regarding a macro, i did this:Option Explicit

Sub datePercnt()
Dim intTotal As Integer
Dim sngBefore As Single
Dim rngDates As Range
Dim dtEndDate As Date
Dim rngCell As Range
Dim sngResult As Single

'set the range containing dates
Set rngDates = Worksheets("Sheet1").Range("A2:A1001")

'set counters to zero
intTotal = 0
sngBefore = 0

'set end date
dtEndDate = DateSerial(2010, 12, 31)

'loop through each cell in the range
For Each rngCell In rngDates
'add 1 to count of all dates
intTotal = intTotal + 1
'test date in cell against end date
If rngCell.Value <= dtEndDate Then
'if on or before add 1 to 'before total'
sngBefore ... Read more

8 more replies
Relevance 77.9%

I am trying to create an AutoOpen VBA Macro in Word 2010 files for automatically inserting the same dates into 2 different text boxes when I first open the file. Ideally, I would want 1 file to do this always for Yesterday's date (whatever that may be) and another file to do this for Friday's date (whatever that may be).

Reason for this is because our mail room gets mail the day before I get it on my desk and I need to date stamp it using a Word 2010 template where I have 2 text boxes (please see attached file--oh, doesn't seem like I can attach the file???) for Tuesday through Friday. Right now, I am using the Macros I have created (1 for Yesterday's Date and another Macro for Friday's Date which is Date() -3).

I want to use that Macro logic to put that into the Text boxes I have when it first opens? Please help me with this anyone......

It doesn't seem like I can attach the file so here is the VBA code:

Sub AutoOpen()
'
' AutoOpen Macro that places the cursor at the last position of edit (not default of top left) (will work with Protected View)
If Application.ActiveProtectedViewWindow Is Nothing Then
Application.GoBack
End If

' InsertTextBox1 Macro
Dim Shp As Shape
Set Shp = ActiveDocument.Shapes.AddTextbox( _
Orientation:=msoTextOrientationVertical, _
Left:=22, Top:=252, Width:=25, Height:=170)
Shp.TextFrame.TextRange.Text = "NMM RECEIVED: "
Set Shp = Nothing
Selection.InsertAfter Format(Now() - 1, "dddd, ... Read more

Answer:Word 2010 VBA AutoOpen Macro to Insert Dates Into Text Boxes

16 more replies
Relevance 77.9%

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

Windows 7 with MS Excel 7.

Kindly help me, its urgent.
 

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

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

2 more replies
Relevance 73.39%

Hello all,

I have been presented with a project that involves Forms that I am not able
to resolve.

Basically I have been asked to create a document template where other team
members can fill out a form that asks a series of questions, then to have
only the completed form results export to a new file.

I am aware that Word can export the form results to a text file, but when I
do this all of the questions that were on the original form are exported to
the new file.

What we are looking to achieve in the end:
If the end user is faced with 20 questions, but only 12 of the questions
pertain to the end users project, only the 12 completed form fields will
export to a new file.

I know that this should be possible with using a macro button to export the data, but that is where I need the help.

I tried searching for this exact situation, but I did not find any matches, hence the new thread.

Thank you in advance,

Bill
 

Answer:Macro help needed to export Word form results to a new file... Word/Excel

11 more replies
Relevance 73.39%

I am trying to merge data from an Excel S/sheet to a Word doc and this data includes dates which I need in the format of '28 September 2005'. I've changed the format in both Excel & Word and for some reason it still merges the dates as '9/28/05'. I've even gone to the extent of putting a character in front of the date so Excel doesn't recognise it as a date but surely I don't have to do this everytime? I have pages & pages of Word docs to go through and edit this extra character out of.

What am I doing wrong???
 

Answer:Solved: Merging dates from Excel to Word

I do beleive I just figured it out myself! LOL

Simple add one space before the first number of the date in the Excel spreadsheet. I thought this would also add the space to the Word doc but it doesn't! Problem solved.
 

1 more replies
Relevance 73.39%

I am having problems mail merging dates from an excel spreadsheet into a microsoft word document. E.G. in excel it is displayed as04-May-13 when merged it comes out as05/04/13 in the word document. How do I display it correctly???Frustrated......

Answer:mail merging dates from excel into word

Make sure you computer is configured correctly for European dates format. Control Panel > Regional Setting. Configure short Date for the format you want the dates to display.Stuart

2 more replies
Relevance 73.39%

Hello,

I wonder if it's possible to make a macro that copies one spesific line / colum from every page in a word document into an excel sheet or new word document.

IE: every 5th and 10th line from one document into another.

-John
 

More replies
Relevance 72.98%

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

I run a weekly excel 2007 macro that converts a spreadsheet to a Word document, but it stops after row 29 of data. Any spreadsheet with 29 rows or less works fine. If I have 34 rows of data, the macro hangs. See below for script. Any help would be appreciated.

Public Sub AddControls(WrdApp As Word.Application, CurRow As Integer)
Dim OptChecked As Boolean
Dim GrpName As String
GrpName = "Grp" & CurRow
'Calculate the colors based on the cell information.
CurRange = "F" & CurRow & ".." & "F" & CurRow
Range(CurRange).Select

If InStr(1, UCase(ActiveCell.Text), "X SRM") > 0 Then 'SRM
OptChecked = True
Else
OptChecked = False
End If

SetCellBG WrdApp, True
WrdApp.Selection.TypeText Text:="Governance:" & vbCrLf

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "SRM"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
End With

If InStr(1, UCase(ActiveCell.Text), "X PPO") > 0 Then ' PPO
OptChecked = True
Else
OptChecked = False
End If

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "PPO"
.Font.Name = "Arial"
.Font.Size = 8
.Value = O... Read more

Answer:Excel Macro - converting excel to word doc - stops after row 29

13 more replies
Relevance 70.93%

Hi all,
Hoping you can help with this one.
My partners Dad has an issue with his PC.
Running Windows 7 Service Pack 1 x64 NTFS, HP TouchSmart 520

He will do some work on an Excel or Word file, save it, but then when he goes back to the file another day it has sometimes lost not only the most recent data, but also previous months.
It's almost as if they are restoring themselves to a previous date.

The most recent example is an Excel file he saved 2 days ago, yet today it is showing as last being modified back in March.

I temporarily resolved his loss of data the other week by right clicking the file, going into properties, previous versions, then restoring from the last save point so that all correct data is then displayed.
Unfortunately the issue has came back on a file so something must be up somewhere.
Any ideas or where to start?
He has McAfee antivirus and Malwarebytes, will post a log from that in a second.
Thanks
 

Answer:Word/Excel files keep restoring to previous dates/lose data

Malwarebytes Anti-Malware 1.75.0.1300
www.malwarebytes.org
Database version: v2013.12.14.04
Windows 7 Service Pack 1 x64 NTFS
Internet Explorer 11.0.9600.16476
keith :: CRAZYMACHINE [administrator]
28/12/2013 12:14:21
MBAM-log-2013-12-28 (12-26-05).txt
Scan type: Quick scan
Scan options enabled: Memory | Startup | Registry | File System | Heuristics/Extra | Heuristics/Shuriken | PUP | PUM
Scan options disabled: P2P
Objects scanned: 254028
Time elapsed: 9 minute(s), 30 second(s)
Memory Processes Detected: 1
C:\Program Files (x86)\Amazon Browser Bar\ToolbarUpdaterService.exe (PUP.Optional.AmazonTB.A) -> 3232 -> No action taken.
Memory Modules Detected: 0
(No malicious items detected)
Registry Keys Detected: 52
HKCR\CLSID\{4FCB4630-2A1C-4AA1-B422-345E8DC8A6DE} (PUP.Optional.Delta) -> No action taken.
HKCR\escort.escortIEPane.1 (PUP.Optional.Delta) -> No action taken.
HKCR\escort.escortIEPane (PUP.Optional.Delta) -> No action taken.
HKCR\CLSID\{C1AF5FA5-852C-4C90-812E-A7F75E011D87} (PUP.Optional.Delta) -> No action taken.
HKCR\delta.deltaHlpr.1 (PUP.Optional.Delta) -> No action taken.
HKCR\delta.deltaHlpr (PUP.Optional.Delta) -> No action taken.
HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Browser Helper Objects\{C1AF5FA5-852C-4C90-812E-A7F75E011D87} (PUP.Optional.Delta) -> No action taken.
HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\Ext\Settings\{C1AF5FA5-852C-4C90-812E-A7F75E011D87} (PUP.Optional.Delta) -> No action taken.
HKCU\SOFTWA... Read more

2 more replies
Relevance 70.93%

Hi all,
Hoping you can help with this one.
My partners Dad has an issue with his PC.
Running Windows 7 Service Pack 1 x64 NTFS, HP TouchSmart 520


He will do some work on an Excel or Word file, save it, but then when he goes back to the file another day it has sometimes lost not only the most recent data, but also previous months.
It's almost as if they are restoring themselves to a previous date.
The most recent example is an Excel file he saved 2 days ago, yet today it is showing as last being modified back in March.


I temporarily resolved his loss of data the other week by right clicking the file, going into properties, previous versions, then restoring from the last save point so that all correct data is then displayed.


Unfortunately the issue has came back on a file so something must be up somewhere.


Any ideas or where to start?


He has McAfee antivirus and Malwarebytes, will post a log from that in a second.
Thanks
 

Answer:Word/Excel files keep restoring to previous dates/lose data

8 more replies
Relevance 69.7%

I have the following Excel Macro that auto hides any line item that states the word off.

This Macro is used in a Word document I have that contains many little Excel tabels.

Instead of having to click on each one of these tables (there is 100+), is there something I can add to this Macro to auto search the document for these excel tables 1st?

Sub AutoHide()
Dim Cell As Range
For Each Cell In ActiveSheet.Range("A1:A65")
If Cell = "off" Then
Cell.EntireRow.Hidden = True
End If
Next Cell
End Sub

Thank you so much! This is going to save me a ton of time!
 

More replies
Relevance 69.7%

I have an Excel sheet where it shows the product code in column A and name of its picture in column B.

I want to copy the sheet into Word and insert the actual picture from director into column C.

I have found a macro that seems to do the same job in Excel (ie pick up the name of the picture in column B and insert the actual picture from directory into column C. But how do I do the same thing in Word?

Sub Picture() Dim picname As String Dim pasteAt As Integer Dim lThisRow As Long lThisRow = 2 Do While (Cells(lThisRow, 2) <> "") 'Range("A6").Select 'This is where picture will be insertedpasteAt = lThisRow
Cells(pasteAt, 1).Select 'This is where picture will be inserted 'Dim picname As String 'picname = Range("B6") 'This is the picture name picname = Cells(lThisRow, 2) 'This is the picture name ActiveSheet.Pictures.Insert("C:\Users\vbayat\My Documents\vidabayat\re-market\" & picname & ".jpg").Select 'Path to where pictures are stored ''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' This resizes the picture ''''''''''''''... Read more

Answer:Does This Need Excel Or Word Macro?

Hi, welcome to the forum, I would suggest Word
Another suggestion, don't just paste your code as you did, it's unreadable.

You start with [ code ] no spaces

copy the code

and end with [ /code ]

The result is
Code:


copy the code

 

1 more replies
Relevance 69.7%

Hello,

I've created a macro that gets selected information from a series of data in one excel worksheet then places it into another one within that excel file.

I now want to be able to select the data table I have just created from my macro and transfer it into a word document by using a macro or something similar.

I'm not having much luck though, the macro I had attempted had the file directory to which the new word file would be created when running the macro but I couldn't get any further than that as it would always prompt me with an error message and highlight the file path as the error, although there was nothing wrong with how it was written or its existance.
Thanks
 

Answer:Excel to Word Macro

Did you get this sorted yet? If not, please post you code.

Regards,
Rollin
 

1 more replies
Relevance 68.88%

Can you write a macro that will copy something from excel and paste it into word?

If so how do you do it? Do you write the macro in Excel or Word?

Many thanks
 

Answer:Macro to work in Word and Excel?

Yes, this can be done. Describe in detail exactly what you are trying to do and I'll write you a sample macro. Where should the values appear in the Word document? Which cell values will you be using? If you could .ZIP and attach some sample files that would make things much easier.

The basic steps are:

1) Set reference to Word library in your VBA Editor in Excel
2) Create a new instance of Word Application within your Excel project.
3) Execute your code on the instance of Word application.
Code:
Dim wdApp As Word.Application

Set wdApp = New Word.Application

wdApp.Visible = True

wdApp.Documents.Open ("C:\Documents and Settings\DocumentName.doc")

'ADD MORE CODE HERE

WdApp.Quit
Rollin
 

2 more replies
Relevance 68.88%

Hello Everyone,

In cell C1, I want the user to type in any word (in this example I typed "desk") and then I have a Search Word attached to the macro:

------
Sub WordSearch()
Cells.Find(What:=Range("c1"), After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate

End Sub
------

My question is I want the macro to just Search in Column C and D. Not Column B or the whole sheet.

I have attached a small version of the document that has the macro.

Any input would be greatly appreciated.

TAS
 

More replies
Relevance 68.06%

Hello,

I have managed to write a macro that copies a table from excel into a new document in word, here is the code:

Sub controlword()

Dim appwd As Word.Application

Set appwd = CreateObject("word.application.9")

appwd.Visible = True

appwd.Documents.Add

Set wdApp = New Word.Application

Range("a1:j7").Copy

appwd.Selection.Paste

End Sub

I would like to make it so that the table copies into word NOT into a new document but into any open document at the location of the cursor...

Could anybody help me out with the code for this...

Thanks
 

Answer:Macro to copy a table from excel into word

You need to Set appwd to use the currently open Word Document not CreateObject.

Sub Controlword()

Dim Appwd As Object

Set Appwd = GetObject(, "Word.Application")

Set wdApp = Appwd.ActiveDocument

Range("a1:j7").Copy

Appwd.Selection.Paste

End Sub
I didn't test for the cursor location.
 

1 more replies
Relevance 68.06%

In Microsoft Office Excel 2003, I am attempting to run an Excel macro which executed successfully with an earlier version of Excel, but does not execute now. I get a Microsoft Visual Basic Compile error: Variable not defined" message at the wdWindowStateMinimize statement. What needs to be done so that the variable is recognized when I attempt to execute the macro?

Private Sub automateWord(wsTemp As Worksheet, wsList As Worksheet, FilePath As String)
'Either gets a new instance of Word or uses the existing instance of Word.
Dim myDoc, oWord As Object

On Error GoTo erroh
Set oWord = GetObject(, "Word.application")

oWord.Visible = True
oWord.WindowState = wdWindowStateMinimize
 

More replies
Relevance 68.06%

Hello guys!
I have a problem, and although I thought the solution was simple, it turned out to be not at al.

I have an excelfile, it contains multiple worksheets, (approx 20)
All these worksheets have a different layout, so the columns have a different size.
When I make a report I have to copy every single worksheet in excel and then have to paste them one by one as a picture in word. This takes a lot of time ofcourse..

So I thought let's make a macro.
I started to paste all the data of the different worksheets into one worksheet and then I could use 1 simple paste and copy into word and voila..
But this didn't worked out because the columns have a different size, so some of the text became unreadible.

Then I made a macro which copies one worksheet and then puts it into a new document in word and saves it, that worked out, but I don't know how to write the code in order to make the macro, after it has copied the first worksheet and pasted it into word, to make it copy the second worksheet and paste it into word and so on for all the worksheets

This is the code to make it copy ONE single worksheet and paste it into word,
but could someone please help me with the code so that it also copies the second worksheet and pastes it into the same word file?
Code:
Sub proWord()
Dim varDoc As Object
Set varDoc = CreateObject("Word.Application")
varDoc.Visible = True
Sheets("Rapportgegevens").Range("... Read more

Answer:(Macro?) Copy Paste Excel to Word

9 more replies
Relevance 68.06%

Hi,I have a same situation in the sheet1 contain the list of Block name (total 341), and in the second sheet the addresses (total over 10000), is it possible to mark red each cell in block sheet that occurs in address sheet. but the block name should be exactly match with the word in the address.for example block name: "Chanditala"in address : 116,Chanditala,Kolkata Pin:700053 need to highlight RED if block sheet if it is present in the address(exact)

Answer:excel macro: how to find word one sheet to another

Like I said, a simple macro does the trick:
Sub RedBlock()
'Loop through Sheet1 A2:A341
For Each b_cell In Sheets(1).Range("A2:A341")
'Search Sheet2 A2:A1000 for Block
With Sheets(2).Range("A2:A10000")
Set b = .Find(b_cell, lookat:=xlPart)
'If Block is found, color Sheet1 cell Red
If Not b Is Nothing Then
Sheets(1).Range(b_cell.Address).Interior.ColorIndex = 3
End If
End With
Next
End Sub
The only issue would be if the address cell contains words like "Chanditalao" or "kChanditala". Since those words contain "Chanditala", they would be considered a match. There is an easy fix for that, but I didn't include it just to keep the code simple. If that fix is required, replace this instruction:Set b = .Find(b_cell, lookat:=xlPart)with this:Set b = .Find("," & b_cell & ",", lookat:=xlPart)That instruction ensures that the search string (the Block) contains a leading and trailing comma, just like in your address string example.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

20 more replies
Relevance 68.06%

I have an Excel macro that creates a Word document and writes to Word. I wanted to control the page breaks so I have a loop that tests whether I can fit the next chunk of text on the same page and if not, I insert a page break. Here's the strange part. If I add a message box into the loop that says "Hi", I get the proper outcome. If I comment out the message box, I get too many page breaks. That is the only change I make. Have you ever had a message box change the results of the macro? Here's the troublesome part of the code:

If wdApp.Selection.Information(wdFirstCharacterLineNumber) + charSize > 52 Then
Call insertPageBreak(wdApp, myString)
MsgBox "hi"
End If
-----------------
Sub insertPageBreak(wordDoc As Word.Application, myString As String)

With wordDoc.Selection
.InsertBreak
With .ParagraphFormat
.RightIndent = InchesToPoints(-0.5)
.Alignment = wdAlignParagraphRight
.SpaceBeforeAuto = False
.SpaceAfterAuto = False
End With
.TypeText (points)
.TypeParagraph
With .ParagraphFormat
.RightIndent = InchesToPoints(0)
.Alignment = wdAlignParagraphLeft
.SpaceBeforeAuto = False
.SpaceAfterAuto = False
End With
End With
End Sub

More replies
Relevance 68.06%

Windows 2000
Office 2000
If I Step into Excel Macro below, I get to MS Word and it stops there. I have to go back to Excel Macro and then Step into Word Macro (ReplaceData).. it then executes correctly and rest of Excel Macro works ok as well. If I try to Run the Excel Macro it gives me an Error in Word Macro (ReplaceData). Should I be waiting for Word application to start/finish and or set the focus back to my Excel Macro? The Application.Run ("Step2_Create_GA710_Prn") is an Excel Macro and it runs ok ,but once again think I have to wait for Word Macro (ReplaceData) to complete before starting that Macro.

Thanks to all that can help.. I am a newbie and enjoy the site and other posts. Ralph

Sub Step1_Extract_Ga710_Data()
' Step1_Extract_Ga710_Data Macro
' Macro recorded 3/22/2005 by DANMIS8
'

Workbooks.Open Filename:= _
"C:\Documents and Settings\DANMIS8\My Documents\Ga710\Base.Wk4"

Range("A8").Select

Range(ActiveCell, ActiveCell.End(xlDown)).Select
Range("A8:N8", ActiveCell.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Copy

Set wordobj = CreateObject("Word.Application")
Application.ActivateMicrosoftApp xlMicrosoftWord
wordobj.Application.Run ("ReplaceData")
Application.Run ("Step2_Create_GA710_Prn")
Application.Quit

End Sub

Here is my Word Macro Below..
Sub ReplaceData()
'Kill "C:\Documents and Settings\DA... Read more

More replies
Relevance 67.24%

Good Morning guys,

This is my first post on Tech Support Forums!

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

Now, on to my question!

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

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

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

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

Kind Regards,
Anthony
 

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

7 more replies
Relevance 67.24%

Using Excel 2000, I want to write a macro that opens a specific word doc. I can manage in VB to open a blank word doc...but what is the command for a specific document?

thanks
 

Answer:Excel 2000: Macro to Open Specified Word Document

Sub OpenWordDoc()

Dim AppWord As Object
Set AppWord = CreateObject("Word.Application")
AppWord.Visible = True
AppWord.Documents.Open Filename:="c:\path\filename.doc"

End Sub
 

1 more replies
Relevance 67.24%

Hello, would you please assist me in solving this problem: I have an Excel object (Excel.Sheet.1) in MS Word document. I'm trying to write a Visual Basic macro for MS Word to change the data in cell A1 of this table. How can I access the cell? I can only activate the table for example like this: ActiveDocument.Fields(1).OLEFormat.Activate - this leads to the same result as double clicking on the table object...

I know how to access MS Word tables, but the problem here is that I have to work with an Excel table.. I suppose only an Excel macro can access it and modify it... And if I write an excel macro to do so, how can I call it from my MS word macro script? Does solving of this problem involve OLE, DDE...? Maybe I should run the Excel application to modify the table and then close it.. so far I have no success in doing that.. this has to be implemented in a single MS word macro.

Thank you!
 

Answer:Accessing excel worksheets in a word document (VB macro).

9 more replies
Relevance 67.24%

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

Is this possible?
 

Answer:Macro to Export Excel Data to New Word Documet

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

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

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

1 more replies
Relevance 67.24%

I have a worksheet where column A uses data validation, from row 1 to row 50, to allow the user to select from two options. I need a macro that, when option A is seelected in say cell A1, cells B1, C1, D1, E1 and F1 will be protected and if Option B is selected it will unlock them again. I need it to be able to lock the cells in the row depending which row the Option is selected in.
I have been trying something with an event change but I can only get it to lock for one one row.
Hope this make sense.

Thanks,

Timbo2000
 

Answer:Excel Macro to protect cells in row if a certain word is selected

Try out the sample Code below:


Code:

Sub Macro1()
'
' Macro1 Macro
'

Dim wrkSheet As Worksheet
Dim xrow As Long, xcol As Long
Dim opt

ActiveSheet.Unprotect

Set wrkSheet = ActiveSheet
Range("A1:A100").Select
Selection.Locked = False
Range("A1").Select

xrow = ActiveCell.Row
xcol = ActiveCell.Column
opt = Range("A" & xrow).Value

If opt = "B" Then
Range("B" & xrow & ":" & "F" & xrow).Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Else
Range("B" & xrow & ":" & "F" & xrow).Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End Sub
 

3 more replies
Relevance 67.24%

Hi,

I have a requirement to populate a word document with excel data, and don't want to use links. In the attached files I have adapted a macro provided on www.datawright.com.au which is perfect except for one issue.

I have emailed the original author but haven't had a response so though I would try you guys.

The macro as written copies and pastes data into bookmarks, based on a reference table in excel telling word what data goes where. The problem is, if there are other bookmarks in the document, the macro pastes over those bookmarks with the last data copied.

Ideally I'd like the macro to only update those bookmarks identified in the reference table in excel. One side issue is that I'd still want to be able to delete bookmarks in word (so the total number of bookmarks in word may be less than that in excel). Any ideas?

Thanks in advance.

Ben

Answer:Macro excel named ranges to word bookmarks

Here is the macro code for those not wanting to download files:

Sub Populate_Fields()

Dim objExcel As Object, _
objWbk As Object, _
objDoc As Document
Dim sBookmark As String, _
sWbkName As String
Dim sRange As String, _
sSheet As String, _
sType As String
Dim BMRange As Range
Dim bmk As Bookmark
Dim i As Integer, _
j As Integer, _
k As Integer, _
bmkCount As Integer
Dim vNames()
Dim vBookmarks()
Dim dlgOpen As FileDialog
Dim bnExcel As Boolean

On Error GoTo Err_Handle

Set dlgOpen = Application.FileDialog( _
FileDialogType:=msoFileDialogOpen)
bnExcel = False
Do Until bnExcel = True
With dlgOpen
.AllowMultiSelect = True
.Show
If .SelectedItems.Count > 0 Then
sWbkName = .SelectedItems(1)
Else
MsgBox "Please select a workbook to use for processing"
End If
End With
If InStr(1, sWbkName, ".xls") > 0 Then
'proceed
bnExcel = True
Else
MsgBox "The file must be a valid Excel file. Try again please..."
End If
Loop

Application.ScreenUpdating = False

Set objDoc = ActiveDocument

'check to see that the Excel file is open. If not, open the file
'also grab the wbk name to enable switching
Set objExcel = GetObje... Read more

4 more replies
Relevance 67.24%

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

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

12 more replies
Relevance 66.83%

How do you make a macro that does the following:

go to next cell down in Column D
copy contents (a single word) of that cell
select entire Column A
find first instance, in Column A, of the word that was copied from Column D, above, (the word is part of a sentence in a single cell in Column A),
underline just that word (not the whole sentence) in Column A
loop

 

Answer:Excel 2007 macro - locate specific word & underline it

Hi Brad, welcome to the forum

I just recorded a amcro that does that and translated it to vba code
Code:

Function ULinetxt(cVal As String, uStr As String)
Dim tp1 As Integer, tp2 As Integer
If InStr(1, LCase(cVal), LCase(uStr)) > 0 Then
tp1 = InStr(1, cVal, uStr)
tp2 = Len(uStr)
With ActiveCell.Characters(Start:=tp1, Length:=tp2).Font
.Underline = xlUnderlineStyleSingle
End With
End If
End Function

Code:


Sub test()
Call ULinetxt(ActiveCell.Value, "underline")
End Sub

You could use this function in vba macro and it's done

Select a cell with your data string and rin the test macro, replace the word "underline" with the text you want underlined and the first occurrence (if found) will be underlined, case is not matched.

Let me know it it works for you.
 

1 more replies
Relevance 66.83%

Okay, this maybe a tough one...

I have a list of registered attendees for a training session. I would like a button for each registrant that auto populates an Word invoice template and confirmation letter template - Conf. letter fields: first name, last name, company, address 1, address 2, city state/prov. and country. Invoice fields: first name, last name, company, address 1, address 2, city state/prov. and country. Session name, date range, cost...

I would also like to use the same list to populate a registrant booklet that is also an existing template in Word - first name, last name, company, email address and phone number. I read on another post that we could possibly do an auto save to a specified location when it populates the template as well. Then we have name tags, book labels and certificates that also need to be populated from the same list...

Anything to save some time and reduce errors resulting from human interference.

T Y I A
Bubbs
 

Answer:Excel macro to populate existing word documents and booklets

Why do you need a macro to do this? Have you looked into a "Mail Merge" ??

Rollin
 

1 more replies
Relevance 66.83%

Hi,
I have posted this question with Microsoft Technet but no answer yet.
Based upon the following artikel:
http://www.microsoft.com/technet/security/bulletin/MS01-050.mspx

Because of a question in the Business Applications forum there was a vbs script to run open an excel workbook and execute a macro.
What surprised me is that it absolutely disregards the security settings and in no way asks if you wish to allow or disable macro's.
I tried if in Office 2003 (not yet with 2007) and any level I set security is works, no prompt.
The macro is unsigned and I tried it at work where security is quite strict.

Any ideas? Leak in Microsoft Office security?
 

Answer:Solved: Overrule macro security in Excel and Word with vbs script

K:
The date of the TechNet bulletin you cited is from the year 2001; updated 2003.

The article states: "Microsoft Knowledge Base articles Q306603, Q306604, Q306605, Q306606 discuss href="http://support.microsoft.com/directory/question.asp?sd=gn&fr=0">Microsoft these issues and will be available approximately 24 hours after the release of this bulletin. Knowledge Base articles can be found on the Microsoft Online Support web site."

See,for example: http://support.microsoft.com/kb/306603

http://support.microsoft.com/kb/306604

http://support.microsoft.com/kb/306605

http://support.microsoft.com/kb/306606

I hope this reduces your concerns.

RF123
 

2 more replies
Relevance 66.83%

Hi guys,

I have a question that has stumped me so far. Here is the scenario: I am dealing with a spreadsheet that has several tabs on it. In each tab, there are several word, powerpoint, and pdf documents that are embedded as objects. My task is to write a macro, that will for each document (keep in mind its an embedded object that needs to be opened), open it with the appropriate program, and save it using a pre-assigned name (the name of the column heading and the row it is in) to a predetermined folder path. This process needs to be duplicated about 200 times. I'm not sure if this is even possible, but I know enough about vba to believe that it is. I've been working on it since yesterday, and I can't seem to tie the pieces together. Can anyone help on this?

Thanks!
 

More replies
Relevance 66.83%

I am using Excel 2003 to create a database of hyperlinks to different paragraphs and then through the use of a macro I can create word documents. I inherited this macro from a different department and want to customize it for my needs. That being said I have no idea how to edit a macro this large and complex. What's the best way for me to post the macro and my questions, through email, message through this site, post it in this window? The macro is about 15 pages in length. Thanks.
 

Answer:Excel 2003 - Macro for creating word document question

First, welcome to the forum!!!

The best way for one of the Excel gurus to work on your problem would be to post your file, after you have removed any confidential data (make a copy and do the editing in it). If it is too large, zip it first (and zip is preferred to rar).
If you are unable to post the file (there seems to be a problem at times with people who don't have very many posts being able to attach files) just send me an email via my profile (include the URL link to this thread). I will respond to you and then you can send it to me, after which I will post it for you.
 

1 more replies
Relevance 66.42%

I have no idea if this is even possible, but I would like to create a spreadsheet that auto-populates a series of dates based on one date that the user enters. Let's say I have a goal date to complete a project, and for every project I work on, something needs to happen 6 weeks prior to the goal date, another thing needs to happen 4 weeks prior to the goal date, and something else needs to happen 1 week and 5 days before the goal date. I would like to set up 4 cells in Excel: one for the goal date, and three for the various things that need to happen leading up to that goal date. I would like to be able to enter the goal date into the main cell and have the other three cells auto-populate with the correct date based on what I enter in the goal date. For example, if I enter a goal date of 11/01/2016, I would like the other three cells to automatically populate themselves with: 09/20/2016 (6 weeks prior), 10/04/2016 (4 weeks prior), and 10/20/2016 (1 week and 5 days prior). Is this possible? Can Excel return dates in that way?

Answer:How can I auto-populate dates in Excel based on other dates?

Sure, it's easy. Excel internally stores Dates and Times as numbers. Each day is a whole number and time is the decimal portion of that day. See here for a pretty good explanation of how Excel deals with Dates and Times as well as links to all the various Date and Time functions that Excel offers:http://www.cpearson.com/excel/datet...As for your question, simply subtract the number of Days required from the cell with the Goal Date.With 11/01/2016 in A1, use this to return 9/20/2016:=A1-42(42 is 6*7, or 6 weeks.)I'll leave the other two formulas to you. ;-)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 66.01%

I am using MsAccess 2010 and have a table with thousands of dates called [Date Closed]. I want to create another field and call it End of Month and, based on the "Date Closed" field, update the End of Month field with the correct month-end date for that month(i.e. either the 31st, 30th or 28/29). I want to call this function from an update query to update the thousands of records with the correct end of month date. In my update query, I have an "Update To:" called EOMDate("strDate").

In the function, I have:

Public Function EOMDate(strDate As String)
Dim strDay As String
Dim strMonth As String
Dim strYear As String
Dim MyDate As String
MyDate = [Date Closed]

Select Case Month(MyDate)
Case 1, 3, 5, 7, 8, 10, 12
strDay = 31
Case 2
strDay = 28
Case 4, 6, 9, 11
strDay = 30
End Select

strMonth = Month(MyDate)
strYear = Year(MyDate)
strDate = strMonth + "/" + strDay + "/" + strYear

End Function

Unfortunately, I can't seem to get it to work. Any suggestions would be greatly appreciated.
 

More replies
Relevance 65.19%

I am working in Excel and would like to open, print, and then close an embedded Word document with a button.

Can anyone help?

Thanks for your time!
 

Answer:Solved: Excel Macro to Open, Print, and Close an Embedded Word Document?

16 more replies
Relevance 65.19%

Does anyone here know how I would go about calculating the number of firsts of the month there are between two date fields in access 2010?Thanks

Answer:Calculate how many firsts of the month between two dates

There are not too many (if any) Access users that frequent this forum. We see Access questions, but not too many (if any) answers.Excel, yes...Access, not so much.You may want to try elsewhere instead of waiting for an answer here.Good luck!DerbyDad03Office Forum ModeratorClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 65.19%

I have 50,000 cells with dates from Jan 2011-sept 2012. All i want is to group them by month (Jan 2011, feb 2011...sept 2012). How do i do that?

Answer:How to look up the long dates and return just the month?

What is the actual format of the Dates?You could use the MONTH() function, in a new blank column, to return just the Month,then Sort on the new Month column. MIKEhttp://www.skeptic.com/

2 more replies
Relevance 65.19%

Hi, I have a spreadsheet that tracks Hits and Player rankings based on the Hits. Each month is contained on a different worksheet.I need a formula that tells me how many places up and down in the rankings a player fell or increased each month.Is this even possible?

Answer:Excel Tracking movement in Ranking from Month to Month

There are multiple ways to reference a cell to the left of a value.One method is to use OFFSET and MATCH:=OFFSET(Sheet1!$B$2,MATCH(Sheet2!B2,Sheet1!$B$2:$B$10,0)-1,-1)This formula will MATCH the value from Sheet2!B2 in the range SHEET1!$B$2:$B$10 and return position of the player number in that list. It will then use that number as the rows argument (after we subtract 1 from it) for the OFFSET function. -1 as the cols argument will reference the column to the left.Another method is SUMIF:=SUMIF(Sheet1!$B$2:$B$10, Sheet2!B2, Sheet1!$A$2:$A$10) Since there is only one occurrence of each player number in the range, the SUMIF will only return a single rank value for each player.You could subtract 2 SUMIF's to get the change in Rank directly.=SUMIF(Sheet1!$B$2:$B$10,Sheet2!B2,Sheet1!$A$2:$A$10) -SUMIF(Sheet2!$B$2:$B$10,Sheet2!B2,Sheet2!$A$2:$A$10)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

11 more replies
Relevance 65.19%

Dear Friends,

I am unable to sort the column from oldest month to latest month in attached excel.

Tried various formula but not working.

Please find attached excel.

Thanks in advance for help.

Regards,
Rashi G
 

More replies
Relevance 64.37%

Hey all. I'm trying to create a budget. Of course, the due dates of my bills all occur at different times of the month. My paycheck comes every week, so sometimes I pay with the 4th check, sometimes the 5th, depending on the number of days in the month.How do I write an "IF" statement so that IF the date at the top (formatted "3-Mar-17") is March 3rd, and before March 3rd, AND less than seven days prior, to enter "1000" (or whatever my rent is?Thanks!

Answer:Monthly Dates with Differing # of Days each Month

I don't understand what you are asking.IF the date at the top..."At the top of what?"(formatted "3-Mar-17") is March 3rd, and before March 3rd, ...."How can the date be March 3rd and also be before March 3rd?"AND less than seven days prior...Prior to what?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

2 more replies
Relevance 63.96%

I am having a problem sorting by the date which contains the month and the year. Here is my Field Description:

Format$(Invoices.[Processing Date],'mm yyyy')

I know you can sort ascending and descending in the sort field, but this does not sort correctly as it starts with the month. Can anyone give me any ideas?

Thanks,

 

Answer:Access 2000: Sort Dates by the Month and the Year

16 more replies
Relevance 61.5%

I need a macro that will enter the dates on the tab like this... Wed June 01, etc. And I need it to keep going till the end of the year. HELP

Answer:macro to enter dates on tab

In a new workbook with 3 sheets, this code will delete sheets 2 & 3 and then add and name sheets starting with Wed June 01 and ending with Sat December 31.Sub DailySheets()
'Turn off alerts so that sheets can be deleted without warning
Application.DisplayAlerts = False
'Delete Sheet 2 and 3
Sheets(Array("Sheet2", "Sheet3")).Delete
'Set Start date as 5/31/2011
nxtDay = DateSerial(2011, 5, 31)
'Increment date, rename sheet, add new sheet
Do Until nxtDay = DateSerial(2011, 12, 31)
nxtDay = nxtDay + 1
nxtSht = nxtSht + 1
Sheets(nxtSht).Name = _
WeekdayName(Weekday(nxtDay), True) & " " & _
MonthName(Month(nxtDay)) & " " & _
WorksheetFunction.Text(Day(nxtDay), "00")
ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)
Loop
'Delete extra sheet
Sheets(Sheets.Count).Delete
'Turn alerts back on
Application.DisplayAlerts = True
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more replies
Relevance 60.27%

Hello everybody,I have 10 excel workbooks each with numerous worksheets within each, labeled 'stock 1' to 'stock 45' for example. Now within each worksheet, i would like to delete the rows which contain specific dates around christmas (ie. 24/12/##, 25/12/##, and 26/12/##). The dates start in column A row 3.Does anyone out there now now to do this quickly via a macro of some sort, as doing it manually would take about six months i reckon!Thanks very much.

Answer:Delete rows with specific dates using macro?

Try this in a backup copy of your workbook. Since it deletes rows, and can't be undone, test it in a backup copy first.Sub NoMoreChristmas()
For shtNum = 1 To Sheets.Count
With Sheets(shtNum)
lastRow = .Range("A" & Rows.Count).End(xlUp).Row
For numRow = lastRow To 3 Step -1
If Month(.Range("A" & numRow)) = 12 Then
If Day(.Range("A" & numRow)) > 23 And _
Day(.Range("A" & numRow)) < 27 Then
.Range("A" & numRow).EntireRow.Delete
End If
End If
Next
End With
Next
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 60.27%

I find that macros work great for certain queries and automating emailing those queries.

But I'd like to have them automatically run if possible (monthly), is that something anyone has done before?

I haven't seen anything in the Macro design that does this, but I might just be overlooking it.

Thanks
 

Answer:Access macro - automatic monthly run dates?

This should be possible in VBA rather than a macro, but anything you do will rely on the Database being "open".
 

2 more replies
Relevance 59.86%

I'm trying to find the condition to send a query once a month in an Access query. I've got the one for the one day a week but I can' seem to find or figure out the one for once a month.
 

Answer:Need condition to run macro in Access once a month.

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

How do you keep a macro running until you reach the end of a page? Currently I just hold down the control or Alt keys. I have long documents so holding down the Alt + whatever key is not always practical.

Here is a sample macro. How do I make the macro run until the macro reaches (in this case) the top of the page?

I think the "do until" feature is used in the code but I do not know the syntax for "do until".

Code:
Sub DelimitBoldSentenceOnRightWithATab()
Selection.Find.ClearFormatting
Selection.Find.Font.Bold = True
With Selection.Find
.Text = "*"
.Replacement.Text = ""
.Forward = False
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.TypeText Text:=vbTab & vbTab
Selection.MoveUp Unit:=wdParagraph, Count:=1, Extend:=wdExtend
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.TypeText Text:=vbTab & vbTab
Selection.MoveLeft Unit:=wdCharacter, Count:=3
Selection.Find.ClearFormatting

End Sub

Thank you in advance for your replies.
 

Answer:Word Macro: How to keep macro running until end of page is reached

You can evaluate the current page number using the following property:

Selection.Information(wdActiveEndPageNumber)

Just use a loop and have it execute while Selection.Information(wdActiveEndPageNumber) = 1

Can you post your sample document?
Regards,
Rollin
 

2 more replies
Relevance 58.63%

Hi All,

Can anybody write for the script for filtering the current month data from an excel sheet, irrespective of the month that we open the file. i.e. once I press the button assigned to macro, I need to filter records of current month from the DATE field that contains records of all months.
 

Answer:Solved: Macro for filtering current month data

16 more replies
Relevance 58.22%

Hi
I'm lookiong for a simple macro that will do the following

Open when excel starts
and select a TAB based on the date (month & year)

I have a calendar month on each tab - although I may change this to one work sheet later

I would like a macro to auto open to
1) the month Tab based on current month

If its July 2012 then it opens the Tab called Jul 12
if is aug 2013 then it opens a tab called Aug 13 tab
etc
for all months - I can change the name of the TAB if that helps make it work in a loop - to minimise the code
so something like find the current month Year then append those to create a name for the TAB

OR

goes to a specific cell in a sheet if i have the calendar all in one sheet
so again I can add the text in a cell - so the it can just look up the date and find the cell - rather than code for all likely cells

so something like in cell A20 have Jul13
and then search for that - if its Jul 13

I'm playing with some ideas for a work schedule and report schedule sheet to be available via a website for people to view - BUT i have very limited functionality with the website as its based on SharePoint shell

Office version is 2007 , but i may test on 2003

thanks
found some code

i have found some code which will open the worksheet and highlight the cell with the date in

How do i search through all tabs so i dont need to specify the tab

Code:

Private Sub Auto_Open()
'first make sure the correct
ThisWorkbook.Worksheets("Sheet1&quo... Read more

Answer:Solved: Macro to open a specific worksheet based on month

6 more replies
Relevance 57.4%

For some reason as I have added dates to cells, they are not getting converted to the Date type I have selected: 15/03/2009I type in my entries in this format:March 15, 2009 (usually it converts immediately but not this time)I've tried selecting the columns of cells with dates, Select>Format>Cells>Date and Type but nothing changed (although one date was converted to 03-15-2104Any suggestions?

Answer:converting excel dates in Excel 2003

Dat's cuz they're probably not being recognized as dates by Excel. My guess from afar is that the cells are formatted as Text and aren't accepting any other formats. Excel is finicky like that sometimes.Try this with one of your stubborn "dates"...let's assume it's in A1=DATEVALUE(A1)Now format that cell anyway you want.Then do a Copy...PasteSpecial...Values to eliminate the formula.

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

Hi,

Recently, I'm having slow file loading while opening ms word files and excel files directly from windows explorer. It takes a minute to load, however it opens immediately if I open .doc files or .xls files directly from their application software. It also take a minute for MS Word and Excel software to shut down.

I have run my antivirus (Symantec) and MBAM but they all show no virus was found. I'm not sure what should I do next, please advice.

Answer:Microsoft Word & Excel - problem with opening files in Word and Excel

What version of Word & Excel?

6 more replies
Relevance 55.35%

Hi,
Don't mind the curious ones, but I'm one of them. So anyway, almost one year ago now, the Word macro virus W97M/Marker.C was running rampant around Gordon College, with many teachers and students alike being infected. I was even infected with it thanks to one of my professors posting an assignment description document that had it. I was running AVG 9.0 at the time, and it was flagged many a time over. I think there were like six or so instances. My memory may be flaky, as my laptop has undergone many reformats since then, but what I do remember is trying to ignore AVG's warning, due to the fact that AVG has given me many false positives when I used it. The message Word gave me was that the document couldn't be opened because it wasn't available. But the strange part of that is that AVG had not removed anything from the file. I can think of two reasons why the file wouldn't run with the virus in it. Tell me which, if either, is correct. One is that Microsoft has since changed the macro format from 2003 and earlier versions of Word simply due to the many viruses that once existed for it, or two, that message was a very convoluted way of telling me that Windows 7 no longer supports Virtual device Drivers (*.vxd) as I was able to open the file after it had been cleaned up by Eset NOD32. Any knowledge you folks have would be very interesting, as I have always been curious about the very odd weekend of February 12, 2010. Now don't get me ... Read more

Answer:random curiosity about MS Word macro viruses and why (thank the Lord), they don't work in Word 2007/2010

I don't know the particulars of the macro virus you mention, but with Microsoft Office 2003, a significant change was implemented that effectively stopped the propagation of macro viruses.

Prior to Office 2003, macros were enabled by default, and opening a document with macros and auto-execute meant the macros were automatically executed (and thus the virus could infect and propagate).

With Office 2003, macro security was introduced. With a Microsoft Office 2003 default install, macros need to be signed with a certificate issued by a trusted root CA. Macros that are not signed will not be executed. Users have to explicitly lower the macro security level for unsigned macros to execute. Almost all macro viruses were unsigned.

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

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%
Question: Dates in Word

Dont use Word a lot lately, and have forgotten simple thing. How do I prevent the date of a letter changing to the current date when opened. e.g. If I saved a document on 5th Jan 1999, it will change to todays date when opened. This is no good if I need to know the date it was written. Cheers...whiz

Answer:Dates in Word

if you right click on the document icon eg barryoneoff.doc click properties it should give you date created date modified and date it was last accessed hope this is of some help

3 more replies
Relevance 54.94%
Question: Word doc. dates

Hi,

I am having trouble using Word 97. When I save Word documents on my hard drive and reopen them again, I notice that the original date when these documents were saved is changed to the current date. How do I prevent this from happening? Now I never know the initial date these documents were composed.
 

Answer:Word doc. dates

9 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
Relevance 54.53%
Question: Dates and Excel

I have a sheet in excel which I input my daily results. Some dates or blocks of dates are missing as I am unable to gather the data. (vacation, travel, etc)Column A is the date (mm/dd/yyyy)Column B is the result (0.00)I have another sheet with the following:Column A has a list of all dates (month and day only) (366 rows) Columns B through M have the year in row 1I need to lookup the month and the day and attach the year based on the column year (this is easy)=VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2)BUT I need the exact date data returned. VLOOKUP returns the closest lower value.If no exact date then return 0 or "".Is there an easy way to do this in Excel without writing code?Thanks for helping in advance!

Answer:Dates and Excel

If you want an exact match, you need to use FALSE or 0 in your VLOOKUP as the optional range_lookup argument:=VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2,0)Review the VLOOKUP Help file to see the options for this argument.If an exact match isn't found, this will return #N/A, so wrap the formula in an IF(ISNA()) function to get a "" of 0 if the VLOOKUP evaluates to #N/A. This will return "":=IF(ISNA(VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2,0)),"",VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2,0))

4 more replies
Relevance 54.53%
Relevance 54.53%
Question: Excel Dates

Eldest daughter driving me mad.At business she is working on a database in Excel.She tries to shorten the DOB entry as for example23/11/29 for someone born in 1929 but it comes out as 23/11/2029 yet when she enters 23/11/31 it comes out as she wants it as 23/11/1931. Is there something we need to format in Cells/Date?Brambles

Answer:Excel Dates

and have the same problem! Using excell 2000 and my cells are formatted correctly. Very strange, however there are lots of very helpful people on this site and here's hoping.Taffy

3 more replies
Relevance 54.53%
Question: excel dates

Filename: SysInfo.exe
Full Path: c:\Users\JIMBO\AppData\Local\Microsoft\Windows\INetCache\IE\RBAGCWGH\SysInfo.exe

____________________________

____________________________
Developers
Not Available

Version
1.0.0.2

Identified
6/27/2016 at 5:17:31 PM

Last Used
Not Available

Startup Item
No
____________________________
Many Users
Thousands of users in the Norton Community have used this file.

Mature
This file was released 4 years 8 months ago.

Good
Norton has given this file a good rating.
____________________________
Source File:
sysinfo.exe

____________________________
File Thumbprint - SHA:
8243b4ea661b060fe8cf4babc11ab5f51eadd28a0c9d66303183e8eceace8234
File Thumbprint - MD5:
Not available
 

Answer:excel dates

What is your problem and/or question?
 

2 more replies
Relevance 54.53%
Question: Excel and dates

I have two columns of dates, A and B. Column B is just As date plus 30 day. What i want it to do is, whenever i update column A's date, i dont want column B's date to change. how do i do that??

Answer:Excel and dates

i dont want column B's date to change.Remove the formula that updates B?MIKEhttp://www.skeptic.com/

24 more replies
Relevance 54.53%
Question: Excel Dates

I have been using this formula to let clients know when their bills are due:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+90)
this ends up looking something like this: 1/1/04
however, I have had a request from them to have it look more like this: 1-Jan-2005.
Any help would be greatly appreciated. Thank you.
 

Answer:Excel Dates

12 more replies
Relevance 54.53%

I've got a strange one, I think !!!Current date is 16 Jan 2005, and in my module I say Dim CurrDate As LongCurrDate = Format(Now(), "00000")This sets CurrDate as 38369Later in the module I want to compare this date with one from a cell ...Dim MyNewDate As LongMyNewDate = Range("N" & MyStoreRow) ... value is 17 Jan 2005The value being returned in MyNewDate is 38369, not, as I would expect, 38370 ...In another worksheet, the new date in Nxx is 18 Jan 2005, and this returns a value of 38370, not 38371 ... Any ideas as to what's happening here ?!?! Is my Current Date wrong ? Is this a known 'problem' ?!?!?

Answer:Dates in Excel VBA (again !) ...

I'm not sure what is goung on but you would be better off working with Dates rather than LongsSub test()Dim mydate As Datemydate = Now()MsgBox mydateEnd Sub

6 more replies
Relevance 54.53%

I have a column of calendar dates (A1:A100) that do not include Saturdays or Sundays. I have certain production times for various products that I must base off of these dates and am having trouble counting an amount of days that doesn't include any Saturdays or Sundays in my count. Example below.

10-02-2006 I need to add 18 production days to this date. Workdays only being Mondays-Fridays, I need the answer of the formula to equal 10-25-2006.

Can anyone assist?

Ron
 

Answer:Need Excel HELP with Dates

7 more replies
Relevance 54.53%
Question: Excel and dates

Hi All,I am updating a old spreadsheet and wanted to add some work days down the side of it. The problem I have is that I only want Mon-Friday dates to be listed. For example....12/3/201212/4/201212/5/201212/6/201212/7/201212/10/2012etc.....For some reason I can't get it to work right. In this example you can see that the first work day for Dec is the 3rd and that the 8th and 9th are passed over because they are a weekend. I know I have done this before I just can't remember how I did it. I was able to click on a cell and drag down to highlite a months worth of cells and put in a formaula in the first cell and then all the dates would just show up below it. Anyone have any ideas?ThanksSpoiler

Answer:Excel and dates

Quote from: Spoiler on December 14, 2012, 10:10:41 AMI know I have done this before I just can't remember how I did it. I was able to click on a cell and drag down to highlite a months worth of cells and put in a formaula in the first cell and then all the dates would just show up below it. Are dates for all rows part of the existing data? If so, then one approach might be to add a column to show the day of the week and then use a Filter to show all days except Saturday and Sunday.

6 more replies
Relevance 54.53%

I'm trying to set up a spreadsheet so that each time I open it, the time between two dates is calculated. The two dates would be today (the day I open the spreadsheet) and a date about 10 years away. Any help would be appreciated.ThanxDD

Answer:Excel - between two dates

For examplein A1 30/03/2017in B1 =TODAY()in C1 =DATEDIF(B1,A1,"d")

10 more replies
Relevance 54.53%
Question: excel dates tip

Can anyone let me know the formula for coverting a date held in excel as yyyymmdd into dd/mm/yyyy.Many thanks

Answer:excel dates tip

Assuming that is a text value try=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))where A1 holds the text date.

2 more replies
Relevance 54.53%
Question: Excel dates

I have a friend from california he has got office over here in europ but because of work he needs too change his dates in excel too american format how can i do this?

Answer:Excel dates

Hopefully this guide will help.

2 more replies
Relevance 54.53%

Hi
I had a macro running in Word 2007, which simply removed paragraph breaks and replaced them with spaces. It did this for the selected text, and then asked whether the user wanted to continue with the rest of the document. To this I mostly selected NO.
In 2010 the macro replaces throughout the whole document without any prompts. I checked the macro on a Word 2007 machine and the code is the same as the new macro in Word 2010.
Can anyone suggest how to overcome this bug in 2010?
Thanks, Garry

The macro code is below:
Sub RemoveParas()
'
' RemoveParas Macro
'
'
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^p"
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindAsk
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub
 

More replies
Relevance 54.53%

I have a Word database file that is 16 columns wide and several hundred rows long.

With the database open:

1. My macro opens a new document (Which I’ll call the “Working Doc”)
2. Activates the database file
3. Captures the information from all the cells in row 1
4. Activates the Working Doc
5. Inserts a Word file at the end of the document based on the information in one of the cells. This file is composed of a couple of paragraphs and a table
6. Inserts the information gathered from the database file into the table
7. Activates the database file
8. Captures the information from all the cells in row 2
9. Activates the Working Doc
10. Inserts a different Word file at the end of the document based on the information in one of the cells. This file is also composed of a couple of paragraphs and a table. All of the inserted files are a couple of paragraphs and a table
11. Inserts the information gathered from the database file into the table
12. And so on until the end of the database is reached.

I have been using this macro for several months with no problems when the database table was only a couple of hundred rows. The database table for the last couple of projects has been several hundred rows instead of a couple of hundred rows.

When I run the macro with the large database, it goes through about ¾ of the database, starts to really slow down and eventually freezes Word.

I can split the database in half and run the macro twice and then paste the 2 documents together bu... Read more

Answer:Word 2002 long macro freeses Word.

It's a very interesting idea to use Word for database handling. It's like trying to hit a nail with a knife.
You can do it, of course, but there are real "hammers" (e.g. Excel or Access) available for this kind of task.

Jimmy
 

1 more replies
Relevance 54.12%

,I know there is a way,but my memory fails me in my old age - When using autotext to insert a date in a Word document how do you stop the date from changing to the current date when re-opening the saved document

Answer:Dates in Autotext (WORD)

Ensure that "update automatically" is not ticked.

4 more replies
Relevance 54.12%

I have a journal that I write and I just realized every time I open the doc, it updates all of my dates, not just the one for the current entry.

So I went into "Date and Time" under Insert, and "update automatically" is not checked.

What else do I need to do here?

More replies
Relevance 54.12%
Question: Word Merge Dates

When creating a word merge document there are several options available for the date field. DATE will always display the current date when the merged document is opened. CREATEDATE will always display the date the template was created. Is there a way to ensure that the resulting merged document always retains and displays the date on which it was created, ie when the merge was done?
 

More replies
Relevance 54.12%

When I put a letter on MC Word or Works and later recall it from its folder, the date of the letter is changed from the creation date to the current date. I realize I can find the creation date on "properties". But, if I want to sent a copy to someone, only the current date will be shown. Is there some way I can change this?
 

Answer:dates on MC Word letters

You're inserting a date and the checkbox "update" at the bottom should NOT be checked.

------------------
~dreamboat~
Brainbench MVP for Microsoft Word
http://www.brainbench.com
 

1 more replies
Relevance 54.12%

When you insert a date you have a list to choose from is there any way of making your choice the default date?

Answer:Word 2000 and Dates

Go to Insert. Click on Date and Time. Click on the format you want and click the default button at the bottom of the dialogue box.Brian

3 more replies
Relevance 54.12%

Hi guys,

I have been on the lookout for a macro that will compare 2 worksheets within MS Excel and output the differences on a third sheet.

I am being directed to the URL below but cannot for the life of me work out how to implement this:

http://exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html

There are two snippets of code which I will include here for convenience but not sure whether these should be pasted into Workbook Code (i.e. right-clicking Excel icon left of "File" - top-left), Worksheet Code (i.e. right-clicking worksheet tab) or inserting a new Module - or some other method. Is there anyone here that could guide me to how to implement this code?

Quote from webpage - "With the macro below it is possible to compare the content of two worksheets.
The result is displayed in a new workbook listing all cell differences. "

Code:
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count > 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Column... Read more

Answer:Solved: How to use this Macro in MS Excel (compare excel worksheets)

Apologies... I have sorted this... For those that might be looking at this for help here's what I missed:

These are both Sub's that should be created as a Module - both in the same module. I am just comparing worksheets within the Active Worksheet so just removed:

' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("WorkBookName.xls").Worksheets("Sheet2")
Click to expand...

I was then able to run this from Macro's. All sorted.

Apologies again
 

1 more replies
Relevance 54.12%

Hi, I have created an excel macro that runs sensitivities in my model. The excel button runs 5 different cases.

The macro runs fine and does as I want it to.

Once the macro has run, and I go to edit a cell and hit "F2" excel crashes. This only seems to happen in certain cells and not all of the time.

Does anyone have experience with similar questions?

Thanks
 

Answer:Excel Macro runs fine..then excel crashes

11 more replies
Relevance 54.12%

With due respect and credit to DerbyDad03 whose earlier post gave me the code below; is there any way to have the new worksheets created from a variable length field e.g. a persons name in a list or a field of fixed length other than a date as the script below uses?Sub CreateMonthlySheets()Dim lastRow, mMonth, tstDate1, tstDate2, shtName, nxtRowOn Error Resume Next'Turn off ScreenUpdatingApplication.ScreenUpdating = False'Make a copy of the data sheet and sort by date Sheets("Sheet1").Copy After:=Sheets(1) Sheets(2).Name = "SortTemp" With Sheets("SortTemp") lastRow = .Cells(Rows.Count, 1).End(xlUp).Row Rows("2:" & lastRow).Sort Key1:=Range("A2"), Order1:=xlAscending 'Using SortTemp Sheet, create monthly sheets by'testing Month and Year values in Column A'Loop through dates For Each mMonth In .Range("A2:A" & lastRow) tstDate1 = Month(mMonth) & Year(mMonth) tstDate2 = Month(mMonth.Offset(-1, 0)) & Year(mMonth.Offset(-1, 0)) 'If Month and Year are different than cell above, create new sheet If tstDate1 <> tstDate2 Then ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count) 'Name the sheet based on the Month and Year ActiveSheet.Name = MonthName(Month(mMonth)) & " " & Year(mMonth)'Copy Column Widths and Header Row .Rows(1).Copy ActiveSheet.Rows(1).PasteSpecial Paste:=8 'ColumnWidth ActiveSheet.Rows(1).PasteSpecial 'Data... Read more

Answer:Excel Macro to create new worksheet in Excel 2010

I created a quick macro to help you out. This macro creates a new sheet and names it using the contents of cell "A1".Sub CreateNewSheet()
Dim NewSheet
Dim SheetName As String

SheetName = Worksheets("Sheet1").Cells(1, "A").Value
Set NewSheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
NewSheet.Name = SheetName

End Sub
Law if Logical Argument: Anything is possible if you don't know what you're talking about.

4 more replies
Relevance 54.12%

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.FileSearchCan 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 VariantDim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As RangeDim ServRange As Range, SrcOpen As Boolean, SourceName As StringDim FilSrch As Object, MyFilArray() As String'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"'Exit Sub'Sheets("LookUp").[a12] = NowWith Application .ScreenUpdating = False .StatusBar = "Counting source files ..."End WithSet FilSrch = Application.FileSearchWith FilSrch .NewSearch .LookIn = ActiveWorkbook.Path .FileType = msoFileTypeExcelWorkbooks If .Execute > 0 Then ReDim MyFilArray(.FoundFiles.Count) For I = 1 To .FoundFiles.Count If .FoundFiles(I) <> ActiveWorkbook.Path & "\" & ActiveWorkbook.Name Then ... Read more

Answer:Excel 2003 Macro doesn't run under Excel 2007

As far as I know, Application.FileSearch is not available in 2007. Searching for filesearch replacement turns up lots of hits with various workarounds, such as this:http://groups.google.com/group/micr...Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 54.12%

Does anyone know how to run Excel 2003 print macro on Excel 2007 or higher? When I tried running my 2003 macro, the VBA editor pops up w dialogue box, " Run-time error '1004': Method 'ActivePrinter' of object'_Application' failed ".

This is my code: Application.ActivePrinter = "Epson LQ-300 ESC/P 2 on USB003"

Can anyone help?

Thanks!
 

Answer:How to run Excel 2003 print macro on Excel 2007?

Welcome to TSG hope we can help you and you can help others.

I'm not an Excel macro guy but you code looks reasonable. What happens if you run this. I suspect printer name is a little off.

MsgBox "The name of the active printer is " & Application.ActivePrinter
 

1 more replies
Relevance 54.12%

Hey
I have imported data from an outside database and it brought in the dates seperated. The information is in columns and the day, month (which is in text), and year each have their own cell. There are hundreds of dates. Due to space considerations I need each date to only take one cell. Is there any way to do this without having to do it by hand?
 

Answer:Solved: Dates in Excel

Not sure which version of Excel you are using, but am assuming 2007. I will also mention what to do if you are using 2003 or earlier

Create a blank worksheet and do the following so that you can see how the following formula works and then how to convert it as a value rather than a formula

In A1 type 24
In B1 type June
In C1 type 1990
In D1 type =DATEVALUE(CONCATENATE(A1,B1,C1))

Assuming you have similar data in the Columns A, B and C, copy the formula in D1 down column D until you have a formula for each row of data in A, B, C

Select all the formulas in column D and click on Home Ribbon tab ans click on the Copy Icon to place it in the clipboard. 2003 and earlier Edit, Copy

Whilst those cells are still selected click on Home ribbon tab and click on the little down arrow just under the Paste button and select paste values. 2003 and earlier Edit, paste Special, Values OK

Your data in column D will now be as a date (not a formula) which in the background is treated as a number, which then allows you to do calculations on the dates.

Now select columns A, B and C and delete those columns

You are now left with column A and the dates in single cells.

Hope that helps
 

3 more replies