Complile error: variable not defined (1 Viewer)

[UCG]

Hi,

could you please help me to look into the error:

Sub CopyCols()
Application.ScreenUpdating = False
Dim srcWB As Workbook
Set srcWB = ThisWorkbook
Dim desWS As Worksheet
Set desWS = Workbooks("CombinedData.xlsx").Sheets("Sheet1")
desWS.UsedRange.ClearContents
Dim beginDate As String
Dim endDate As String
Dim lastRow As Long
Dim bottomA As Long
Dim ws As Worksheet
desWS.Range("A1:H1") = Array("Course Type", "Invoice No.", "Lecturer", "Description", "Net Amount", "Invoice Date", "Exchange", "Classification")
For Each ws In srcWB.Sheets(Array("Purchase USD", "Purchase EUR"))
If ws.Name = "Purchase USD" Then
bottomA = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
Intersect(ws.Rows("2:" & bottomA), ws.Range("A:A,D:E,G:G,K:L,N:N,S:S")).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
ElseIf ws.Name = "Purchase EUR" Then
bottomA = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
Intersect(ws.Rows("2:" & bottomA), ws.Range("A:A,D:F,J:K,M:M,S:S")).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
Next ws
response = InputBox("Please enter the search criteria for the course type material.")
debug at the sub

Thanks.

 

[June7]

Please post code between CODE tags to retain indentation and readability.

Which line throws the error?

I am guessing it is the one with response variable.

or maybe something to do with 'debug at the sub', which I have never seen and have no idea what this is for.

There is no End Sub line.

 

[UCG]

Please post code between CODE tags to retain indentation and readability.

Which line throws the error?

The line starts with response

 

[June7]

Yes, the code does not have a Dim statement to declare the variable.

 

[UCG]

Not quite sure what to dim here.. could you advice?

 

[jdraw]

UCG,

Please tell users what this code is about in simple plain English.
And specific info as to how this relates to Access.

People here are willing to help, but you have an obligation to:
-overview the application/database/activity in simple terms(no jargon)
-identify the issue, clearly
-identify what you have tried and what results were obtained

Do you have Option Explicit at the top of your module? You should because that will identify undefined variables.

Good luck.

 

[UCG]

I was trying to extract data from another workbook and have them copied to specific column.
I would like to have the message box to prompt the filter as in which period I would like to extract and for which type of invoice I would like to extract

 

[June7]

Suppose you could declare it as a Variant. If variable type is not explicitly indicated, variable will default to Variant

Dim response

or maybe declare it as a string

Dim response As String