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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Glad we could help & thanks for the feedback.
I changed the default font in the editor, for just that reason.
 
Upvote 0
[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?
 
Upvote 0
In the editor goto Tools, options, Editor format, Normal text, then select a font.
I use Consolas, but there are plenty of options.
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,217
Members
453,024
Latest member
Wingit77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top