Posts by haroon2015

haroon2015 · Feb 21st 2017

Re: Worksheet_Change Event Add unique value to Sheets

No its not dynamic,
ABD = Sheets("total").range("B4:B100") And
ABE = Sheets("hide").range("C10:C100")

if we need 5th value then add it to ABD and ABE but only if its a unique value, i mean if this value already not added to ABD and ABE
thanx

haroon2015 · Feb 20th 2017

Re: Worksheet_Change Event Add unique value to Sheets

its a range of cells

haroon2015 · Feb 20th 2017

hi,
i have a range Sheets("entry").range("c10:c300")
i want when i input data in above range then value automatically add to Name Range ("ABD") and Name Range ("ABE") but only unique value

for example
i add in range c10 = "ALAN" Then add "ALAN" To Name Range ("ABD") And ("ABE")
again
i add in range c11 = "BLAN" Then add "BLAN" To Name Range ("ABD") And ("ABE")
again
i add in range c12 = "ALAN" Then Don't add "BLAN" To Name Range ("ABD") And ("ABE")

thanx

haroon2015 · Jan 9th 2017

Re: Update data from sheet2 based on criteria

sample workbook

haroon2015 · Jan 9th 2017

hi every one,
i want to update my sheet1 from sheet2

i have data in sheet1.range(a2:i500)
every day i do back up of work in sheet2

i want to update my sheet1 from sheet2 after backup

when i want to update and run macro
i want to popup a InputBox now
i fill a value in inputbox = 31-DEC-2016
now macro search in sheet2 and column b4:b1000
and the macro found value 31-DEC-2016 in column c and rows c94:c111

then copy the range c94:c111 and paste in sheet1 in range a2, and macro should also add new rows according to copied range..(count rows = 18)

then add new 18 rows and paste from a2

range form sheet2
[ATTACH=CONFIG]71044[/ATTACH][ATTACH=CONFIG]71044[/ATTACH]
Befor update
and
after update

sample workbook added

haroon2015 · Jan 5th 2017

Re: Want to change in Worksheet_Change Event

Do something plz

haroon2015 · Jan 4th 2017

hi,
i am using below code to filter table according to cell value

Code

Private Sub Worksheet_Change(ByVal Target As Range)    If Intersect(Target, Range("B1, C1, D1, F1, I1")) Is Nothing Then Exit Sub
    Range("Table1[[#Headers],[CHQ NO]]").AutoFilter
    If Target.Column = 2 Then
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:=Target
    ElseIf Target.Column = 4 Then
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=4, Criteria1:=Target
    ElseIf Target.Column = 3 Then
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:=Target
    ElseIf Target.Column = 9 Then
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=9, Criteria1:=Target
    ElseIf Target.Column = 6 Then
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=6, Criteria1:="=*" & Target & "*"
    End If
End Sub

Display More

in above code i need some changing.....
1 - what's wrong in above code is when i input data in cell separately B1, C1, D1, F1, I1, above code filtered data, now i delete the inputvalue from range then table is not going to unfiltered....it it filtring the "" also, i don't want to filter ""

2 - i need little improvement in above code is i want to filter data at once when i input data in above cell range
for example - i want to filter "harooon", in cell "I1", and i type in cell I1 ..."H" then it filter the all data with the value of "H" When i cancel the typed h then unfilter the table..

thanx

haroon2015 · Jan 3rd 2017

Re: If duplicates found then don't run macro

In less rows its working perfectly

it will do

thanx a lot once again for hard work

haroon2015 · Jan 3rd 2017

Re: If duplicates found then don't run macro

35000 rows dear mod

haroon2015 · Jan 3rd 2017

Re: If duplicates found then don't run macro

thanx alot dear mod Trebor76

Its Working Properly but its taking too much time to find duplicate vaya msgbox

haroon2015 · Jan 2nd 2017

Hi,
I have numeric data in sheet4.range(A1:A)
I have also numeric data in sheet4.range(A1:A)

If with both range found duplicate data then don't run code and warn me with the help of msg box

