Compile Error Syntax Error

Bill Williamson

Board Regular
Joined
Oct 7, 2019
Messages
124
Have a macro that was working on older laptop. Had printed copy of said macro which I used to create a new macro on new computer.
Looked up and down this code but cant find any typos that would explain the error. I am currently running a newer version of excel, Could that be causing error?

I believe it may be how I am declaring variables, but it is the same as before so I am lost.
Any Help would be greatly appreciated.

I have ************ the rows that are highlighted red in my macro.


VBA Code:
Sub SytelineDataTransfer()
'   Keyboard Shortcut: Ctrl=+g
'   Imports sorts data from Syteline parts reports
'   Saves them into Quality tracking in excel
'
'
'


'   Verify worksheet name before running, if incorrect exit macro.

If Not ActiveSheet.Name Like "JobOperationsExport*" Then Exit Sub


'   Rename Worksheet

ActiveSheet.Name = "Export"


'   Turns off Screen Updating for faster operation

Application.ScreenUpdating = False


'   Seperates only required data, Sorts and Filters it, Puts everything into proper columns.

Columns("A:B").Select
Selection.Delete Shift:=xlToLeft

Columns("B:C").Select
Selection.Delete Shift:=xlToLeft

Columns("C:AA").Select
Selection.Delete Shift:=xlToLeft

Columns("D:S").Select
Selection.Delete Shift:=xlToLeft

Columns("E:V").Select
Selection.Delete Shift:=xlToLeft

ActiveWindow.SmallScroll ToRight:=-3
Cells.Select
Cells.EntireColumn.AutoFit

Range("E4").Select
Columns("B").Select
Selection.Cut
Columns("D").Select
Selection.Insert Shift:=xlToRight


'   Removes letter J from Job Numbers

Columns("A").Select
Selection.Replace "j", ""
Cells.Range("A1").Select
Selection.Replace "ob", "Job#"


'   Renames Item number to Part#

Columns("C").Select
Selection.Replace "item", "Part#"

'   Renames Received to Quantity

Columns("D").Select
Selection.Replace "Recieved", "Quantity"


'    Deletes all Carbon Steel Parts

Dim lr As Long, i As Long
lr = Range("C" & Rows.Count).End(xlUp).Row
        For i = lr To 1 Step -1
            If InStr(Range("C" & i), "C") > 0 Then
                Range("C" & i).EntireRow.Delete
            End If
    Next i
   
ActiveSheet.Columns("A:B").Insert Shift:=xlToRight
ActiveSheet.Name = "Export"


'   Message Box's for Customer and CSO#
'   Insert input Data into proper Columns


Dim CSO As String, Customer As String
CSO = InputBox("CSO#")
Customer = InputBox("Customer Name")
Range("A2").Value = CSO
Range("B2").Value = Customer
Range("A1:B1").Value = Array("CSO#", "Customer")
[A2].Resize(Range("C" & Rows.Count).End(xlUp)(0).Row, 2).Value = Array(CSO, Customer)
Columns("C").Select
Selection.Cut
Columns("B").Select
Selection.Insert Shift:=xlToRight
   

'   Centers, Resizes and Aligns Data to Correct Format

Cells.Select
With Selection
    .EntireColumn.AutoFit
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With


'   Turns Off Alert Display
'   Opens Quality Stainless document

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "C:\Test\Testworkbook.xlsx", FileFormat:=51

' Temporarily Changed to Test Program   *****************************************

   Workbooks.Open Filename:= _
        "C:\Users\Billw\Desktop\QA Documents\Stainless Data Entry 120519 copy for code work.xlsm"

'   Workbooks.Open Filename:= _
    '    "C:\Users\Billw\Desktop\QA Documents\Stainless Data Entry 120519.xlsm"
       
   
'   Finds last used row in both sheets. Copies and pastes data below existing data.

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim 1DestLastRow As Long                                                     ' ************************************************************************************************************  RED



'   Set Variables for Copy and Destination Sheets

Set wsCopy = Workbooks("Testworkbook.xlsx").Worksheets("Export")


'   Modified for code work


Set wsDest = Workbooks("Stainless Data Entry 120519 copy for code work.xlsm").Worksheets("Data")

'   Finds Last Used Row in the copy range based on data in Column A

1 CopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row



'   Finds first blank row in the destination range based on Data in Column C
'   Offset Property moves down 1 row

1DestLastRow = wsDest.Cells(wsDest.Rows.Count,"C").End(xlUp).Offest(1).Row               '************************************************************************************  RED



'   Copy and Paste Data

wsCopy.Range("A2:F" & 1CopyLastRow).Copy                                             ' *****************************************************************************RED
wsDest.Range("C" &  1DestLastRow).PasteSpecial                                         ' *****************************************************************************RED


'   Date Stamp and Column Numbering

Range("B" & 1DestLastRow & ":B" & 1DestLastRow = 1CopyLastRow -2).Value = Date                                                 ' *****************************************************************************RED
Range("A" & 1DestLastRow & ":A" & 1DestLastRow = 1CopyLastRow -2).Formula = "=r[-1]c+1"                                    ' *****************************************************************************RED

Workbooks("Testworkbook.xlsx").Close = False

Application.DisplayAlerts = True
Application.ScreenUpdating = True


MsgBox "Data Printed and Transferred"

'   Set Print Area

ActiveWindow.RangeSelection.PrintOut


' Future Modification Code, Currently Commented Out.

'   ActiveWorkbook.Save
'   Application.Quit
'   Workbooks.ThisWorkbook.Close SaveChanges: = True
'   Filename:= _
        "C:\Users\Billw\Desktop\QA Documents\Stainless Data Entry 120519.xlsm"



End Sub
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,947
Office Version
  1. 365
Platform
  1. Windows
You cannot have variables that start with a number so this Dim 1DestLastRow As Long should probably be a lower case L not the number 1.
You will also have to change the other lines giving errors as they also have a 1 rather than an l.
You also have Offest instead of Offset
 

Bill Williamson

Board Regular
Joined
Oct 7, 2019
Messages
124
Fluff

I have said it before, I will say it again, You my friend are a Genius. And apparently have bionic eyes, even while changing the 1 to a l they still looked the same to me. LOL
The # 1 and the lower l definitely the problem . Thank you so much for your help. aslo the other typo.....


Thanks,

Bill Williamson
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,912
Office Version
  1. 365
Platform
  1. Windows
Bill

You seem to have some typos in the code, for example you have 1 instead of l here.
VBA Code:
Dim 1DestLastRow As Long
Same here and Offset isn't spelt correctly.
VBA Code:
1DestLastRow = wsDest.Cells(wsDest.Rows.Count,"C").End(xlUp).Offest(1).Row
In fact the 1 instead of l seems to be the main problem throughout the code.
 

Bill Williamson

Board Regular
Joined
Oct 7, 2019
Messages
124

ADVERTISEMENT

Yes you were both correct. Unfortunately the font I have both look the same to me..... But all corrected and compile error is a done deal...... Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,947
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
I changed the default font in the editor, for just that reason.
 

Bill Williamson

Board Regular
Joined
Oct 7, 2019
Messages
124

ADVERTISEMENT

[QUOTE I changed the default font in the editor, for just that reason. [/QUOTE]


I need to figure out how to do that, What font do you recommend?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,947
Office Version
  1. 365
Platform
  1. Windows
In the editor goto Tools, options, Editor format, Normal text, then select a font.
I use Consolas, but there are plenty of options.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,947
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,865
Messages
5,542,946
Members
410,578
Latest member
schleeepy
Top