Free Microsoft Excel 2013 Quick Reference

Excel VBA Deselect Entire Sheet

In my code I have selected an entire sheet in a workbook and pasted the data into another workbook, hence having the entire sheet highlighted/selected.

Later in the code I want to delete columns but I get a run-time error 'Select method of Range class failed.'

Here is my code:


	VB:
	
Workbooks(NewWorkbook).Activate 
Workbooks(NewWorkbook).Sheets("Gold AOR - Summary").Cells.Copy 
Workbooks(Entry_Book).Sheets("Gold AOR Exec Summary").Cells(1, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 
Application.CutCopyMode = False 
 
 '... rest of code that works fine.
 
 'Never gets past this first line of code.
Workbooks(Entry_Book).Sheets(1).Range("C:C").Select 
Selection.Delete 
Workbooks(Entry_Book).Sheets(1).Range("D:F").Select 
Selection.Delete 
Workbooks(Entry_Book).Sheets(1).Range("D:D").Select 
Selection.Insert 
Selection.ColumnWidth = 2 
Workbooks(Entry_Book).Sheets(1).Range("F:F").Select 
Selection.Delete 
Workbooks(Entry_Book).Sheets(1).Range("G:I").Select 
Selection.Delete 
Workbooks(Entry_Book).Sheets(1).Range("G:G").Select 
Selection.Insert 
Selection.ColumnWidth = 2 
Workbooks(Entry_Book).Sheets(1).Range("I:I").Select 
Selection.Delete 
Workbooks(Entry_Book).Sheets(1).Range("J:L").Select 
Selection.Delete 
Workbooks(Entry_Book).Sheets(1).Range("J:J").Select 
Selection.Insert 
Selection.ColumnWidth = 2 
Workbooks(Entry_Book).Sheets(1).Range("L:L").Select 
Selection.Delete 
Workbooks(Entry_Book).Sheets(1).Range("M:O").Select 
Selection.Delete 
Workbooks(Entry_Book).Sheets(1).Range("M:M").Select 
Selection.Insert 
Selection.ColumnWidth = 2 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The only thing I can think of is the issue is that the entire sheet is still selected, but even when I just tell it to select a specific cell that errors out the same way.

Any help you could provide would be much appreciated.


Here's my code. Basically with this code, I have the SharePoint 2010 list items imported to current Excel workbook in new sheet which is sheet4. Since I m using vba macro to do it, I need to feed this macro enabled xlsx file to third party tool. That third party tool doesn't take macro enabled excel file. So I want to write a vba code that would simply copy the existing ListObjects to a new Excel workbook which I can save using xlsx extension and easily provide as an i/p to my third party tool. Below, I have created a Demo xlsx file. I want to copy the sheet4 of current workbook to Sheet1 in Demo xlsx file. How do I do it? Thanks.

Sub TestMacro()
Dim objMyList As ListObject
Dim objWksheet As Worksheet
Dim strSPServer As String
Const SERVER As String = "http://abcd/"
Const LISTNAME As String = "{A486016E-80B2-44C3-8B4A-8394574B9430}" Const VIEWNAME As String = ""
' The SharePoint server URL pointing to ' the SharePoint list to import into Excel.
strSPServer = "http://" & SERVER & "/_vti_bin"
' Add a new worksheet to the active workbook.
Set objWksheet = Worksheets.Add
' Add a list range to the newly created worksheet
' and populated it with the data from the SharePoint list.
Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _ Array(strSPServer, LISTNAME, VIEWNAME), True, , Range("a1"))
Dim xlApp As Object
Dim wbExcel As Object
Dim wb2 As Workbook
Set xlApp = CreateObject("Excel.Application")
Set wbExcel = xlApp.Workbooks.Add
With wbExcel
.Title = "Demo"
.SaveAs Filename:="C:Documents and Settingsshress2DesktopDemo.xlsx"
End With

xlApp.Visible = True

Set objMyList = Nothing
Set objWksheet = Nothing
End Sub