If duplicate not found then run the code,

Thanx alot

haroon2015 · Dec 29th 2016

Re: Check if sheet hide then unhide them

dear
thanx for reply

but its not working

haroon2015 · Dec 27th 2016

hi,
i am using below code to run the macro

Code

Sub ExcelToTallyAll()


Application.Goto Sheet15.Cells(4, 3)
ExcelToTally_V2




Application.Goto Sheet22.Cells(4, 3)
ExcelToTally_LV




Application.Goto Sheet23.Cells(4, 3)
ExcelToTally_F2




Application.Goto Sheet24.Cells(4, 9)
ExcelToTally_L2
End Sub

Display More

in above code what i want to change are

check sheet15 is hide then unhide them
check sheet22 is hide then unhide them
check sheet23 is hide then unhide them
check sheet24 is hide then unhide them

like below

Code

Sub ExcelToTallyAll()Worksheets("Sheet15").Visible = True
Application.Goto Sheet15.Cells(4, 3)
ExcelToTally_V2
Worksheets("Sheet22").Visible = True
Application.Goto Sheet22.Cells(4, 3)
ExcelToTally_LV
Worksheets("Sheet23").Visible = True
Application.Goto Sheet23.Cells(4, 3)
ExcelToTally_F2
Worksheets("Sheet24").Visible = True
Application.Goto Sheet24.Cells(4, 9)
ExcelToTally_L2
End Sub

Display More

thanx in advance

haroon2015 · Dec 13th 2016

Re: Vba code is taking too much time to do job

no need to say sorry, i understand the situation, there is lot's of for help

Yes i am using your code post by RoyUK
.
thanx again for you hardwork and help

haroon2015 · Dec 13th 2016

Re: Vba code is taking too much time to do job

@ KjBox

this time code is working perfectly as it should be....

thanx a lot for all your help

i don't know why

haroon2015 · Dec 13th 2016

Re: Vba code is taking too much time to do job

OK
WILL UPLOAD THE WORKBOOK SOON

my original workbook is about 1200kb

i have to create a sample workbook for u

thanx for reply

haroon2015 · Dec 13th 2016

Re: Vba code is taking too much time to do job

Code

[COLOR=blue]Sub[/COLOR][COLOR=#333333] OLV_FORMULA_COPY() [/COLOR]    Sheets("OL V").Activate 
    Application.ScreenUpdating = [COLOR=blue]False[/COLOR] 
    [COLOR=blue]Dim[/COLOR] i, j, count 
    count = WorksheetFunction.CountIf(Sheets("OL V").Range("W4:W53"), ">0") - 1 
     
     
    i = 47 
    [COLOR=blue]For[/COLOR] j = 1 [COLOR=blue]To[/COLOR] count 
        Range("A4:R46").Copy 
        Range("A" & i).PasteSpecial Paste:=xlPasteAll 
        i = i + 43 
    [COLOR=blue]Next[/COLOR] j 
    Application.ScreenUpdating = [COLOR=blue]True[/COLOR]  [COLOR=blue]End Sub[/COLOR][COLOR=#333333] [/COLOR]

Display More

in above formula, first it count the range w4:w53 greater then 0
it found 3 values

then it copy a4:r46 and paste them 3 time

first time it copy range a4:r46 and paste them to a47
again it copy ragne a4:r46 and paste them to a90
again it copy range a4:r46 and paste them to a133

it takes too much time to copy paste

count 30
it take 26 seconds

if possible

it copy first time a4:r46 and paste them to A47
and
second time it copy from A47:r89 and paste them to a90

and continue
will this process slow down the copy paste value?

haroon2015 · Dec 12th 2016

Re: Vba code is taking too much time to do job

Any other way to slow down this copy paste method ?

haroon2015 · Dec 12th 2016

Re: Vba code is taking too much time to do job

Dear mod, KjBox And royUK

still facing same problem
?

haroon2015 · Dec 12th 2016

Re: Vba code is taking too much time to do job

it says,

Compile Error:
Label not defined