Posts by jamesparker_1

Re: Unhide Sheet With Index Name

Hi

Unfortunately that doesnt work because wf is not the sheet name. wf is the Sheet 'index' name if you get what i mean.

Hi

I want to make a hidden sheet visible. Except that i want to use the name of the sheet (the thing that stays the same even when you change the sheet tab name). I also want to select the name based on a variable called Year which the user adds elsewhere (eg as 2006).

wf = "WF_Edin_" + Year    'So for 2006 this would read WF_Edin_2008
wf.Visible = True

The above doesnt work as wf becomes a string which i dont think i want do i?

Thanks

James

Hi

The below Vba code runs a mailmerge of data stored in an excel called "mailmerge.xls" fine. However, When it does run word always asks me to chose which sheet I want to mailmerge which is a bit annoying when i want the process to be fully automatic.

ActiveDocument.Mailmerge.MainDocumentType = wdFormLetters 
ActiveDocument.Mailmerge.OpenDataSource 
Name:="H:\MBI\MailShot\Mailmerge.xls" 
 
With ActiveDocument.Mailmerge 
    .Destination = wdSendToNewDocument 
    .SuppressBlankLines = True 
    With .DataSource 
        .FirstRecord = wdDefaultFirstRecord 
        .LastRecord = wdDefaultLastRecord 
    End With 
    .Execute Pause:=False 
End With

What line of code do i need to add to make the macro automatically mailmerge "sheet1" of mailmerge.xls?

Thanks in advance for your help!

James

(p.s. I put this problem in the 'Word' help section originally but maybe this is more a VBA/Excel issue):confused:

Re: Having A Picture Of A Summary Sheet On A Graph Sheet

Hi again Andy!

You are becoming quite the hero of mine! Lol!

Again, Thank you very much for your expert advice! It is invaluable!

James

Hi

I have an excel worksheet called 'Data' and a graphsheet called 'Control'. The graph sheet doesnt contain a graph, I just use it as a blank sheet and put buttons on it that have macros attached (so it basically looks like a control panel).

My problem is that on this control Panel, I want to have a little picture box or something that shows a uptodate picture of a summary section (Cells A1:H8) on the sheet 'Data'.

Is there anyway to do this?

If it helps. I've attched a copy of what i'm trying to achieve

Thanks

Urgent: Vba Mailmerge To Autoselect Sheet Of Table

Hi

Is anyone able to help with this problem? I just cant figure out how to stop word asking me to choose which sheet of the excel file to mailmerge from!

Thanks

James

Re: & (and) Criteria In An 'else If' Macro Statement

Hi John

Thanks. That worked great. Big thanks for your help!!

James: D

Hi

I have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it. Please help!

'....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen


ElseIf YearBox.Value = iYear [COLOR="Red"]&[/COLOR] iMonthbox < iMonth Then
     MsgBox ("You may not enter Data before the current Month")
Else     '...... Run main code here

Thanks

James

Re: Range Definition Error

:music: Result!!

That worked perfectly. Thankyou very very much for all your time and hard work Andy! I really was stuck!

Thanks Again

James: D

Re: Range Definition Error

Hi

I just did what you suggested and you're correct in that there is no content at the point:

Set d = .Find(Seating_Month, LookIn:=xlValues)

I dont see why there isnt though.

I've attached a spreadsheet of my sheet where the data is trying to be entered if it helps.

Re: Range Definition Error

Apologies, I had copied the code in wrong! :confused: Ive corrected that now and I have no bugging errors now. But no data value (Seating_BAU_Existing) is being put in cell d.Offset(2, 0) now which i cant understand!! :crying:

Re: Range Definition Error

Hi Andy

I Really Appreciate all this help!!!

I'm still getting told "Run-time erroe '1004' Application-defined or Object-defined error" but now at the point:

With .Range(C.Offset(3, 0), C.Offset(3, 0).End(xlRight))

James

Re: Range Definition Error

Hi

I think i follow what is going on. I like the simplification of the code. But my macro still errors at the 'Set C = rngSearch.....' line. saying "Run-time error 424. Oject required". I'm only just getting to grips with this object stuff so what is wrong?

Thanks

Re: Range Definition Error

I'm still getting told "Run-time erroe '1004' Application-defined or Object-defined error" at the same point.

Is this anything to do with the fact that i define firstaddress1 as:

firstaddress1 = C.Offset(3, 0).Address

For Reference, here is the full code i'm using:

Public Seating_BAU_Existing
Public Seating_Year
Public Seating_Month