I have used the following vba codes to hide the menu bars, toolbars, etc. on Excel 2003 'Main Menu Sheet', and it has worked perfectly well: I'm sorry I tried many times to enclose the code using the # tag, but it would come out wrong hence I have pasted it without enclosing it.


	VB:
	
) 
    Dim ws As Worksheet 
    If ActiveSheet.Name = "MAIN MENU" Then 
        Application.CommandBars("Worksheet Menu Bar").Enabled = False 
        Application.CommandBars("Standard").Visible = False 
        Application.CommandBars("Formatting").Visible = False 
        With ActiveWindow 
            .DisplayHorizontalScrollBar = False 
            .DisplayVerticalScrollBar = False 
        End With 
        Application.DisplayFormulaBar = False 
        Application.DisplayStatusBar = False 
    Else 
        Application.CommandBars("Worksheet Menu Bar").Enabled = True 
        Application.CommandBars("Standard").Visible = True 
        Application.CommandBars("Formatting").Visible = True 
        With ActiveWindow 
            .DisplayHorizontalScrollBar = True 
            .DisplayVerticalScrollBar = True 
        End With 
        Application.DisplayFormulaBar = True 
        Application.DisplayStatusBar = True 
    End If 
     
    Application.DisplayAlerts = True 
End Sub 
 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    If Sheets("MAIN MENU").Range("A1").Value = "close" Then 
        Sheets("MAIN MENU").Range("A1").Value = "" 
        Application.CommandBars("Worksheet Menu Bar").Enabled = True 
        Application.CommandBars("Standard").Visible = True 
        Application.CommandBars("Formatting").Visible = True 
        With ActiveWindow 
            .DisplayHorizontalScrollBar = True 
            .DisplayVerticalScrollBar = True 
        End With 
        Application.DisplayFormulaBar = True 
        Application.DisplayStatusBar = True 
        ActiveWorkbook.Saved = True 
        ActiveWorkbook.Close 
    Else 
        Application.CommandBars("Worksheet Menu Bar").Enabled = True 
        Application.CommandBars("Standard").Visible = True 
        Application.CommandBars("Formatting").Visible = True 
        With ActiveWindow 
            .DisplayHorizontalScrollBar = True 
            .DisplayVerticalScrollBar = True 
        End With 
        Application.DisplayFormulaBar = True 
        Application.DisplayStatusBar = True 
        ActiveWorkbook.Save 
        ActiveWorkbook.Close 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However my PC which has Office 2003 developed a serious fault and on transferring to another PC with Office 2010, these same codes which successfully hides the menu bars, toolbars, formatting bars, etc. for the 'Main Menu' sheet (Excel 2003) is not hiding the menu bars, etc. for the same Main Menu sheet for the Excel 2010.

The 'Main Menu' sheet contains only command buttons that enable me to go to other sheets in the workbook. It also includes a command button to 'Exit Application'. The other sheets contain command buttons that enable a user to get back to the 'Main Menu' sheet. It is therefore only the 'Main Menu' sheet that I do not want the menu bars, formatting, scrollbars to be visible. This has worked well with Excel 2003 in my now-faulty PC.

I should be grateful, please, for any adjustment to the codes so I can achieve the same aim in respect of the 'Main Menu' sheet with Excel 2010. I want to prevent users from attempting to format or alter the 'Main Menu' sheet.

Thanks for your help.

Buddy

How to clear the contents of an entire sheet.

i want to delete the contents of the sheet1. without deleting the sheet.

Thanks in advance for the help.

I am working on an excel/vba userform that can add date to my excel workbook. The problem that I have come up with is trying to add data to two separate excel sheets.

Explanation of form:
Used to track volunteer events and points of contact. One sheet has volunteer events listed on it, the other has points of contact listed on it. The goal is to use one userform to enter all new info for an event and the point of contact information for it, then add this and it will populate the next empty row in both events and poc sheets.

Code is below;
Private Sub cmdAdd_Click()
  
If Trim(Me.EventName.Value) = "" Then
  Me.EventName.SetFocus
  MsgBox "Please enter an event name"
  Exit Sub
End If

If Trim(Me.VolReq.Value) = "" Then
  Me.VolReq.SetFocus
  MsgBox "Please enter how many volunteers are requested or unspecified"
  Exit Sub
End If

If Trim(Me.HoursDay.Value) = "" Then
  Me.HoursDay.SetFocus
  MsgBox "Please enter how many hours long this event is from start to finish"
  Exit Sub
End If

If Trim(Me.PreviousYes.Value) = "" And Trim(Me.NewYes.Value) = "" Then
    Me.PreviousYes.SetFocus
    MsgBox "Please choose either previous or new POC and check the box"
  Exit Sub
End If

If Trim(Me.PreviousYes.Value) = "" And Trim(Me.NewYes.Value) = "" Then
    Me.PreviousYes.SetFocus
    MsgBox "Please choose either previous or new POC and check the box"
  Exit Sub
