What's wrong with this part of my macro?

[Sunvisor]

Windows("MRCollect.xls").Activate
  Set ws = ActiveWorkbook.Sheets(1)
  lngLastRow1 = ws.Range("A65536").End(xlUp).Row + 1
  Range("A" & lngLastRow1).PasteSpecial xlPasteValues
  Application.CutCopyMode = False

AT this part of the code I already have MRCollect activated so I dont need that, and also I'd like for it to go to the second workbook which is called "TEMPLATE"....

I am getting the error around the lngLastRow1

Anyone see anything wrong?

 

[cstimart]

Windows("MRCollect.xls").Activate
  Set ws = ActiveWorkbook.Sheets(1)
  lngLastRow1 = ws.Range("A65536").End(xlUp).Row + 1
  Range("A" & lngLastRow1).PasteSpecial xlPasteValues
  Application.CutCopyMode = False

AT this part of the code I already have MRCollect activated so I dont need that, and also I'd like for it to go to the second workbook which is called "TEMPLATE"....

I am getting the error around the lngLastRow1

Anyone see anything wrong?

From an amateur point of view, it looks like lngLastRow1 is trying to set a value of 65537....are you in a version of Excel that allows that many rows?

 

[Sunvisor]

It was working before, so it can't be that

 

[RSXchin]

replace it with this

ws.cells(rows.count,1).End(xlUp).Row + 1

let me know how it goes

 

[Sunvisor]

Thanks, I dont think that part is the problem actually...
When I tried to change

        Set ws = ActiveWorkbook.Sheets(1)

To sheets(2), nothing works like its supposed to

Also I am trying to make something like

Windows("Meter Read Collection3.xls").Sheets("Sheet2").Activate

to work but its not! =[

 

[RSXchin]

oh duh... I can't believe I missed that.

instead of "Windows" use "Workbooks"

 

[Sunvisor]

Yes it exsists..

 

[Sunvisor]

Sub MRcollect_Click()
Dim path As String
Dim FileName As String
Dim Wkb As Workbook
Dim wsmf As Worksheet
Dim lngLastRow1 As Long
Dim wkb1 As Workbook
Dim rng As Range
    
Call ToggleEvents(False)
    '###################################
    path = "C:\Documents and Settings\M08040\My Documents\Test1"  'Change as needed '(Petrina's computer)
    '###################################
    FileName = Dir(path & "\*.xls", vbNormal)
    
    Do Until FileName = ""
        
        'this opens the workbook in the above specified folder
        Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName)
        
        'set this to the sheet number to look at
        Set wsmf = Wkb.Sheets(1)
        
        'moves to next spreadsheet if no "Job Description" is not found
        On Error GoTo NotFound
        LR = Range("B" & Rows.Count).End(xlUp).Row
        Range("B5:N" & LR).Copy
       'copies only if there is a serial number
        
        
        'pastes the information in the last row of your spreadsheet
        Workbooks("MRCollect.xls").Activate
        Set ws = ActiveWorkbook.Worksheets("Template Compiler")
        lngLastRow1 = ws.Range("A35536").End(xlUp).Row + 1
        Range("A" & lngLastRow1).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    
NotFound:
    FileName = Dir()
    
    Wkb.Close
    
    Loop
Call ToggleEvents(True)
End Sub
Sub ToggleEvents(blnState As Boolean)
    
    With Excel.Application
        .DisplayAlerts = blnState
        .EnableEvents = blnState
        .ScreenUpdating = blnState
        If blnState Then .CutCopyMode = False
        If blnState Then .StatusBar = False
    End With
    
End Sub

Here's the full code that just doesn't seem to work right

 

[RSXchin]

i edited my post. read it for the relavant info.

 

[Sunvisor]

It's currently just pasting only one spreadsheet at row 1008 or somethign random...