Pass Row Number Of Last Used Column Row to Variable

markc · Aug 12th 2007

Hi all -

This line in my code is causing an invalid qualifer error message:

Code

lngNew = wsNew.Range("B65536").End(xlUp).Row.Offset(1, 0)

I am unsure why. It seems it should work

Full code below
Thanks
-marc

Code

Option Explicit


Sub Disney_DumpData()
    Dim wbBook As Workbook
    Dim wbNew As Workbook
    Dim ws As Worksheet
    Dim wsNew As Worksheet
    Dim rng As Range
    Dim rngNew As Range
    Dim rngUnit As Range
    Dim rngUnitPaste As Range
    Dim lngNew As Long
    Dim Cnt As Integer
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False


    Set wbBook = ThisWorkbook
    Cnt = 0
    
    'Dump To New File
    
        '/Define new workbook
            Set wbNew = Application.Workbooks.Add
            wbNew.SaveAs "C:\data\delete\Disney_budget_data.xls"
            wbNew.Sheets("Sheet1").Name = "Data"
            Set wsNew = wbNew.Worksheets("Data")
            ActiveWindow.Zoom = 75
            
        '/Copy SLC
            '/Data
                Set ws = wbBook.Worksheets("Grumpy")
                Set rng = ws.Range("A14:X164")
                rng.Copy
                If Cnt = 0 Then
                    lngNew = wsNew.Range("B65536").End(xlUp).Row
                Else
                    lngNew = wsNew.Range("B65536").End(xlUp).Row.Offset(1, 0)
                End If
                Set rngNew = wsNew.Range("B" & lngNew)
                rngNew.PasteSpecial (xlPasteValuesAndNumberFormats)
            '/Unit Number
                Set rngUnit = ws.Range("B3")
                rngUnit.Copy
                Set rngUnitPaste = wsNew.Range("A" & lngNew + 1, "A" & lngNew + 152)
                rngUnitPaste
            '/Cleanup - Setup
                Set ws = Nothing
                Set rng = Nothing
                Set rngUnit = Nothing
                Set rngUnitPaste = Nothing
                Set rngNew = Nothing
                Cnt = 1


    '/Cleanup
    
        Set wbBook = Nothing
        Set wbNew = Nothing
        Set ws = Nothing
        Set wsNew = Nothing
        
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        Application.DisplayAlerts = True

Display More

Dave Hawley · Aug 12th 2007

Re: Offset Causes Invalid Qualifier

Try,

Code

lngNew = wsNew. Range("B65536").End(xlUp)(2,1).Row

jindon · Aug 12th 2007

Re: Pass Row Number Of Last Used Column Row to Variable

Change to

Code

lngNew = wsNew.Range("B65536").End(xlUp).Row+1

or

Code

Dim lngNew As Range
Set lngNew = ws.Range("B65536").End(xlUp).Offset(1)

markc · Aug 12th 2007

Re: Pass Row Number Of Last Used Column Row to Variable

Thanks guys -

Shear brilliance on my part trying to use offset on a variable dim'd as long:exclamat:
The revised code below accomplishes exactly what I need

Thanks much!
-marc

Code

Option Explicit


Sub Disney_DumpData()
    Dim wbBook As Workbook
    Dim wbNew As Workbook
    Dim ws As Worksheet
    Dim wsNew As Worksheet
    Dim rng As Range
    Dim rngNew As Range
    Dim rngUnit As Range
    Dim rngUnitPaste As Range
    Dim lngNew As Long
    Dim Cnt As Integer
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False


    Set wbBook = ThisWorkbook
    Cnt = 0
    
    'Dump To New File
    
        '/Define new workbook
            Set wbNew = Application.Workbooks.Add
            wbNew.SaveAs "C:\data\delete\Disney_budget_data.xls"
            wbNew.Sheets("Sheet1").Name = "Data"
            Set wsNew = wbNew.Worksheets("Data")
            ActiveWindow.Zoom = 75
            
        '/Copy Grumpy
            '/Data
                Set ws = wbBook.Worksheets("Grumpy")
                Set rng = ws.Range("A14:X164")
                rng.Copy
                If Cnt = 0 Then
                    lngNew = wsNew.Range("B65536").End(xlUp).Row
                Else
                    lngNew = wsNew.Range("B65536").End(xlUp).Row + 1
                End If
                Set rngNew = wsNew.Range("B" & lngNew)
                rngNew.PasteSpecial (xlPasteValuesAndNumberFormats)
            '/Unit Number
                Set rngUnit = ws.Range("B3")
                rngUnit.Copy
                If Cnt = 0 Then
                    Set rngUnitPaste = wsNew.Range("A" & lngNew, "A" & lngNew + 150)
                Else
                    Set rngUnitPaste = wsNew.Range("A" & lngNew + 1, "A" & lngNew + 151)
                End If
                rngUnitPaste.PasteSpecial (xlPasteValuesAndNumberFormats)
            '/Cleanup - Setup
                Set ws = Nothing
                Set rng = Nothing
                Set rngUnit = Nothing
                Set rngUnitPaste = Nothing
                Set rngNew = Nothing
                Cnt = 1


    '/Cleanup
    
        Set wbBook = Nothing
        Set wbNew = Nothing
        Set ws = Nothing
        Set wsNew = Nothing
        
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        Application.DisplayAlerts = True
        
        
            
End Sub

Display More

**norie** · Aug 12th 2007

Re: Pass Row Number Of Last Used Column Row to Variable

marc

Glad to see you solved the problem.:)

But I have to ask why you have this.

Code

Cnt = 0

And this.

Code

Cnt=0

You don't seem to be doing any iterating and Cnt isn't declared publicly.:confused:

markc · Aug 12th 2007

Re: Pass Row Number Of Last Used Column Row to Variable

Hi Norie -
Wow! Long time haven't heard from you :smile:

The code is the start for 7 other sheets in the workbook
Once I get it working properly it's just a matter of copy/paste the code and update the sheet names for all 8 worksheets to be copied out to a new workbook.

Thanks
-marc

**norie** · Aug 12th 2007

Re: Pass Row Number Of Last Used Column Row to Variable

marc

Why would you want to do that?

Wouldn't that just be repeating the same code, perhaps with some minor changes?

markc · Aug 12th 2007

Re: Pass Row Number Of Last Used Column Row to Variable

Hi Norie -

Edited by marc -
Weird. How did same message post? See above.
The sample is to copy data from a single sheet in a workbook to a single sheet in a new workbook
Once I got the procedure working I will copy it down and simply update the sheet name for the new snippet
Ex. Change Grumpy to Doc.
It is not the most efficient way to program
But since I don't have total control of the workbook, I think it might be the best solution

Thanks
-marc
=======

Wow! Long time haven't heard from you :smile:

The code is the start for 7 other sheets in the workbook
Once I get it working properly it's just a matter of copy/paste the code and update the sheet names for all 8 worksheets to be copied out to a new workbook.

Thanks
-marc

Pass Row Number Of Last Used Column Row to Variable

Participate now!

Share

Users Viewing This Thread