Is it possible to assign range to global variable

AndyJM87

New Member
Joined
Aug 31, 2018
Messages
10
Hi all,

I have 3 worksheets, one of these three is a master template. (sheet 3 for eg)

I need to collect data from the other two sheets (sheet 1 and sheet 2) and send to the relevant cells on the master template.

To do this I have assigned a set of global variables, hoping that I could set these variables to the range on either sheet 1 / sheet 2 and then using the variable name put the stored data onto sheet 3.

The worksheets ws… are all in the global module.

modGlobals

Code:
Public strSafety As String
Public strRefDtl As String
Public strDate As Date

Public rngTemp As Range
Public rngDTL As Range
Public dtlCell As Range


Public lngNtemp As Long
Public lngRTemp As Long
Public lngNdtl As Long
Public lngRdtl As Long
Public lngCell As Long
modUpdateTemplate

Code:
Option Explicit
Sub UpdateDRACAS()


    lngNdtl = 2
    lngRdtl = wsData.Range("B" & lngNdtl).End(xlDown).Row
    
    Set rngDTL = wsData.Range("B" & lngNdtl, "B" & lngRdtl)
    
    For Each dtlCell In rngDTL
        lngCell = dtlCell.Row
        
        With wsData
            strSafety = .Range("A" & lngCell).Value
            strRefDTL = .Range("B" & lngCell).Value
            strDte = .Range("C" & lngCell).Value
        End With
            
        With wsTemp
        
            lngNtemp = 1
            lngRTemp = wsTemp.Range("C" & Rows.Count).End(xlUp).Row + 1
            
            Call AssignConsts
            
            .Range("A" & lngRTemp) = strDte
            .Range("B" & lngRTemp) = strBy
            .Range("C" & lngRTemp) = strRefDTL
        End With
        
    Next
End Sub
Error: object variable or with block not set

Code with Error: lngRTemp = wsTemp.Range("C" & Rows.Count).End(xlUp).Row + 1
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,425
Office Version
365
Platform
Windows
Have you assigned a value to wsTemp?
 

Forum statistics

Threads
1,078,215
Messages
5,338,905
Members
399,267
Latest member
Danielle1017

Some videos you may like

This Week's Hot Topics

Top