Excel copy not working

Closed
Report
Posts
1
Registration date
Wednesday March 31, 2010
Status
Member
Last seen
March 31, 2010
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hey, I got a code from this site and made changes to copy from a different excel file and paste to another file.

I'm copying from a file Source.xls to Target.xls where the macro is. In the target file, there are three sheets: czk,eur & usd.

Sub copycolumns()


Dim LDate As String
Dim LColumn As Integer
Dim LFound As Boolean
Dim objWorkbook1 As Workbook

On Error GoTo Err_Execute

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook1 = objExcel.Workbooks.Open("C:\xxx\Source.xls")


'Retrieve date value to search for
LDate = objWorkbook1.Sheets("Sosi").Range("D4").Value

Sheets("CZK").Select

'Start at column B
LColumn = 4
LFound = False

While LFound = False

'Encountered blank cell in row 2, terminate search
If Len(Cells(1, LColumn)) = 0 Then
MsgBox "No matching date was found."
Exit Sub

'Found match in row 2
ElseIf Cells(1, LColumn) = LDate Then
'.......................................................
'Select values to copy from "Rolling Plan" sheet
objWorkbook1.Sheets("Sosi").Select
objWorkbook1.Sheets("Sosi").Range("D6:D29").Select
Selection.Copy

'Paste onto "Plan" sheet
Sheets("CZK").Select
Cells(2, LColumn).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'......................................................
objWorkbook1.Sheets("Sosi").Select
objWorkbook1.Sheets("Sosi").Range("E6:E29").Select
Selection.Copy

'Paste onto "Plan" sheet
Sheets("EUR").Select
Cells(2, LColumn).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'......................................................
objWorkbook1.Sheets("Sosi").Select
objWorkbook1.Sheets("Sosi").Range("F6:F29").Select
Selection.Copy

'Paste onto "Plan" sheet
Sheets("USD").Select
Cells(2, LColumn).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'......................................................

LFound = True
MsgBox "The data has been successfully copied."

'Continue searching
Else
LColumn = LColumn + 1
End If

Wend

On Error GoTo 0

Exit Sub


Err_Execute:
MsgBox "An error occurred."



End Sub

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
and the error is ?