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

 

[Martvg]

assuming both workbooks are open and that the sheet you want to copy from and to is called "sheet1".

Sub coppas()


    Workbooks("SOURCE").Worksheets("Sheet1").Range("D:D").Copy Destination:=Workbooks("DESTINATION").Worksheets("sheet1").Range("IN:IN")
    
End Sub

 

[gallen]

I don't think it can be done without opening. However you can open and close the workbook in vba

 

[catchsomnath]

I want to open only destination workbook and I am coping columns from 40 workbooks.

DESTINATION workbook
copy from SOURCE 1 - Sheet IMPORT - D column to DESTINATION - Sheet INDEPTHStats - Column IN
copy from SOURCE 2 - Sheet IMPORT - D column to DESTINATION - Sheet INDEPTHStats - Column IO
copy from SOURCE 3 - Sheet IMPORT - D column to DESTINATION - Sheet INDEPTHStats - Column IP
..
..
..
copy from SOURCE 40 - Sheet IMPORT - D column to DESTINATION - Sheet INDEPTHStats - Column XX

 

[Martvg]

This does open every workbook, but should be quite fast, you only need to change one line as specified within 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:\Users\someone\Documents\OneNote Notebooks\SOURCE " & x & ".xlsx")   '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("DESTINATION").Worksheets("INDEPTHStats").Columns(247).Offset(, x)
    
    wb.Close True
    
    Next x
    
    Application.ScreenUpdating = True
     
End Sub

Best regards

 

[catchsomnath]

Thank you Martvg,

I made changes as per your suggestion but I am getting Run time error '1004': Application-defined or object-object defined error

Here is 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("sheet1").Range("D:D").Copy Destination:=Workbooks("sde.xlsm").Worksheets("INDEPTHStats").Columns(247).Offset(, x)

wb.Close True

Next x

Application.ScreenUpdating = True

End Sub

 

[Martvg]

Hi,

Where do you get the error?
Is the file an .xls file?
Is the file named "template"?

I think you need to change: "("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 "("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\SOURCE " & x & ".xls")" or to "("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\SOURCE " & x & ".xlsx")"

Best regards,
Mart

 

[footoo]

If you are able to set the maximum number of rows in the source files, here's a possibility that does not open the source files.
I have assumed the maximum number of rows is 1000.
The macro is completely untested (so probably needs tweaking) :

Dim pth$, n%, c%
pth = "D:\MyFiles\[SOURCE " 'Change to actual file path
n = 1
For c = 248 To 287
    With Cells(1, c).Resize(1000)
        .FormulaR1C1 = "=if(" & pth & n & ".xlsx]IMPORT!RC4="""",""""," & pth & n & ".xlsx]IMPORT!RC4"
        .Value = .Value
    End With
    n = n + 1
Next

 

[catchsomnath]

Hi Martvg

file name is template.xls. The file is in folder vba

Regards,
Somnath

 

[Martvg]

Hi,

in that case you filled the code in all right, on which line does the error occur?

Best regards,
Mart