End If

If Trim(Me.PreviousYes.Value) = "True" And Trim(Me.NewYes.Value) = "True" Then
    Me.PreviousYes.SetFocus
    MsgBox "Please choose either previous or new POC and check the box"
  Exit Sub
End If

If Trim(Me.PreviousYes.Value) = "True" And Trim(Me.Previous.Value) = "" Then
    Me.Previous.SetFocus
    MsgBox "Please choose a previous POC or uncheck the previous box"
  Exit Sub
End If

If Trim(Me.NewYes.Value) = "True" And Trim(Me.FullName.Value) = "" Then
    Me.FullName.SetFocus
    MsgBox "Please write in the full name of the POC"
  Exit Sub
End If

If Trim(Me.NewYes.Value) = "True" And Trim(Me.Phone.Value) = "" Then
    Me.Phone.SetFocus
    MsgBox "Please write in the phone number of the POC"
  Exit Sub
End If

If Trim(Me.NewYes.Value) = "True" And Trim(Me.Email.Value) = "" Then
    Me.Email.SetFocus
    MsgBox "Please write in the email of the POC"
  Exit Sub
End If

Dim emptyRow As Long
With Sheet1
emptyRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(emptyRow, 1).Value = Me.DayStart.Value
.Cells(emptyRow, 2).Value = Me.DayEnd.Value
.Cells(emptyRow, 3).Value = Me.EventName.Value
.Cells(emptyRow, 4).Value = Me.VolReq.Value
.Cells(emptyRow, 6).Value = Me.MultShift.Value
.Cells(emptyRow, 7).Value = Me.HoursDay.Value

If Trim(Me.PreviousYes.Value) = "True" Then
  .Cells(emptyRow, 8).Value = Me.Previous.Value
ElseIf Trim(Me.NewYes.Value) = "True" Then
  .Cells(emptyRow, 8).Value = Me.FullName.Value
End If

End With

With Sheet2
emptyRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(emptyRow, 1).Value = Me.FullName.Value
.Cells(emptyRow, 2).Value = Me.EventName.Value
.Cells(emptyRow, 3).Value = Me.Phone.Value
.Cells(emptyRow, 4).Value = Me.Email.Value
End With

Me.EventName.Value = ""
Me.VolReq.Value = ""
Me.MultShift.Value = "False"
Me.HoursDay.Value = ""
Me.DayStart.Value = ""
Me.DayEnd.Value = ""
Me.PreviousYes.Value = "False"
Me.Previous.Value = ""
Me.NewYes.Value = "False"
Me.FullName.Value = ""
Me.Phone.Value = ""
Me.Email.Value = ""

End Sub
Any help is appreciated. I have tried scouring the internet for an example to follow but couldn't find one that fit.

Below i have uploaded two images that i have captured from my excel document (Sheet1, Sheet 2) so as to better illustrate what i mean.

Here's a brief description basically, I just want my macros to compare Part Number (column C) from both sheets and find out the differences. And when a string differences is detected between both sheets it will highlight the row on both sheet of BOM-list to indicate to the user the differences in the Part-number(column C). But that is a problem too as seen in the images there is some rows with "space" which the loop have to take care of to prevent comparing an empty string thus giving wrong result.

Sorry for my poor command of English and explanation if its not clear to you. Can someone guide me on this i'm rather aimless on where or how to start and i have to complete this within a week without prior knowledge on excel-VBA programming understanding.

Dear Experts - first if you respond many thanks and im sorry if this is a
poor question

i have an excel sheet with many rows and i would like to use a loop which
processes the row in a similar manner to a classic Print statement possibly
creating an ascii file of the data , i know excel vba has a row statement
which i would like to use - even if its printing rows via a message box - to
me the sheet i work on rows are like database records and as such i want to
treat them as such - can any one help

my row numbers are row 4 to 1443 - not that i guess that helps

my logic is something like ;

for rows
do
print entire row to output file
loop
--
C Ward

Hi, I'm trying to write a macro in which i need to add excel files as new sheets in the current workbook.. could you please help me

here is the scenario
i have opened a excel file say myexcel.xlsm in that i wanna to add new sheets populated with files sample1.xlsm and sample2.xlsm which are located in my hard disk gimme some vba code to do this

Thanks in advance!!

