Hi sir. I have a query. Here is an example: cell A1 contains value 20. Cell A2 = A1 / 2. Now I would like to develop a function which does goal seek like this: For what value of A1, A2 = say, 100. My Goal Seek value is in cell A3.I can do this using "data - what if analysis - goal seek". But I would like to develop a function with three parameters ( SetCell as range, GoalValue As Double, ChangingCell As Range) which when entered in A4, will change values in A1 & A2 accordingly. Deepak Kamtikar
sir. i have problem. how to move filtered data and add the data into another sheet. and then empty was filterd data source will be deleted . then the new one table will be display without the data filter was. exmple i have data: a b c d e f vertikal data table. i want filter and move the data b e into other sheet then the data source will be: a c d f
This Excel tutorial will guide: www.exceltrainingvideos.com/automatically-cut-paste-data-rows-from-an-excel-worksheet-into-another/ Or search for more Excel VBA help: www.exceltrainingvideos.com
Hi Sir, if i want to take criteria from another workbook and filter in other workbook then what i need to do because in that case Destination: ws.Range("X2") is not working where ws is other workbook sheets could you please suggest smething
@@Exceltrainingvideos But this is in same workbook, i have data on another workbook and want to filter from other one....Could you share me your id so that i can share my doubts ...Thank you
Good morning sir I have seen your video, I have one Excel worksheet where the data is more than 5 lakes. I have to use filter in 3 collunm user ID, transect ID and the name filter copy make new folder new workbook paste save and close. I had made one VBA code which one is taking that much of time how much is required to do manually. Can you help me on that? How to do in short time.
You can try advanced filter: www.exceltrainingvideos.com/extract-data-from-one-excel-worksheet-to-another-using-advanced-filter-with-vba/ How can I make my VBA code run as fast as possible? Here are a few tips: 1. Be sure to declare all your variables as a specific data type. (Use Option Explicit in each module’s Declarations section to force yourself to declare all variables.) 2. If you reference an object (such as a range) more than once, create an object variable using the Set keyword. 3. Use the With-End With construct whenever possible. 4. If your macro writes data to a worksheet and you have lots of complex formulas, set the calculation mode to Manual while the macro runs (but make sure you do a calculation when you need to use the results!). 5. If your macro writes information to a worksheet, turn off screen updating by using Application.ScreenUpdating = False. Search www.exceltrainingvideos.com/
Hi Sir, What would be the VBA code to auto filter my data if my criteria is current week plus 2 weeks ahead. for example if I am trying to filter my data on date 18.01.2018 then it should auto filter current week i.e., 14,01.2018 and 2 weeks ahead i.e., 21.01.18 and 28.01.18. so when I run the vba in any other week then it should automatically filter that particular current week plus 2 weeks ahead. Please help.
hi sir, amazing video ,, i have doubt, in this concept, how to search(copy/filter data--amount should be given in textbox) criteria using textbox and cmd button ..
Hello Sir, I follow your training's regularly. I am trying make automate my testing regular status report which we need to take from Test Automation management tool HP ALM then after we have to do some modifications in excel. Here the task is , once i import data from ALM , we have defects primary status & Severity in two columns. here primary statuses data column does have (Assigned,Closed,Deferred,Fixed,Open,Rejected,Reopen).& Severity column data statues like (Critical,High Medium,Low ). Here the task is i need to find out primary statues count for each severity status wise. after that we have to place them in separate sheet in the form grid. the grid is designed like in form of horizontal & vertical shape. Horizontal row contains Primary status and vertical column contains Severity data. Can you help me out how data is divided in earlier sheet and place them in second sheet .?
Very helpful vidz sir,Congrats!Here is my case now.i am a bookkeeper and trying to create my own workbook.I have an accounts receivable column in sheet 1.How can i transfer payment received from certain client that i recorded in my accounts receivable column to sheet 2 which is the accounts receivable ledger.clients pay number of times per month.Please help using VBA.thanks a lot and more power...
Sir Firstly I appreciated your work and efforts in Excel VBA and Now I want to copy active row from shit one in sheet two in next available blank available sheet by Short key like there are various rows of data in sheet one I find the data with find tool on row 50 and want to past this row in sheet two with short key of Ctrl+space is it possible
Hey Dinesh Sir! I want to ask that, can make any ledger in excel as like other accounting software, that we can get opening balance and ending balance, periodic view or as per sheet changes
How can i do this for multiple worksheets? This code works well for copying data to one sheet but if i try to replicate the formula to cover more than one sheet, the results are not as expected.
Hello, I have a quick question on this. Instead of filtering on column Salary, i want to filter on column A i.e. Name and based on all the names in column A i want to create different cuts within the same worksheet. for ex. if i filter on name "Ram" then all the data related to ram should be copied to a new sheet with Sheet name as Ram. how can we do it?
Hi sir i'm facing one problem please help me " i have master workbook here some empty cells, we need to update data automatically empty cells using same workbook other sheets data. by using VBA pls help me sir. thank you.
+Raghavendra Rao The Resize property makes the range a specific number of rows and columns larger or smaller so that we can copy the filtered range only.
+Raghavendra Rao Hello Raghavendra. I saw your VBA posts on this blog: www.myengineeringworld.net/2014/05/pdf-search-through-vba.html I appear to be having the same problem that you had regarding the automation error. Could you email me please as I'm hoping you may have found the solution. mikebrooksusa@hotmail.com Thanks.
Hi, I am getting a Run-Time error '91' Object variable or With block variable not set. This occurs With ActiveSheet.AutoFilter.Range. do you have any idea why this has happened? thanks in advance
These links to the relevant Excel VBA lessons will help: www.exceltrainingvideos.com/counting-and-displaying-totals-automatically-using-excel-vba/ www.exceltrainingvideos.com/consolidate-data-using-vba/ Or search this channel or www.exceltrainingvideos.com
is there any simple process without VB , i want to get the total entry in the first sheet , for ex sheet 2 (day 2 )+ ,sheet 3(day 3) + sheet 4 (day 4) etc, ?? thanks
Dear Sir, I am Liazul Islam, I am a great fan of you. I used this Technic and its work. But when there is no filter result, I mean filter criteria1 not match then it copy others data row. How can i solved it.
Have you placed apostrophes before the two lines of code like so: 'Sheet2.Range("A1") = "name" 'Sheet2.Range("B1") = "salary" You should remove the apostrophes. Or. check your code again because if the criteria is not met the autofiltrng will be nothing and you should receive the message: "no data available for copying!" This link will help: www.exceltrainingvideos.com/copy-auto-filtered-data-to-another-worksheet-automatically-with-vba/
Dear Sir, Thanks for replaying me. I use your code on my another workbook. It is creating customer Ledger. I have 3 data sheet which i get from my sql server. I am trying to create customer Ledger this data sheets.
Necesitaba conocer la dirección del rango filtrado. y después de muchas busquedas, hallé la solución aquí. Muchas gracias por compartir sus conocimientos
@@Exceltrainingvideos sir you didn't understand my question. I want to select a cell after filter. Whatever first cell will visible after filter I want to select that
there is error showing ["run time error "9" subscript out of range] while runnin' the code and debugging goint to the worksheets("sheets2)".cells.clear...help me pls asap
Sub Back_Log_Report() Dim rng As Range Dim autofiltrng As Range Dim Rg As Range Set Rg = ThisWorkbook.Worksheets("Backlog_Report").Range("A1").CurrentRegion Rg.Offset(1).ClearContents Sheets("PROJECT #'S").Activate With ActiveSheet .Range("B1").AutoFilter Field:=8, Criteria1:="Active" End With 'On Error Resume Next With ActiveSheet.AutoFilter.Range On Error Resume Next Set autofiltrng = .Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) On Error GoTo 0 End With If autofiltrng Is Nothing Then MsgBox "no data available for copying" This is my current code up to that point for reference.
I was able to make it run again by adding "With ActiveSheet .Range("B2").Select End With before the activesheet.autofilter.range line as a common start point before the offset command.
