Chirag R Raval
Member
Dear Sir,
My code is here ..that created for extract data from database & make separate file but just required buyer nos only (not generate for all buyers in database) ... coded for my requirement by this forums by Mr. Picosta...(I will always be thankful him for that help)
this macro moved from saved file to personnel.xlsb..for make it globally usable that work any active sheet..
but its stuck..
Macro stuck on
ThisWorkbook.Sheets("sheet1").range("A1:W" & lrow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
ThisWorkbook.Sheets("sheet1").range(Cells(1, "X"), Cells(Rows.count, "X").End(xlUp)), Unique:=False
may be saved file's modules, code transfer to other module can prevent to run?
is this macro only run for only for which its resided in file?
another point of future error can be raise.....
can we prevent from raise error after add new file to paste the data?
please also resolve "Thisworkbook" & "Active workbook" concept in this code.
how this code decide from which workbook to which workbook paste data after add new file?
I already convert thisworkbook to active workbook but failes..
is there need to establish Parent Child relation ?
can this macro need variables that hold
(1) "from workbook" and another variable that hold (2) "To workbook"
to copy data ?
I can not understand..
please resolve.
Regards,
Chirag
My code is here ..that created for extract data from database & make separate file but just required buyer nos only (not generate for all buyers in database) ... coded for my requirement by this forums by Mr. Picosta...(I will always be thankful him for that help)
this macro moved from saved file to personnel.xlsb..for make it globally usable that work any active sheet..
but its stuck..
Code:
Sub SavetoNewFile()
Dim lrow, i As Integer
Dim criteria As New Collection
Dim c As range
lrow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.count, 1).End(xlUp).Row
ThisWorkbook.Sheets("sheet1").range("A1:W" & lrow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
ThisWorkbook.Sheets("sheet1").range(Cells(1, "X"), Cells(Rows.count, "X").End(xlUp)), Unique:=False
For Each c In ThisWorkbook.Sheets("Sheet1").range("A2:A" & lrow)
If c.EntireRow.Hidden = False And IsInCollection(c, criteria) = False Then
criteria.Add c
Debug.Print c
End If
Next c
For i = 1 To criteria.count
ThisWorkbook.Sheets("sheet1").range("A1:W" & lrow).AutoFilter Field:=1, Criteria1:=criteria(i)
Workbooks.Add
ThisWorkbook.Sheets("sheet1").range("A1:W" & lrow).Copy ActiveWorkbook.Sheets(1).Cells(1, 1)
Next i
End Sub
Private Function IsInCollection(valToBeFound As Variant, coll As Variant) As Boolean
Dim element As Variant
On Error GoTo IsInCollectionError: 'Collection is empty
For Each element In coll
If element = valToBeFound Then
IsInCollection = True
Exit Function
End If
Next element
Exit Function
IsInCollectionError:
On Error GoTo 0
IsInCollection = False
End Function
Macro stuck on
ThisWorkbook.Sheets("sheet1").range("A1:W" & lrow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
ThisWorkbook.Sheets("sheet1").range(Cells(1, "X"), Cells(Rows.count, "X").End(xlUp)), Unique:=False
may be saved file's modules, code transfer to other module can prevent to run?
is this macro only run for only for which its resided in file?
another point of future error can be raise.....
can we prevent from raise error after add new file to paste the data?
please also resolve "Thisworkbook" & "Active workbook" concept in this code.
how this code decide from which workbook to which workbook paste data after add new file?
I already convert thisworkbook to active workbook but failes..
is there need to establish Parent Child relation ?
can this macro need variables that hold
(1) "from workbook" and another variable that hold (2) "To workbook"
to copy data ?
I can not understand..
please resolve.
Regards,
Chirag