I am trying to run multiple web queries in excel vba and I get the 1004 - Application-defined or object-defined error when the code reaches the second ".Refresh" line (see code below). Basically, if I run the code below using something like www.msn.com as the url, then it works just fine, but if I use www.yahoo.com, then it errors out on the ".Refresh" in the second With block. Can someone please help?

Sheets("Sheet1").Select
Cells(1, 1).Select

varconnection = "http://www.yahoo.com"

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & varconnection, Destination:=Range( _
"A1"))
.FieldNames = False
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = False
.SaveData = True
End With

hi there,

lets say i have this data sheet:
Eg:

A1
This is a cute dog
This is a orange cat
This is an big apple

i need excel vba codes to do a reverse sort meaning instead of sorting the first string in the cell, i want it to sort from the string that the user can specified.
Meaning: if inputbox: "Enter sorting order # from the back: 2"
that means it will sort by the 2nd last string in this order: big, cute, orange

and i want program to copy and paste the data
This is a cute dog
This is a orange cat
This is an big apple
into cell B1 down into new sheet called "Sorted" without the sorting criteria meaning "cute", "orange" and "big" will not be in the string
whereas they will be in cell A1 down.
Finally Eg:Sheet("Sorted")
A1
big
cute
orange

B1
This is an apple
This is a dog
This is a cat

and after sorting, the column A1 would be deleted, leaving only B1, which i then want to shift the values to column A1.

Final Results: Worksheets("Sorted")
A1
This is an apple
This is a dog
This is a cat
In vba anyone can help???

hi all,

