VBA Projects

Full Access with Source Code

  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design


Project Management Templates

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Effortlessly Manage Your Projects

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

Share Post


Its one of my first questions when I started learning EXcel VBA, How to Read or Get Data from Worksheet Cell to VBA? To automate any thing in excel, first we need to read the data in Worksheet and based on that we can execute the next steps.

Read or Get Data from Worksheet Cell to VBA in Excel – Solution(s):

It is very simple to read the data from Excel to VBA. We can use Cell or Range Object to refer a Worksheet Cell.

Get Data from Worksheet Cell – An Example of using Cell Object

The following example will show you how to read or get the data from Worksheet Cell using Cell Object.

Example Codes

In this example I am reading the data from first Cell of the worksheet.

Sub sbGetCellData()

MsgBox Cells(1, 1)
'Here the first value is Row Value and the second one is column value 
'Cells(1, 1) means first row first column
End Sub

In this example I am reading the data from first row and fourth column of the worksheet.

Sub sbGetCellData1()

MsgBox Cells(1, 4)

End Sub

Here is sample picture, which helps you to understand this concepts. Any Row or Column number start with 1 and You have to specify Cell(RowNumber,ColumnNumber) to read the data from a Cell of the Worksheet.

Reading data from Cells

Get Data from Worksheet Cell – An Example of using Range Object

The following example will show you how to read or get the data from Worksheet Cell or Range using Range Object.

Example Codes

In this example I am reading the data from first Cell of the worksheet.

Sub sbGetCellData2()

MsgBox Range("A1")
'Here you have to specify the Cell Name which you want to read - A is the Column and 1 is the Row

End Sub

Here is sample picture, which helps you to understand this concepts. If you select any cell in the worksheet, you can see the name of that cell in the Name Box.

Reading data from Cell Range

Get Data from Worksheet Cell – Specifying the Parent Objects

When you are reading using Cell or Range object, it will read the data from Active Sheet. If you want to read the data from another sheet, you have to mention the sheet name while reading the data.

The below example is reading the data from Range A5 of Sheet2:

Sub sbGetCellData2()

MsgBox Sheets("Sheet2").Range("A5")
'Here the left side part is sheets to refer and the right side part is the range to read.

End Sub

