Followin. Code works in ThisWorkbook for copying range into another worksheet of .xlsm but if changed to another workbook then No Range Displays

[NimishK]

Hello
The below code works perfectly to copy range in particular named worksheet with ThisWorkbook with Coding with reference of thisworkbook and Copying range in .Xlsm File
But when Workbook is changed with File extension .Xlsx i get No Data at all.

Did i miss on defining the range for another workbook.worksheet. If yes then please help me to correct the syntax to define the range

For Next Loop where Destination Sheet Range needs to be displayed unfortunately no data Displayed

Option Explicit
Public wrkBkMainSource As Excel.Workbook
Public wksMainSource As Excel.Worksheet, wksDestination As Excel.Worksheet
Public rngCellSource As Range
Public RangeSource As Range, lastRow As Long

Private Sub UserForm_Initialize()
Set wrkBkMainSource = Workbooks.Open("C:\ABC\Cars.xlsx")
Set wksMainSource = wrkBkMainSource.Sheets("MasterDetails")

End Sub

Private Sub CommandButton1_Click()
 Call CopyRowsToParticularSheet("Japanese Cars", "Toyota")
End Sub


Public Sub CopyRowsToParticularSheet(ParticularSheet As String, rngSearchText As String)

Dim rngCellSource As Range, RangeSource As Range
Dim lngDestinRow As Long
Dim j As Integer, i As Long

With wksMainSource
 Set RangeSource = .Range(.Cells(2, 3), .Cells(.Rows.Count, 3).End(xlUp))
     lastRow = .Cells(Rows.Count, 3).End(xlUp).Row
End With


If Not sheet_exists(ParticularSheet) Then
       wrkBkMainSource.Sheets.Add( _
                     after:=wrkBkMainSource.Sheets(wrkBkMainSource.Sheets.Count)).Name = _
                     ParticularSheet

Set wksDestination = wrkBkMainSource.Worksheets(ParticularSheet)
With wksDestination
         .Activate
         .Range("A1:J1").Font.Bold = True
         .Range("E:E").NumberFormat = "@"
         .Range("A1:J" & lastRow).Rows.AutoFit
         .Range("A1:J" & lastRow).VerticalAlignment = xlCenter
         .Range("A1:J" & lastRow).HorizontalAlignment = xlLeft

lngDestinRow = 1
     For Each rngCellSource In RangeSource
            If rngCellSource.Value = rngSearchText Then
                lngDestinRow = lngDestinRow + 1
                .Cells(lngDestinRow, "A").EntireRow = rngCellSource.EntireRow.Value
            End If
      Next
End With
End If

End Sub


Public Function sheet_exists(strFileName As String) As Boolean
    On Error GoTo eHandle
    Set wksMainSource = Workbooks("C:\ABC\Cars.xlsx").Worksheets(strFileName)
    sheet_exists = True
    Exit Function
eHandle:
    sheet_exists = False
End Function

Thanks
NimishK

 

[NimishK]

Resolved
Missed to Assign the range object

Option 1
I changed the Following
from
For Each rngCellSource In RangeSource
To
For Each rngCellSource In wksMainSource.Range(wksMainSource.Cells(2, 3), wksMainSource.Cells(wksMainSource.Rows.Count, 3).End(xlUp))


Option 2
Set RangeSource = wksMainSource.Range(wksMainSource.Cells(2, 3), wksMainSource.Cells(wksMainSource.Rows.Count, 3).End(xlUp))
then use below
For Each rngCellSource In RangeSource

NimishK