Sub Input_Seating_data()


    'Load Form to input Year and month and variations in seating capacity
    Load Seating
    Seating.StartUpPosition = 2
    Seating.Show


    'Copy Values into Capacity model


With Worksheets("Edin Capacity").Range("G4:IV4")


        Set C = .Find(Seating_Year, LookIn:=xlValues)
        firstaddress1 = C.Offset(3, 0).Address
  
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Worksheets("Edin Capacity")
 
        With ws.Range(ws.Range(firstaddress1), ws.Range(firstaddress1).End(xlRight))
        
              Set d = .Find(Seating_Month, LookIn:=xlValues)
              secondaddress = d.Offset(2, 0).Address      
              Range(secondaddress).Value = Seating_BAU_Existing


         End With


End With

Hi

I get an error when I run this macro at the the first line in the code below. firstaddress1 is set to cell $H$7 and I want the range to go from the value of firstaddress1 to whatever the value of the column is containing the last data value.

With Worksheets("Edin Capacity").Range(firstaddress1.End(xlRight)) 
       
        Set d = .Find(Seating_Month, LookIn:=xlValues)
        secondaddress = d.Offset(2, 0).Address
        
        Range(secondaddress).Value = Seating_BAU_Existing


End With

Thanks for your Help

James

Hi

I have designed a userform where a user enters a year, a month and a value. I am trying now to make my macro search for this year along row 1 in my worksheet("Edin Capacity"), then once it has found the year, search through the months (Row 3) to find the month and then enter the value a couple lines down (Row 6). (I've attached an example sheet to make it clear).

I've been looking at the excel help file and trying to modify it below but all i can do is search for the year that was entered (Public Seating_Year) and change the year to the data value. Not very impressive i know!

Public Data_Value
Public Seating_Year
Public Seating_Month


Sub Input_Seating_data()


    'Load Form to input Year and month and data value
    Load Seating
    Seating.StartUpPosition = 2
    Seating.Show




'Find Year and month Colunm.
'Paste values into rows.


With Worksheets("Edin Capacity").Range("A1:IV1")
    Set c = .Find(Seating_Year, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = Data_Value
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
  
 
End Sub

Hopefully someone can help!

Re: Combobox Is Blank Until I Type In It!

Thank Was perfect! Thank you so much for your help!!

Hi

I've Created a userform with a ComboBox that I've filled with the 12 months of the Year as follows:

Private Sub YearBox_Change()
YearBox.Style = fmStyleDropDownList
For i = 6 To 15
    YearBox.AddItem Format(DateSerial(i, 1, 1), "yyyy")
Next i
End Sub

However. When i run the userform the combobox is always blank until i type something in it and only then can i see the dropdown list of months.

I'm sure this is something simple! Can anybody help?

Hi

Hopefully a simple problem

My excel workbook called "Main" has a macro that opens a new workbook fills the new sheet with addresses from a sheet in "Main" then Saves the new workbook as "Mailmerge.xls". It then loads a word file and automatically runs through mailmerging these addresses to the printer.

My main problem is that I then write the below code to close the mailmerge.xls file:

Windows("Mailmerge.xls").Close

This is sometime ok the first time i run the process But I usually then get a window appear saying the following if i do it a second time:

"Mailmerge.xls is now available for editing.

Choose Read-Write to open it for editing."

I dont want this to appear. I just want to close the file (I dont care if it saves changes or not if that helps).

Any Suggestions?

Finally Would this same code be used to close the word template documents used to mailmerge the data?

Re: Macro to open Word and Start Mail Merge

Hi Everyone

I need some help with the problem of running a mailmerge from excel as you've described above. The Vba code runs a mailmerge of data stored in an excel called "mailmerge.xls" fine. However, When it does run it always asks me to chose which sheet I want to mailmerge which is a bit annoying when i want the process to be fully automatic.

ActiveDocument.Mailmerge.MainDocumentType = wdFormLetters 
ActiveDocument.Mailmerge.OpenDataSource 
Name:="H:\Mailmerge.xls" 
 
With ActiveDocument.Mailmerge 
    .Destination = wdSendToNewDocument 
    .SuppressBlankLines = True 
    With .DataSource 
        .FirstRecord = wdDefaultFirstRecord 
        .LastRecord = wdDefaultLastRecord 
    End With 
    .Execute Pause:=False 
End With

What line of code do i need to add to make the macro automatically mailmerge "sheet1" of mailmerge.xls?

Thanks in advance for your help!

James