Free Microsoft Excel 2013 Quick Reference

How to write VBA to copy range and save into .JPG ?

Hello All ,

I would like to know how to write VBA code to copy a specific range in Excel and then save it in .JPG format ?

Please suggest.

I have a web page download consists of the contact information like Name,
address and city etc. I opened it with the Excel and the information is
organized in the way that is impossible to import to an Access database.
However the "Name" is shown in a font and size differnt from other
information and this is only indication to seperate individual contact
information. How to write a vba to evaluate the test font and size on an
active cell range? Thanks.

All my VBA is opening, formatting, saving and closing PPT from Excel. I'm new to VBA and have been try to resize cell ranges and charts Pasted Special and linked. I've seen a number of examples but haven't been able to get them working with my current code. (from my phone because my internet is down - so there are no equal signs or brackets available in the code I typed below)

PPPres.Slides(3).Shapes.PasteSpecial DataType: ppPasteOLEObject, Link: msoTrue

Set PPSlide PPPres.Slides(3)
With Selection
.Height 200
.Width 500
End With

Remember that I don't have an "equals" on my phone so I know they are missing.

I don't know how to place this code? I don't know how to write for two ranges and one chart on the next slide.

hi I'm trying to write a macro that would save the current workbook into a NewFileName (ie LLPF.xls); and if that NewFileName already exists it would attach a digit to the NewFileName and loop until it is available (ie LLPF1.xls or LLPF2.xls etc)

If Counter >= CounterLimit Then
CurrentFileName = ActiveWorkbook.FullName
NewFileName = "LLPF.xls"
j = 0
Do While Not Dir(NewFileName) = ""
j = j + 1
NewFileName = "LLPF" + j + ".xls"

The macro currently does the job in saving the LLPF.xls, however if LLPF.xls already exists it fails to create LLPF1.xls as intended. Can anyone help with this please. Thanks.

Hi AllCould you please help me the way to writing VBA code in Addnew Button in Access 2003.


I have a noob question pls:

I want to define a variable range and use it to calculate averages. Here is what I have:

Dim rng1 as range
Set rng1 = Range(Selection, Selection.End(xlDown)).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(rng1)"

But it doesn't work and keeps erroring out at average. Any ideas?


My data looks like this:

Faye 14,874
Faye 18,050
Faye 31,255
Faye 56,351
Faye 59,352
Faye 74,887
Jean 42,671
Jean 45,560
Jean 60,170
Jean 100,357
Jean 112,163
Jeff 18,078
Jeff 26,859
Jeff 49,090
Jeff 74,579

I want to know how to loop through this dataset and for each value in
Column A, I will do some calculations for the associated data in Column
B and then place the result on Column C. For example, I want to find
the minimum/maximum of the value in Column B for the associated value
in Column A, then place the result in Column C.

I have started the code like this,

For RowNdx = Selection(Selection.Cells.Count).Row To Selection(1).Row +
1 Step -1
If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value
End If
Next RowNdx

I need help. Thanks.

Faye Larson

Hi, I want to write a VBA macro in Word that open an Excel file, save that Excel file to another file (to another name), and then clear all content of the original Excel file. How to do that? Thanks!

How to convert Columnar Data Range in UPPERCASE using VBA and not formulas?

Dear Forum,

In a Table I need to have certain Columns of Data in UPPER case, so how do I do the same for existing data , I know about the UPPER function but I want to convert the data which is already there with a Macro.

Also, need some suggestions on using the COUNTA function in VBA..I will be copying data from an Company Application in Excel..

I need to make this Data look neater with BORDERS only till the last filled Row and the remaining portion will not have any GRIDLINES..

I am doing this right now, as am sill no good in VBA but applying the Functional approach..

Dim DataRng As String
DataRng = "2:" & WorksheetFunction.CountA(Range("B:B"))
BorderRng ="A1:M" &WorksheetFunction.CountA(Range("B:B"))

Rows(DataRng).RowHeight = 31.5

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

'=====The code for geting Borders===== This works, however the approach is not VBA oriented so can someone also help me on this as well as getting an Entire Column into UPPERCASE?

This somehow does not work

UCase (Range("B2:B516").Value) Warm Regards

