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

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
660
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

VBA Code:
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
660
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
 
Solution

Forum statistics

Threads
1,181,673
Messages
5,931,352
Members
436,787
Latest member
ogharipour

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top