At the moment I directly open the *pri file in our Portfolio program called Advent Axys, and manually update the ticker prices using a downloaded Excel format file from the BigCharts website which is saved as csv extension.
What I would like to do is develop a stand alone program in Visual Basic ( not in Excel VBA) to update the price file in our Portfolio system automatically using the downloaded Excel format file csv extention file from the BigCharts.
But before that, I need to export the *.pri file from our Portfolio system in to Excel which still saves as *.pri extention.
Then once it updates, I import the updated *.pri file back in our Portfolio program.
I understand that the Excel VBA code can be incorporated in Visual Basic code provided there is an object declaration for Excel file (In this case eventhough the both files are in Excel format, they don't have xls extention). Would anyone have any ideas about how to use external files and Excel VBA code in Visual Basic?

Below is the code that I currently have in Visual Basic. What I'm trying to accomplish is using the ticker (eg. msft) as a keyword search to look up in the price file. If found , the price of that ticker from the test.csv file will be copied in to the price file which is test.pri. I haven't ran it yet. Any thoughts or advice on this will be much appreciated.
Thank u so much
P.S Not able to upload the 2 files since their extention is different


	VB:
	
 
Sub UpdatePrice(BigChartPath As String, BigChartName As String, AxysPricePath As String, AxysPriceName As String) 
     'Below are Excel VBA codes
     'Uses the  test.csv  to look up tickers in test.pri and update the price in it
    Dim PriceFile As Workbook, BigChartFile As Workbook 
    Dim PriceFileSheet As Worksheet, BigChartSheet As Worksheet 
     
    Dim MaxRows As Long 
    Dim PriceFileRow As Long 
    Dim BigChartRow As Long 
    Dim BigChartFound As Boolean 
     
    Call CheckBookOpen(BigChartPath & BigChartName) 
    Call CheckBookOpen(AxysPricePath & AxysPriceName) 
     
    Set BigChartFile = Workbooks(BigChartName) 'Big Chart website imported CSV file saved as test.csv
    Set PriceFile = Workbooks(AxysPriceName) ' pri file imported from Advent Axys saved as test.pri
     
    Set BigChartSheet = BigChartFile.Sheets(Sheet) 
    Set PriceFileSheet = PriceFile.Sheets(Sheet) 
     
     
    MaxRows = w1.Range("a65536").End(xlUp).Row 
     
    For BigChartRow = 2 To MaxRows 
         'On Error GoTo NotFound
        BigChartFound = True 
        PriceFileRow = PriceFileSheet.Range("b:b").Find(BigChartSheet.Cells(BigChartRow, 2).Value).Row 
        If BigChartFound = True Then 
            PriceFileSheet.Cells(PriceFileRow, 3).Value = BigChartSheet.Cells(BigChartRow, 4).Value 
        Else 
            PriceFileSheet.Cells(PriceFileRow, 3).Value = "not found" 
             'PriceFileRow = PriceFileRow + 1
        End If 
    End If 
     
Next BigChartRow 
 'ThisWorkbook.Activate
Exit Sub 
 
 'NotFound:
 
 'w3.Cells(w3Row, 1) = w1.Cells(w1Row, 1)
 'w3.Cells(w3Row, 2) = w1.Cells(w1Row, 2)
 'w3.Cells(w3Row, 3) = "not found"
 'w3Row = w3Row + 1
 'w2Found = False
Resume Next 
 
End Sub 

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


Hi

I'm a newcomer to excel VBA and have been having some problems with loops

Of the two examples below the first works like a dream but the second returns an 'object variable or with block variable not set error'

As you will see there is little difference between the two chunks of code, except for the do statements. The first copies rows to another sheet and the second replaces found values - can anyone help?

Example 1 - works fine

With Worksheets("core data").Range("s1:s30000")
'search for accounts for batch
input_account = "None_sent"

Set search = .Find(input_account, LookIn:=xlValues)
If Not search Is Nothing Then
firstAddress = search.Address
Do
search.EntireRow.Copy Destination:= _
Sheets(15).Cells(65536, 3).End(xlUp).Offset(1, -2)
Set search = .FindNext(search)
Loop While Not search Is Nothing And search.Address firstAddress
End If

End With

Example 2 - variable not set error

With Worksheets("core data").Range("s1:s30000")
'search for accounts for batch and update date and value
input_account = "None_sent"

Set search = .Find(input_account, LookIn:=xlValues)
If Not search Is Nothing Then
firstAddress = search.Address
Do
search.Value = "Date2"
Set search = .FindNext(search)
Loop While Not search Is Nothing And search.Address firstAddress
End If

End With

Looking for a little syntax help.

I'm writing an excel module that will scan each cell for a date. If the date is not equal to today's date then I would like to highlight the entire row grey.

So far I was thinking something along the lines of the follwing:
Because this is my first time doing vba (I was a java programmer) I will have to use psuedo code.

Sub todayStandsOut()
If Sheet1.(Range("Current Cell")) != date(today) Then
Rows("current row").Interior.ColorIndex = 15
End If
End Sub

Please help!
Also If anyone knows an excel vba command reference site or listing that would be helpful as well.

Thanks

Excel / VBA – Over writing existing file.

I am opening a template file, “C:JobsNew Sheet.xlt”, through a macro and saving it as “C:JobsNew Sheet.xls”, which is already in existence. I want the existing file always overwritten without the warning prompt appearing. What is the code to do this?
Sandy

Hey,
Ive made an online store spreadsheet where people can build and purchase computers. I need to work out how to make it so my stock decreases by a certain amount when they select a product, but if they deselect a product i need it to return to the orignal value e.g:

someone chooses a AMD 2200XP, quantity of 4. My stock for this component then decreases by 4. Next they change there mind and choose something else, the component's stock increases by 4.

I know this is incredibly easy to do in excels VBA, but i need to do it without using that if possible...can ayone help?

Thanks for your time,

-Ross

Help! I've been building a large macro for 2+ months on a PC with Windows 7 and Excel 2010. I have the calculation procedures split over multiple smaller macros that perform the actual calculations.

Everything was working fine until I came in after the weekend. Now, when I try to run the macro through hitting the Play button in VBA, Excel/VBA locks up with no error message. Similarly, when I open the workbook and go to the Developer Tab>Macros, the file crashes when I just simply click on the name of the master macro. Same deal with a button on a worksheet that I have assigned to the master macro. All of this worked before the weekend, and I have NO idea what could have changed...

I've tested a few of the subroutines, and the simple ones that clear individual worksheets to zero-out the calculations work just fine with no lock-ups. But, anytime that I try to run the master macro, or the subroutines that pull information from worksheets (esentially, anything involving my public variables), the whole thing crashes. This same error happens when I go to Debug>Compile in VBA. It's terribly frustrating since there is NO error message, no run-time error, nothing to show me what goes wrong.

I found some previous posts that recommended changing code like this:

	VB:
	
Sheets("INPUTS").select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
to something more specific like:

	VB:
	
ThisWorkbook.Sheets("INPUTS").select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So, I changed all of my sheet references to call the activeworkbook first, but that still didn't help.

My guess is that it's a security issue, but I am not familiar enough with MS Office to know.... any ideas?

Thanks!
Darcie

All,

This is the issue I'm currently faced with:
When running a code step-by-step, so using F8 in the VBA screen, the code works fine and does everything I would like the code to do. However, when running the entire code (using F5 or a button linked to my piece of code) MS Powerpoint crashes: "Microsoft Powerpoint has stopped working".
I'm using a piece of code in Excel vba that (1) opens an existing powerpoint file, (2) updates the embedded table on the second slide - the source data for this table is in a separate Excel file, (3) saves and closes the powerpoint files and (4) finally closes the ms powerpoint application.

Hope somebody can help me out!

Libraries used
in Excel: Microsoft Powerpoint 12.0 Object Library / Visual Basic for Applications / Microsoft Excel 12.0 Object Library / OLE Automation / Microsoft Office 12.0 Object Library / Microsoft DAO 3.6 Object Library.
in Powerpoint: Visual Basic for Applications / Microsoft Powerpoint 12.0 Object Library / OLE Automation / Microsoft Office 12.0 Object Library.

System:
Windows 7 professional
MS Excel 2007
MS Powerpoint 2007

Solutions so far tried, but didn't work:
> Check on add-ins, disabled one-by-one and checked the effect
> Check default printer installed (may seem strange but some stories on the internet proved to be succesfull after checking this), test printer installed and set as default printer, same for pdf printer
> Changed code for closing the powerpoint file to "PPapp.CommandBars.ExecuteMso "FileClose", didn't work / error

Complete code:

	VB:
	
[COLOR=#505050][FONT=Segoe UI]Sub createppt()[/FONT][/COLOR] 
 
[FONT=Segoe UI][COLOR=#505050] 'open ppt template file[/COLOR][/FONT]
[COLOR=#505050][FONT=Segoe UI]Dim PPT As PowerPoint.Application[/FONT][/COLOR] 
[FONT=Segoe UI][COLOR=#505050]Set PPT = New PowerPoint.Application[/COLOR][/FONT] 
[FONT=Segoe UI][COLOR=#505050]PPT.Visible = True[/COLOR][/FONT] 
[FONT=Segoe UI][COLOR=#505050]PPT.Presentations.Open Filename:="C:Test02output_01.pptm"[/COLOR][/FONT] 
 
[FONT=Segoe UI][COLOR=#505050]adaptpptcontent01[/COLOR][/FONT] 
 
[FONT=Segoe UI][COLOR=#505050]End Sub[/COLOR][/FONT] 
 
 
[FONT=Segoe UI][COLOR=#505050]Sub adaptpptcontent01()[/COLOR][/FONT] 
 
[FONT=Segoe UI][COLOR=#505050]Dim PPapp As PowerPoint.Application[/COLOR][/FONT] 
[FONT=Segoe UI][COLOR=#505050]Dim PPpres As PowerPoint.Presentation[/COLOR][/FONT] 
[FONT=Segoe UI][COLOR=#505050]Dim PPslide As PowerPoint.Slide[/COLOR][/FONT] 
 
[FONT=Segoe UI][COLOR=#505050]Set PPapp = GetObject(, "Powerpoint.Application")[/COLOR][/FONT] 
[FONT=Segoe UI][COLOR=#505050]Set PPpress = PPapp.ActivePresentation[/COLOR][/FONT] 
 
[FONT=Segoe UI][COLOR=#505050]PPapp.ActiveWindow.ViewType = ppViewSlide[/COLOR][/FONT] 
 
[FONT=Segoe UI][COLOR=#505050]PPapp.ActiveWindow.View.GotoSlide (2)[/COLOR][/FONT] 
[FONT=Segoe UI][COLOR=#505050]PPapp.ActivePresentation.Slides(2).Shapes("Slide2Table").Select[/COLOR][/FONT] 
[FONT=Segoe UI][COLOR=#505050]PPapp.CommandBars.ExecuteMso "LinksUpdate"[/COLOR][/FONT] 
 
[FONT=Segoe UI][COLOR=#505050]PPpress.Save[/COLOR][/FONT] 
[FONT=Segoe UI][COLOR=#505050]PPpress.Close[/COLOR][/FONT] 
[FONT=Segoe UI][COLOR=#505050]PPapp.Quit[/COLOR][/FONT] 
 
[FONT=Segoe UI][COLOR=#505050]Set PPapp = Nothing[/COLOR][/FONT] 
[FONT=Segoe UI][COLOR=#505050]Set PPpress = Nothing[/COLOR][/FONT] 
 
[COLOR=#505050][FONT=Segoe UI]End Sub[/FONT][/COLOR] 

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


Hello,

I have to remove a number of columns from a particular Excel report. I am running Excel v 5.0 on a Windows 2K network. I used the macro record feature, and here is the resulting code:

Sub ColumnCut()
Columns("O:P").Select
Selection.Delete Shift:=xlToLeft
End Sub

Seems pretty straightforward to me (and I do have a programming background, just not VB.) However, when I run this macro, it deletes the
entire sheet (i.e. all data in all columns A:N disappear.)

I can't believe I can't make something so simple work; I must be missing something very obvious.

Any help would be appreciated. Scorn is optional!

Thanks.

I have a message box that says "Delete entire sheet row?" that pops up when I run this line of code:

CurWks.Range("A4:J2000").Delete

This must be an automatic excel message box because I have not created it. Is there anyway to incorporate a "yes" reply into the code or to stop the box from appearing?

Hello all,

I've been busy trying to figure out how to edit MP3 tags in Excel/VBA.
I've found a method which makes use of a DLL-file called cddbcontrol.dll.
Here's the code which is working for me :
Code:
Sub MP3TagChange()
Sheets("MP3tags").Select
Dim id3 As New CddbID3Tag
id3.LoadFromFile Range("A2").Value, False
id3.Album = Range("B2").Value
id3.Title = Range("E2").Value
id3.LeadArtist = Range("F2").Value
id3.Year = Range("G2").Value
id3.Genre = Range("H2").Value
id3.TrackPosition = Range("I2").Value
id3.SaveToFile Range("A2").Value
End Sub
In A2 I have the full path to a MP3-file.
This code is working and the changes are being made to that file.

What I'd like to do is to change this code, so that it changes the tags for all the Mp3's which are listed in Col A.
I hope someone can help me out on this one..

Thanks in advance,
Mike

Is there any way that you can automatically update an entire sheet with data from another sheet in another workbook? I.E. In Workbook A, format Sheet 1 so that it automatically pulls in all the data and formatting from Sheet 1 in Workbook B?

At the moment I am formatting each cell in Sheet 1 Workbook A to draw in data from the source cells in Sheet 1 Workbook B. However, when new rows or columns are added in the source sheet, they have to be manually added in to the target sheet as well. It would be so much simpler if I could simply tell the target sheet to pull in everything from the source sheet.

I am using Excel 2003, Windows XP Home

How Excel VBA corporate with MSN Alerts?

I want MSN Alerts to remind me when an Excel cell value more than 5.

Can I make it?

For detail:

1.aa.xls and sheets bb has a cell, C5.( in Computer A )

2.when the value of C5 = p` (>5) → MSN Alerts sent the value p` to Computer B.

3.and shake my msn window (ex.cc@msn.com in Computer B)

Can it done with Excel VBA ?

Is it free?

Thanks for your reading.

Hi,

I need some help with using Formula in Excel VBA Macros. I am trying to write a macro that put in a cell a formula referring to a cell whose position is dynamic.

So, let's say the cell i am writing to is the m,n th cell on sheet A. And the formula i want to write in the cell is simple, like "=Sheet2!F6". The complication is that while i know i want it to be Sheet2, i don't know if it would be F6 ... i get the row and column number from another set of variables a and b.

How do I resolve this?

Hello all,

I just finished a project in Excel/VBA. While thinking I had done such a great thing I happened to see a project another person was working on in Excel and I could not believe how different it looked. When you opened the application it didn't look like Excel at all, no toolbars, no cells visible, just his main VBA input box.

1. ANybody know any webiste that may help in doing this?.

2.Any ideas how he did hid the tolbars and cells like that? I didn't think you could hide all sheets?

I have a problem about using find method in Excel VBA.
Here is my program:-
__________________________________________________ _______________

Private Sub CommandButton2_Click()

Dim MyProduct As Range
Product = Range("F8").Value
Set MyProduct = ThisWorkbook.Sheets("Sheet2").Columns("E").Find(wh at:=Client, LookAt:=xlWhole)

ActiveSheet.Cells(8, 8).Value = MyProduct.Columns("G").Value

End Sub
__________________________________________________ ___________________

> how can I pull data at Columns(G) to Cells(8,8)
>the program I wrote ' ActiveSheet.Cells(8, 8).Value = MyClient.Columns("G").Value ' , give me error... Run-time error '91' Object variable or With block variable not set
>What does it mean?
> Please someone help me to solve this problem....