I have a Worksheet_SelectionChange event handler that manipulates som
graphic objects – usually not a problem. However, if the user ha
selected a cell, done a copy or cut and then clicked on another cell i
anticipation of doing a Paste, then my macro screws up the pending cu
or copy range. I want to save the copy range on entrance to the macr
and restore it just before exit, but the target parameter to th
SelectionChange event handler is the new range. How can I find th
range that was selected before the user clicked on the new cell

Message posted from

hello, I have created a sheet where a user can enter a large range of data for a cash out system on a sheet named (CashOut). I need to save all the data entered into a database that I have created on another sheet (DB). what I've done is created a sheet called (CashOutDB) and organized all the information that was entered from (CashOut) in one row (row2) across 200 columns. I have a bit of code that is saving this row to the (DB) sheet. my problem is that it is saving the formulas from (CashOutDB) and not the data that is there. once the user hits save on the (CashOut) sheet all the entered data is reset to allow the user to cash out the next person/bar and therefore I loose the information on the (DB) sheet. to sum it up, my code right now is only returning the formulas and not the values. is there an easy fix to allow it to return the values and not the formulas? I'd like to keep this organized range and not have to add each cell into the code.

    Dim iRow As Long 
    Dim ws As Worksheet 
    Dim ws2 As Worksheet 
    Dim rng As Range 
    Set ws = Worksheets("DB") 
    Set ws2 = Worksheets("CashOutDB") 
     'check if data already there
    On Error Resume Next 
     'find date
    Set rng = ws.Columns(1).Find(ws2.Range("A2").Value, LookIn:=xlFormulas) 
    On Error Goto 0 
    If Not rng Is Nothing Then 
        iRow = rng.Row 
        If ws2.Range("B2").Value = ws.Cells(iRow, "B").Value And _ 
        ws2.Range("C2").Value = ws.Cells(iRow, "C").Value And _ 
        ws2.Range("D2").Value = ws.Cells(iRow, "D").Value Then 
            MsgBox "Duplicate" 
            Exit Sub 
        End If 
    End If 
     'find first empty row in database
    iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row 
     'copy the data to the database
    ws2.Range("A2").Resize(, 200).Copy ws.Cells(iRow + 1, 1) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
thank you ahead of time for any help.


Is it possible to write vba code to copy the range A1:A20 from my workbook to a new workbook? Thank you

I don't know how to write this VBA in this condition
(1) Click the "Open" button to select the multiple csv files
(2) I had done the multi-selection command as below:

Sub Test1()
 Dim Finfo As String
 Dim FilterIndex As Integer
 Dim Title As String
 Dim Filename As Variant
 Dim i As Integer
 Dim Msg As String

' Setup lists of file filters
 Finfo = "Comma separated Files (*.csv),*.csv," & "All Files (*.*),*.*"

' Display*.* by default
 FiterIndex = 5

' Set the dialog box caption
 Title = "select a File to Import"

' Get the filename
 Filename = Application.GetOpenFilename(Finfo, FilterIndex, Title,   MultiSelect:=True)

' Exit if dialog box canceled
 If Not IsArray(Filename) Then
 MsgBox "No file was selected."
 Exit Sub
 End If

'Display full path and name of the files
 For i = LBound(Filename) To UBound(Filename)
 Msg = Msg & Filename(i) & vbCrLf
 Next i
 MsgBox "You selected:" & vbCrLf & Msg
 End Sub
(3) The selected files will show in the list box. Use the mouse to select the file(s) in the list box that you want to open. When click the "Run" button, it will load the selected csv file(s). Or click the "Delete" button to delete the selected file(s) from the list.

Thx for anyone help~~ It is very important for me~~

EDIT: cleaned up html tags - Moderator

How to use VBA to copy this code from one opened workbook to all opened
workbooks ? I need this code under "Thisworkbook" object of each opened
workbook. Thank you!

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
Application.SendKeys "{F9}"
End Sub

Amolin's Profile:
View this thread:

How to use VBA to copy this code from one opened workbook to all opened workbooks ? I need this code under "Thisworkbook" object of each opened workbook. Thank you!

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.SendKeys "{F9}"
End Sub

Hello folks,