In the same way you can mention the workbook name, if you are reading the data from different workbooks.

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: Excel VBATags: Last Updated: June 17, 2022


  1. Magesh February 18, 2014 at 3:02 PM

    I want continue number from A1 to A10 but only while am typed in B1 to B10 like
    A1 1 Magesh
    A2 2 Kumar
    A3 3 Sathish
    A5 4 Raja
    A6 5 Ram
    A7 6 Raju
    A8 7 Samy

    A9 8 Prabu
    A10 9 Raja

    A11 10 Magesh

    how to put this number alternately in Ms Excel 2007 using VBA.

    with regards,

  2. PNRao February 18, 2014 at 11:27 PM

    Hi Magesh,

    You can use the following code. Paste this code in your worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    'print if B is not empty and A is empty
    If Cells(Target.Row, 2) <> " And Cells(Target.Row, 1) = " Then
    Cells(Target.Row, 1) = Target.Row
    End If

    End Sub


  3. praveen August 7, 2014 at 12:55 PM


    This information is really helpful; I have one question:
    1. I want to read the data from cell(i.e sheet2-Row1)
    2. It has to read Unique record from Row1
    3. It has to give count in sheet1 as summary based on criteria


  4. PNRao August 17, 2014 at 11:25 AM

    Hi Praveen,

    You can use Worksheet functions to perform this task. You can use CountIf or SumIf functions to create summaries based on criteria.

    For Example:
    cnt = Application.WorksheetFunction.CountIf(Range(“A1:A10”), “Your Criteria”)


  5. fakru October 3, 2014 at 3:35 PM

    how to read data from a any random selection

  6. PNRao October 3, 2014 at 8:49 PM

    You can use Selection.Value.

  7. jai October 10, 2014 at 12:10 PM

    I want to read all the rows from a cell in one sheet and compare with all the rows in other cell in another sheet and each rows should be compared and it is a string value present in the rows..please help me out in this.

  8. Tsuna November 27, 2014 at 3:34 PM

    Can somebody help me with this gentlemen,

    I have a User form with 5 textboxes, I want to put the value of a cell in the textboxes but the value in each textboxes are from different sheets. Can anyone help me with this situation.

    Thank you in advance.

  9. PNRao November 29, 2014 at 7:35 PM

    Hi Tsuna,
    The following code will get the values from 5 different sheets to 5 different text boxes:



  10. suraj December 10, 2014 at 12:51 AM

    Need help. I want to create a new sheet in an existing workbook. But the name should be that i enter in cell B2 and then click on a create button. ‘actually i want for multiple values but will use the same logic. Anyone please???

  11. sima December 27, 2014 at 7:48 PM

    i want to create a bottom when pressing, the data of the current sheet transfer to my matrix in my program,is it possible?
    thanks in advance.

  12. Shareef January 4, 2015 at 9:56 PM

    The VBA codes regarding macros ex: copytopaste macro it works sometimes n some times it shows error user type not defined. Pls help me out. M getting irritation because berceuse of this.

  13. PNRao January 6, 2015 at 7:57 PM

    Hi Shareef,

    Requesting you to paste the code which is not working.


  14. Manooj January 20, 2015 at 2:25 PM

    can any one help me with the below senario

    I hav 3 sheets in the excel and i need to take a particular value from sheet one and subtract it with a particular value from sheet 2 and this shouls be displayed in sheet 3.


  15. PNRao January 23, 2015 at 10:47 AM

    Assuming you want to subtract A2 of Sheet1 from A2 of Sheet2 and print at A2 of Sheet3:

    Sheets("Sheet3").Range("A2")= Sheets("Sheet2").Range("A2") - Sheets("Sheet1").Range("A2")


  16. N. Panchal March 19, 2015 at 11:48 AM

    Consider below scenario…
    In Row 1 Starting from B1, C1, D1…I have entered date in dd/mm/yyyy
    In Column 1, Starting from A2, A3, A4, I have empolyee Code… like EMP1, EMP2 etc..
    Now, from third party application I want to access this sheet using VBA and based on current date(From first Row) and EMP code (From First Column), I want to read corresponding value in that Cell, meeting corresponding.row and column.
    Required EMP code can be entered from my application to a variable in VBA.
    Anyone can me help out with this..

  17. Sarika April 16, 2015 at 10:47 AM


    I am looking to write a script in excel to get the data from one sheet to another sheet in different way.

    eg: input

    emp m1 m2 m3 m4
    abc 1 2 3 4
    def 5 6 7 8
    ghi 9 10 11 12

    ouput should be like the below :

    abc 1 m1
    abc 2 m2
    abc 3 m3
    abc 4 m4
    def 5 m1
    def 6 m2
    def 7 m3
    def 8 m4

    like wise there are “n” number of emps..

    Please help.


  18. sowmi April 17, 2015 at 7:46 AM

    it is wonderful. I am having one problem. We are having 600 rows and 12 column worksheet. Can I have a code for locating a particular value (key value) in the sheet and to displa ay the values of the row.

    The same as our friend Panchal requires

  19. Bhamila April 20, 2015 at 2:24 PM

    Can you Help me to write a code to arrange the letters like below.

    Lets say if my input is 01234567890 – then the result has to be 34567890.012 and vice versa.

    But the program has to read the input from excel sheet automatically if I entered 1000 value in A column.

  20. Haider April 29, 2015 at 12:31 AM

    Hi please help me with this one:

    The code below should read all value from cell P1:P4 and implement it in an autofilter…Thanks for your help in advance

    Sub c1()
    MsgB Range(“P1”)
    MsgB1 Range(“P2”)
    MsgB2 Range(“P3”)
    MsgB3 Range(“P4”)

    Dim i As Long
    For i = 2 To 13
    With Sheets(i).Activate
    ActiveSheet.Range(“$Y$2:$Y$2999”).AutoFilter Field:=1, Criteria1:=Array(MsgB, MsgB1, MsgB2, MsgB3)
    On Error Resume Next
    End With
    Next i
    End Sub

  21. SADDAM HUSAIN May 15, 2015 at 12:36 PM

    i want to open a browser windows in excel sheet . how will i open??

  22. tanvi May 19, 2015 at 4:17 PM


    I have below requirement:

    Sheet1: Two cols
    Col1 Col2
    A 1
    B 2
    C 3

    Sheet2: Two Cols
    Col1 Col2
    A 2
    D 3
    C 4

    Read Col1 -Lets say A from sheet 1, (loop thru each row in Col1), search the first value of Col1 which is A in sheet2 and get its corresponding Number which is 2 and write this in sheet1 in Col3.

    Sheet1: 3 cols
    Col1 Col2 Col3
    A 1 2
    B 2 NA
    C 3 4

    Can Anyone please help with this question.

    Thanks so much!

  23. PNRao May 22, 2015 at 9:13 PM

    Hi Tanvir,
    We can use two for loops to do this, but using worksheet function will be simpler. Here is the macro to Search For A String And Get The Corresponding Value From Other Sheet.

    Sub sbSearchForAStringAndGetTheCorrespondingValueFromOtherSheet()
    lRowSheet1 = Sheets("Sheet1").Range("A100000").End(xlUp).Row ' Please refer the code finding the last row in our 100+ Example
    For i = 1 To lRowSheet1
    If Sheets("Sheet1").Range("A" & i) <> " Then
        If Not IsError(Application.WorksheetFunction.Match(Sheets("Sheet1").Range("A" & i), Sheets("Sheet2").Range("A:A"), 0)) Then
            Sheets("Sheet1").Range("C" & i) = Sheets("Sheet2").Range("C" & Application.WorksheetFunction.Match(Sheets("Sheet1").Range("A" & i), Sheets("Sheet2").Range("A:A"), 0))
        End If
    End If
    End Sub


  24. Rajesh B June 11, 2015 at 5:31 PM

    Hi Friends, Please me in this, I have a requirement ike if we have one Excel file which we have list of computers name

    I wants to cut this www for all the rows and I have to put it in another excel sheet . How to do this with VB script. Please help me , thanks in advance.

  25. PNRao June 12, 2015 at 6:10 PM

    Hi Rajesh,

    Here is the code which will help you to solve your requirement.

    Sub MoveAndChangedData()

    ‘Here moving original data from Sheet1(Source Sheet Name) to Sheet2(Destination Sheet Name)
    ‘You can change the range(“A1:D10”) according to your requirement
    Sheets(“Sheet15”).Range(“A1:D10”).Copy Destination:=Sheets(“Sheet16”).Range(“A1”)

    ‘Replacing “WWW” with blanks in the destination sheet
    With Sheets(“Sheet16”)
    .Range(“A1:D10″).Replace What:=”WWW”, Replacement:=”, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    End With

    End Sub


  26. Rajesh B June 15, 2015 at 4:33 PM

    But Friend, It does not work throws error and i corrected the uotes which is in different format in the excel when i tried copy paste from here, after corrected its throws run time error like Run time error : 9 subscript out of range like that,.

  27. bujar June 27, 2015 at 10:19 AM

    Hello everyone,

    I have an excel master sheet that is created for every customer filename will change each time, the workbook layout is the same. I would like a macro to import some of the information from the master sheet, I have 3 sheets in the workbook, from each sheet I would need to copy few cell data (any range would be fine, i will change that to fit by needs) to a new workbook. I would like the macro button also to move down each time the new data is imported. New Workbook data range paste to A2:D2, next A3:D3 and so on. Below is the code i have so far.

    Thank you in advance,

    This is the Code I have.

    Sub Foo()
    Dim vFile As Variant
    Dim wbCopyTo As Workbook
    Dim wsCopyTo As Worksheet
    Dim wbCopyFrom As Workbook
    Dim wsCopyFrom As Worksheet

    Set wbCopyTo = ActiveWorkbook
    Set wsCopyTo = ActiveSheet
    ‘Open file with data to be copied

    vFile = Application.GetOpenFilename(“Excel Files (*.xl*),” & _
    “*.xl*”, 1, “Select Excel File”, “Open”, False)

    ‘If Cancel then Exit
    If TypeName(vFile) = “Boolean” Then
    Exit Sub
    Set wbCopyFrom = Workbooks.Open(vFile)
    Set wsCopyFrom = wbCopyFrom.Worksheets(1)
    End If

    ‘Copy Range
    wsCopyTo.Range(“a2”).PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    ‘Close file that was opened
    wbCopyFrom.Close SaveChanges:=False

    End Sub

  28. JOTS July 7, 2015 at 9:20 PM


    I have the names of all the workbooks which are listed on a worksheet called”filelist” saved in a workbook called “Read property and transaction data with macro to be run 070715.xlsm”. I need to get the workbook to open for all the the names listed in filelist worksheet and go to the tab called “GL Transactions” and retrieve a value from call AB12 and save it in the workbook “Read property and transaction data with macro to be run 070715.xlsm” under a tab called GL Total. How can I record a macro for this?

  29. Deepan August 24, 2015 at 2:31 PM

    How to retrieve a particular percentage of value(ex:get only 20% of data from sheet i.e out of 20 users,we need only 4 users name to be displayed) from excel sheet using macro?

  30. PNRao August 25, 2015 at 12:37 AM

    Find the last row and loop through the 20% of last row to fetch only 20% of the data.


    Sub sbGetOnly20PercentOfData()
    Set SourceWb = Workbooks.Open("C://....") ' your file path
    SourceWbLastRow = 500 'See our example in 100+ popular macro list to find last rows dynamically in different scenaios
    rows20percent = CInt(SourceWbLastRow * 0.2)
    For i = 1 To rows20percent
    Sheets("DestinationSheet").Range("B" & i) = SourceWb.Sheets("SheetName").Range("A" & i)
    Next i
    End Sub


  31. Ajay October 7, 2015 at 3:21 PM

    Hi PNRao Sir,
    In the above example by you i am able to find only 1 data in excel sheet, i need to find many data in excel sheet so how will be do this can you please help me this
    If there are many data in excel sheet in need to find all data from Sheet In Particular TextBox

  32. Ajay October 31, 2015 at 12:22 PM

    It’s a lot more easier if you just write a formula for this instead of VBA code.

  33. Pkaran December 14, 2015 at 7:02 PM

    I have a excel file contains in the cell AI one, A2 two, A3 three,… I want to copy the value in another excel one in the sheet1 cell A1, two in the sheet2 cell A1, three in the sheet3 cell A1,…

  34. Younis January 12, 2016 at 1:01 PM

    I would like the macro button which is importe new data from sheet1 and paste on sheet2 A2:D3 but when new data inserted in sheet1 the range of sheet2 will be A3:D3 and so on.

  35. Younis January 20, 2016 at 12:45 PM

    I would like the macro button which is importe new data from sheet1 and paste on sheet2 A2:D3 but when new data inserted in sheet1 the range of sheet2 will be A3:D3 and so on.

  36. AlexC February 13, 2016 at 3:44 AM

    I want to select and print the worksheets in a workbook whose cell, (I22), contains a value greater than one (1).

    Thank you

  37. Ahmed March 20, 2016 at 1:47 PM

    i have excel sheet need to create sub menus , when select value its creates sub menu , when select value its generate another menu with formulas & conditions

  38. Ahmed March 20, 2016 at 1:53 PM

    Hi Developer
    i have excel sheet with formulas & conditions , i need to generate menus and sub menus , when select value its generate sub menus with conditions & formulas , i need to generate 10 menus based on cell value

    could you please help me ?

  39. nht March 23, 2016 at 10:21 AM

    i have problem: i have sheet “abc”, in sheet “abc” have colum A
    I want count num “NG” in sheet A
    Plz Help me!!

  40. fab March 28, 2016 at 12:47 PM

    Sub main()
    Dim Newsheet As Worksheet
    Dim val As Double
    Dim Firstrow As Integer
    Dim Lastrow As Integer
    Dim totscore1 As Integer
    Dim i As Integer, j As Integer
    Firstrow = ActiveCell.row
    Lastrow = ActiveCell.row

    totscore1 = Lastrow – Firstrow
    Firstrow = ActiveCell.row
    Lastrow = ActiveCell.row

    For i = 0 To totscore1
    val = Cells(2, 3).Value – Cells(2, 4).Value
    ActiveCell.Value = val
    ActiveCell.Offset(1, 0).Select
    Next i
    End Sub

    The code is working only for the cells D2-B2. And the same value getting printed. I want the results down the column (Relative referencing).. D3-B3, D4-B4… so on. Please help

  41. Mark April 25, 2016 at 7:51 PM

    Hi Vali,

    You have provided quite insightful answers to all questions posed, please help me with mine:
    This is the rudimentary format for my excel:

    Name Place Gender Date occupation
    Arthur UK Male 16-4-78 Carpenter
    Brad USA Male 5-10-80 Teacher
    Charlie Poland Male 13-8-69 Painter

    I’m trying to export this to a text file the result of which should look like this:

    Arthur UK
    Gender: Male
    Date: 16-4-78
    Occupation: Carpenter, Mason, Plumber

    Brad USA
    Gender: Male
    Date: 5-10-80
    Occupation: Teacher

    Charlie Poland
    Gender: Male
    Date: 13-8-69
    Occupation: Painter, Singer, Editor.

    have searched high and low, but in vain.

    The actual excel on which I’m working consists of 11 Columns, and have to deal with 300 to 350 rows at a time, which is a cumbersome task.

    Please help me with this.

  42. Archan Pagedar May 4, 2016 at 3:02 PM

    Hi nht,

    You can use formula “COUNTIF” for this.

  43. Rutger May 6, 2016 at 7:02 PM

    Hi, I want to add data from cells to an inputbox, do you have any clue on how to do that?
    thanks in advance!


  44. Deepak May 30, 2016 at 5:08 PM

    One Excel File (Table 1) containing Roll Nos. of candidates with other details like name, address, contact No., Email Id etc. for 2000 candidates and other Excel File (Table 2) containing only Roll Nos. of 200 candidates which are same as in Table 1 so I want to add other column details in Table 2 (only for 200 candidates) by retrieving records from Table 1 by searching Candidates Roll Nos. from Table No. 2.

    How it can be possible in Excel.

  45. Spyrijus June 10, 2016 at 12:31 AM

    Ok. So this problem is getting on my nerves for some time now. I made damage calculator using macros. Works pretty good. Now I want to add temporary hit points into account.
    Let’s say it looks like this:

    DMG TotalDMG TempHP HP
    2 3 7 24

    So, basicaly, I need to deplete TempHP first and with every dmg calculation to see if there is any TempHP left. So with every calculation I need to check the value of TempHP cell and DMG cell, then do TempHP – DMG (7-2) and the result should be new TempHP value (5). And when DMG is bigger than TempHP do the following: HP – (DMG – TempHP) and set TempHP to 0.

    Thanks in advance.

  46. Awanish August 16, 2016 at 11:56 AM

    Hello Expert

    I need to read value from column A excl sheet and export those value in txt file .

    Thanks in advance

  47. raja September 28, 2016 at 7:30 PM


    I want to select the data and month from the Date in cell(a1)(containing formula) and file 2016-06-07 ABCD should be opened using that date and month.


  48. Sohan November 18, 2016 at 1:18 PM

    I want a vba,
    I want to copy a cell’s value to another cell, but the cell on which, the value will be pasted, that cell’s number will be directed from another cell.
    Example: I want to copy value from ‘A1’ to a specific cell, and that specific cell’s number is specified on cell ‘B1’. [Note: value of ‘B1’ changes time to time according to formula]

    Please help me how can i do this ! Thank you…..

  49. Durai November 20, 2016 at 3:45 PM

    Dear Sir,

    I have some ComboBox – 8No’s and TextBox -40No’s in Sheet1 and our employee details (Emp.ID, Name, Date of Birth, Email, Contact Numbers) in Sheet2. I need to display employee id in Combobox 1 to 8 and when i select anyone employee id in ComboBox then his all details wants to display other Testboxes in Sheet1 as per below. But (Sr.No) not in Sheet2 database. Anyway i need to display when i select second Combobox, then Sr.No wants to display number-2 automatically. Kindly anyone give a solution to solve my query.

    Sr.No Emp. ID Name Date of Birth Email Contact No.
    1 1201 AAAAAAAA1 12/12/1980 dfs 1111111111
    2 1202 AAAAAAAA2 12/13/1980 dfs 1111111112
    3 1203 AAAAAAAA3 12/14/1980 dfs 1111111113
    4 1204 AAAAAAAA4 12/15/1980 dfs 1111111114
    5 1205 AAAAAAAA5 12/16/1980 dfs 1111111115
    6 1206 AAAAAAAA6 12/17/1980 dfs 1111111116
    7 1207 AAAAAAAA7 12/18/1980 dfs 1111111117
    8 1208 AAAAAAAA8 12/19/1980 dfs 1111111118

  50. Sasi November 20, 2016 at 5:58 PM


    i have an excel workbook which will be update frequently by inserting new worksheet. I want to get the value of specific cell in each sheets and send to one particular sheet to draw the graph once i insert new worksheet. Are there any VBA codes to send the cell value to specific sheet by clicking button?. Please help

  51. Mike January 10, 2017 at 6:24 AM

    Hi, this is wonderful platform and thank you all for yours comments! I’m pretty new in VBA and I’m finding this very helpful. Supposing I have excel file that has huge informations capture in it and these informations could be actions, time the action was done, number of actions performed for several users and are all ranged columns per user. How should write a script to extract this data based on those that user performed with <3 minutes, compute totals number of actions done by all user in specific time slot, categories the number of activities done in range of <2,3-4, 4< time difference. I will be very delighted.

  52. Haivus February 3, 2017 at 9:46 PM

    I have 3 different excel sheets which contain my client reviews with respect to Date as header followed respective dates in 1st column and 2nd column contains reviews as header followed by reviews. .
    so in all files i have same dates for eg: date : 23.06.2016 will have minimum of 30 reviews in each sheets, so wen i combine its taking too much time to open one file and filter and copy vice versa.
    Help me out with VBA coding so that i can pull out those datas easily into my master file with respective to having terrible n doing in this.


  53. Arash June 6, 2017 at 11:21 AM

    Dear All how can I get the value in spread sheet (for example day and month) and use it as reference value to plot the point in VBA?
    Can you please explain it??

  54. YRUYIM August 17, 2017 at 12:28 AM

    Hi Everyone,

    I am trying to genrate text file from Excel via VBA and successful.

    My requiremet is to read data from multiple excell sheets write into text file, I can raead data from sheet1 of excel howver unable to read or move function from sheet1 to sheet2 or sheet3 to get data and write in text file,

    Can anyone help me pleas.

  55. Venus August 19, 2017 at 9:35 PM

    I am trying a scenario in which I have a Mini Invoice and my requirement is to save the data of the invoice and need to retrieve the saved data based on Invoice Number. I request to help me in this scenario giving the desired code.
    Thanks in advance.

  56. yash August 31, 2017 at 3:40 PM

    I have an excel database. I want a person’s all data by using only his / her mobile no. Is it possible by using VBA programming or other programming?

  57. PNRao September 1, 2017 at 11:41 PM

    Yes, Please let us know data structure. So that we can help you.

  58. Kundan November 27, 2017 at 9:55 PM

    Hi there,

    How to read a file stored in excel sheet through vba. I have a file path in cell A1 through A10. How to read each files contents and base64 encode it to store it in a string.


  59. Roberto January 20, 2018 at 11:52 PM

    Hello, I have Category in column A and number of teams in column B and I already can create automatically worksheets with the names on column A, but now I want to paste on each new worksheet the data corresponding to the number of teams in column B.

    I already named the ranges so whenever I have e.g. 6 on column B , I just need to select data range _6 and paste it on A1 on the new worksheet.

    Can you tell me how to code this?

    Thanks in advance!

  60. Colin January 30, 2018 at 4:10 AM

    I have a column of dates from Jan 1 to dec31 2017. The adjoining cell has a value in it
    I am setting targets and need to pull a certain dates value to the target sheet. Is it possible to do this

  61. Tresna Budiman June 5, 2018 at 7:45 AM

    Hi Master,
    Please let me know how to call the sub i made as :

    Sub Sumbycolorto(range_data As Range, ByVal range_tosum As Range, ByVal range2_tosum As Range, ByVal range_toset As Range, criteria As Range)
    Dim datax As Range
    Dim xcolor As Long
    Dim isi As Long
    xcolor = criteria.Interior.ColorIndex

    For Each datax In range_data
    isi = Cells(datax.Row, range_tosum.Column) + Cells(datax.Row, range2_tosum.Column)
    If datax.Interior.ColorIndex = xcolor Then
    Cells(datax.Row, range_toset.Column) = range_tosum.datax.Row + range2_tosum.datax.Row
    End If
    Next datax
    End Sub

    Sub Run_click()
    ‘ How to call ? Sumbycolorto ???
    End Sub

  62. PNRao June 14, 2018 at 10:12 AM

    Sub Run_click()
    ‘You can use Call statement and enter the inputs for parameters: range_data,range_tosum,range2_tosum,range_toset,criteria
    Call Sumbycolorto(range_data,range_tosum,range2_tosum,range_toset,criteria)
    End Sub

  63. Naem July 3, 2018 at 12:13 PM

    Can any one show me how to write a macro which reads values from a sheet with two columns consisting of dates and numerical values and calculates slope from these values but writes value of slope and date when slope changed from positive to negative and visa versa and ignore all values and not write only the date and slope at slope change only.
    Your input is appreciated.

  64. Emil October 2, 2018 at 5:47 PM

    Hello, I have a lots of cells with this function: PrintFile (xxxyyyyssssqqqq.***)
    How can I run those functions within a dynamic range in vba?
    Basically, how can I run a function if it’s written as text in a cell?
    Thank you a lot!

  65. Vikramaditya Chouhan May 10, 2019 at 10:07 PM

    I have name of company in Column one with entries Employees name, salary, phone number in next column.
    The name of the company in first column repeats.

    I dont want the name of the company again and again. Only other entries like Employees Name, salary and phone number in new sheet.
    The Company name should appear once, rest all cells for same company must remain empty.

    Please suggest.

  66. Christopher Allyn Jenkins June 1, 2019 at 8:21 AM

    To anyone out there, I am stuck. I have created a button in Excel, I want to attach a Macro to this that adds up a series of numbers to make a growing total; “A1″+ “A2” – “A3” = Total
    So that if I change A1, A2, A3 it would give me the old total plus the sum of the new A1,A2,A3
    I have no clue what the code would look like please help!

  67. Rupesh December 1, 2019 at 8:36 PM

    Dim strCycle As String, i As Integer, trgSheet As String, sht As Worksheet
    With Sheets(“Run”)
    i = 2
    For Each sht In Sheets
    If sht.Name “Run” Then
    End If

    Do While True
    strCycle = .Cells(i, 1)
    If Len(Trim(strCycle)) = 0 Then Exit Do
    If strCycle = “All” Then
    trgSheet = “All_Summary”
    LoadDetail srcJIRAFile, “select LEFT([Test Summary],INSTR([Test Summary],’_’)-1) as EPIC_ID, x.* ” & _
    “from [Sheet1$] x WHERE Step=’Step1′”, trgSheet
    trgSheet = strCycle & “_Summary”
    LoadDetail srcJIRAFile, “select LEFT([Test Summary],INSTR([Test Summary],’_’)-1) as EPIC_ID, x.* ” & _
    “from [Sheet1$] x WHERE Step=’Step1′ and CycleName in (‘” & strCycle & “‘)”, trgSheet
    End If

    LoadDetail ThisWorkbook.FullName, “select x.* from [” & trgSheet & “$] x,(SELECT [Test Summary], MAX(date+time) as dt” & _
    ” FROM [” & trgSheet & “$] group by [Test Summary]) t where ” & _
    ” x.[Test Summary]=t.[Test Summary] and (”, strCycle & “_Snap”

    If strCycle “All” Then

    LoadDetail ThisWorkbook.FullName, “TRANSFORM COUNT(*) select EPIC_ID ” & _
    “FROM (Select Status,EPIC_ID from [” & strCycle & “_Snap$] UNION ALL ” & _
    “Select ‘Total’ as Status,EPIC_ID from [” & strCycle & “_Snap$]) group by EPIC_ID PIVOT Status”, strCycle & “_Status”
    End If
    If strCycle = “All” Then
    LoadDetail ThisWorkbook.FullName, “TRANSFORM COUNT(*) select CycleName ” & _
    “FROM (Select Status,CycleName from [” & strCycle & “_Snap$] UNION ALL ” & _
    “Select ‘Total’ as Status,CycleName from [” & strCycle & “_Snap$]) group by CycleName PIVOT Status”, strCycle & “_Status”
    End If

    i = i + 1

    End With

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

  68. Chirag Vadara June 5, 2020 at 10:57 AM

    I want to call value from a cell of excel in my VBA code how to do it. Plus i have some other query, i want to get the resulting values of my code in excel back. How it can be done

  69. Kumar S October 21, 2020 at 2:42 PM

    Hi – I am totally new to excel macro. I give below my macro. i would like to write the macro in one file and execute the same many times on the same day based on some criteria.

    My data has City name, date of transaction and other data spread over a few columns .

    1. I would like to select one file where the all transaction details are available. (Say AllData.xlsx).
    2. City Name will be received as Input at Macro as variable

    3. Need to select and open this file and read the contents row by row and write to another file -(Output File) (this file name to be generated into a variable like – OutputFileName = City Name + “Transactions Till ” + Yesterday Date + “.xlsx”. This file need to be created inside macro with the name assigned.

    4. The data read in AllData should be one row in full (or column by column as you recommend) and based on the first column (city name) matching with City Name received as input.

    5. If matching need to write on Output file one entire row.
    6. Read Next Row from AllData and if matching write to Output file
    7. Above process to be repeated till Last Record of AllData File.

    I tried but not getting correct code. Request you to give me the code and help me asap.

    Kumar S

  70. Hart December 16, 2020 at 2:05 AM

    Hi, I have excel sheet where i record quantity of our fresh products for our market , i need to set up VBA code to auto sent my manager email when quantity is on certain amount . Eg. A1 – PRODUCT DESCRIPTION, A2 – ARTICLE NUMBER, A3 – QUANTITY,
    B1 – BANANA, B2 – 52465, B3 – 47
    C1 – ORANGE, C2 – 63351, C3 – 28
    D1 – PEARS, D2 – 85856, D3 – 14.

    I need VBA to sent email to my manager for all products that are below 30 units accompanied by product description and article number.

Leave A Comment