vba copy column from one workbook to another without opening

[catchsomnath]

Hi,

I want to copy column from workbook "SOURCE" column "D" values to workbook "DESTINATION" column "IN".

Regards,
Somnath

 

[catchsomnath]

Hi,

How do I now which line is causing problem? I tried f8 too but I only get pop up with the error

Regards,
somnath

 

[Martvg]

If you run the error from the workbook, and it gives an error, you can choose "debug".
Choosing debug will open up the VBE and highlight a certain line, this line is the line causing the error.

 

[catchsomnath]

Set wb = Workbooks.Open("C:\vba\template " & x & ".xls") 'This line must be filled in by yourself,

 

[Martvg]

I see, is there just one file called "template.xls" or are there multiple files called "template 1.xls", "template 2.xls"...... "template 40.xls" (with the space between template and 1/2).

 

[catchsomnath]

multiple files with template1, template2 etc

 

[Martvg]

In that case, can you change this line of code:
Set wb = Workbooks.Open("C:\<acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym>\template " & x & ".xls")
To:
Set wb = Workbooks.Open("C:\<acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym>\template" & x & ".xls")
So without the space between template and " & x

 

[catchsomnath]

Sorry... I am still getting the same error: Below the the code

Public Sub coppaste()
Application.ScreenUpdating = False

Dim x As Integer
Dim y As Integer

y = 40

For x = 1 To y

Dim wb As Workbook

Set wb = Workbooks.Open("C:\vba\template" & x & ".xls") 'This line must be filled in by yourself,
'First you add the file path and name except for the ".xls" or ".xlsx"
'then you leave "& x &" for the macro to realise which file it must open.
'then you add ".xls" or ".xlsx"
wb.Worksheets("IMPORT").Range("D:D").Copy Destination:=Workbooks("sde.xslm").Worksheets("INDEPTHStats").Columns(247).Offset(, x)

wb.Close True

Next x

Application.ScreenUpdating = True

End Sub

 

[Martvg]

I'm afraid I won't be able to help you any further in that case... as the code runs for me.

I think either the file path; "C:\vba" is wrong or the file type ".xls" is wrong and must be "xlsx".
I did notice that the workbook you are trying to copy to has the wrong file type, ".xslm" must be "xlsm" in that line of code.

 

[catchsomnath]

Hi,

I created new folder on D drive. also create dummy files Book1, src1 and src2 for test the code. Source file is opening but still getting same error and unable to copy the columns.

Anyway... thank you for your help

 

[Martvg]

One last question,
Where you running the macro from the "sde.xlsm" file?