I am trying to figure out how to use a loop to generate multiple reports from one source spreadsheet. Below is an example of what I would like to do:

Master Spreadsheet:

School Network Attendance Rate
1 A 96
4 A 95
3 B 84
7 B 90
2 C 92
5 C 98
Data is sorted only for Network A, copied, pasted and saved onto a new workbook.

School Network Attendance Rate
1 A 96
4 A 95
What I've been doing is recording a macro for all the steps to save the data for one network, copying the VBA code and pasting it over and over again for the other networks. I want to know if there is loop that can do this for me.


Does anyone know how to write a macro in an Excel file which can copy and
append the data in an Excel worksheet to an existing Access file? Also at the
same time, a graph in that Excel worksheet is linked to a PowerPoint file. So
when the macro is run, I hope both the Access and PowerPoint files will be
updated automatically.

Since I am doing some works which require data analyisis by using Excel, I
currently need to copy and append the data to the Access database manually.
Also I copy the graph from the Excel file to the PowerPoint file manually as
well. It would be great and save a lot of time if one macro in Excel can
manipulate between Excel, Access and PowerPoint?

Thanks and regards,

How to write a Series (Sr.No) for Spaced Data using VBA?

Dear Forum,

I am making a Pivot Table using a Macro however I want to add 2 Extra Columns in the Matrix once the Pivot Table is completed...

Sr.No ShowRoom Co-ordinator Sub Sr.No. Client's Name

In the above column headings the Column A has Sr.No , Col B ShowRoom Co-ordinator, Col C will have Sub Sr.No. and Col D will have Clients Name..

This is a Pivot tble pivotted on the ShowRoom Co-ordinator , the Col's A and C are added dynamically after the Pivot Table is created, I want a Running Series for the ShowRoom Co-ordinators as there would be gaps between them and for the Sub Sr.No. in the column C I need a Series which begins from 1 every time there's a new ShowRoom Co-ordinators entry in the Col B..It will be a Series for all the Clients handled by a particular ShowRoom Co-ordinator.

IF possible please help in setting the Borders..this will make the pIvopt table more meaningful.

Warm Regards

Please help...

I need to know how to write a function or macro to copy the contents of cell
(A1) to cell (C1) and contents of a cell (B1)to the comments of cell (C1).

i.e. A1 copied to C1; B1 copied to the comments of C1

Thanks for your help

I need to copy some ranges of some rows to another worksheet. I need to specify the range using cells, since I later need to change the target rows and columns during the program run. However I cannot for my life understand why this works:

NYDATA and MADDATA are two worksheets in the same workbook

NYDATA.Range("A1:L1").Value = MADDATA.Range("A1:L1").Value

But this, using Cells, which I need, doesn't:
a = 1
L = 12
NYDATA.Range(Cells(1, a), Cells(1, L)).Value = MADDATA.Range(Cells(1, a), Cells(1, L)).Value

How can I then copy ranges without specifying the cells in beforehand.

PS How do I make the code part look better in my posts, the (code) tag only alters the font?

I need help on how to write a formula to compare two columns of results and
return a text field answer.
Column A $32
Column B 65%
Any company listed that is >$32 AND >65% will return "Corporate Gold" in
Column C. Thanks!

How to count the column value and convert to Row in Excel VBA
A B C 2 3 3 A A B B B C C C

I need help on how to write a formula to compare two columns of results and
return a text field answer.
Column A $32
Column B 65%
Any company listed that is >$32 AND >65% will return "Corporate Gold" in
Column C. Thanks!

Hi everyone

This forum is very good and helpful for people like me whose knowledge of vba is very little. Whenever I have problem of ' how to' , I would come here and air my trouble. Almost all the time, my problems are solved instantly . I am thinking how and where do I learn vba in whole way. I find the syntaxs are simple and easy to read but sorry usually they are not undstandable. for example like "istext" or "unique=true" etc etc. These words are ok to read but I must say most of them are strange to me. Would someone tell me where and how do I learn vba? I also have bought some book of excel and vba, but I can only find some samples close to the case I need and copy.


I have a vba macro which defines a range such as below:

What i want to do is highlight this range "rngSourceData" so that i can see what cells make up this range.......but
i keep getting an error .

Any ideas how to select/highlight this range?