Help with Error Expected Sub, Function or Property

[Sameer.k21]

Hi All,

I am trying unmerge, delete filter and copy the data to another workbook. however I get this error. Please advice.

Sub rawdata()
   
Dim lngStart As Long, lngEnd As Long
Dim lrow As Integer

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
   
    Sheet2.Activate
    Columns("A:F").Select
    Selection.UnMerge
    Range("C:C,F:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.NumberFormat = "[$-10409]m/d/yyyy"
 
  Sheet1.Activate
  lngStart = Range("C7").Value 'assume this is the start date
  lngEnd = Range("E7").Value 'assume this is the end date
  Sheet2.Activate
  Range("E:E").AutoFilter Field:=1, Criteria1:=">=" & lngStart, Operator:=xlAnd, Criteria2:="<=" & lngEnd
 
lrow = 2
  With Sheet2.Cells(2, 1).CurrentRegion
  .Offset(1).Resize(.Rows.Count).Copy
  Workbooks("Data File.xls").Activate
  ThisWorkbook.Sheets(1).Cells(lrow, 2).PasteSpecial xlPasteValues
  Application.CutCopyMode = False: lrow .Rows.Count - 1
  End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Luke M]

You didn't say which line was causing the problem, but I'm guessing it's this one near the end:

  Application.CutCopyMode = False: lrow .Rows.Count - 1

Looks like you're missing a "=" between lRow and the .Rows.Count

  Application.CutCopyMode = False: lrow = .Rows.Count - 1

On a side note, I'm not sure why you are changing value of lRow right before the end of the sub anyway...

 

[Sameer.k21]

Thanks Luke. I updated that and removed the value of lrow.

 

[Sameer.k21]

@Luke I made some changes and now I get another error message

Option Explicit
Private Sub rawdata()
   
Dim lngStart As Long, lngEnd As Long
Dim lrow As Integer, ws As Workbook, ws1 As Workbook

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
   
Set ws = Workbooks("Book1.xlsm")
Set ws1 = Workbooks("Book2.xlsb")
   
    Sheet2.Activate
    Columns("A:F").Select
    Selection.UnMerge
    Range("C:C,F:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.NumberFormat = "[$-10409]m/d/yyyy"
 
  Sheet1.Activate
  lngStart = Range("C7").Value 'assume this is the start date
  lngEnd = Range("E7").Value 'assume this is the end date
  Sheet2.Activate
  Range("E:E").AutoFilter Field:=1, Criteria1:=">=" & lngStart, Operator:=xlAnd, Criteria2:="<=" & lngEnd
 
'lrow = 2
  With Sheet2.Cells(1, 1).CurrentRegion
  .Offset(1).Resize(.Rows.Count).Copy
  ws1.Activate
  ThisWorkbook.Sheets(1).Cells(lrow, 1).PasteSpecial xlPasteValues
  Application.CutCopyMode = False: lrow = lrow + .Rows.Count - 1
  End With
ws1.Save
Application.ScreenUpdating = True
Application.DisplayAlerts = True

ws.Activate
ws.Close 0

End Sub

 

[Sameer.k21]

Its working. Thank you for helping me

 

[Luke M]

Awesome, thanks for the follow-up.