Excel VBA: copy excel columns from one file to other file (in different folder) of same name

Question

I have same type of files generated monthly. Datafiles are having same name but they are on different folders. What I want is to copy a specific column (computed result) of previous month's datafile to new month's datafile. I have tried . But couldn't get it. I am getting this error. "VBA Object doesn't support this property or method"

My code is

Private Sub CommandButton1_Click()

 Dim CSVfolder, CSVfolder1, CSVfolder2 As String
 Dim XlsFolder, XlsFolder1, XlsFolder2 As String
 Dim fname, fname1, fname2 As String
 Dim wBook As Workbook
 Dim vArr, vArr1, vArr2
 Dim vFile, vFile1, vFile2
 vArr = Array("Bangalore")
 CSVfolder = "C:\Charts\0\"
 CSVfolder1 = "C:\Charts\1\"
 CSVfolder2 = "C:\Charts\2\"
 XlsFolder = "C:\Charts\0\"
 XlsFolder1 = "C:\Charts\1\"
 XlsFolder2 = "C:\Charts\2\"
vArr1 = Array("Bangalore")
vArr2 = Array("Bangalore")
Dim fileName, Pathname As String
Dim WB, WB1, WB2 As Workbook

Pathname = "c:\Charts\0\"
Dim fileName1, Pathname1 As String
Pathname1 = "c:\Charts\1\"
For Each vFile1 In vArr1
fileName1 = Dir(Pathname1 & vFile1 & "\" & "*.xlsx")
Do While fileName1 <> ""
Set WB1 = Workbooks.Open(Pathname1 & vFile1 & "\" & fileName1)
    WB1.Application.ScreenUpdating = False
    WB1.ActiveSheet.Columns("M").Copy
    ActiveSheet.Close SaveChanges:=False

    Workbooks.Open (Pathname & vFile & "\" & fileName1)
    ActiveSheet.Columns("C").Select
    ActiveSheet.Paste
    ActiveSheet.Close SaveChanges:=True
Loop
Next

Dim fileName2, Pathname2 As String
Pathname2 = "c:\Charts\2\"
For Each vFile2 In vArr2
fileName2 = Dir(Pathname1 & vFile2 & "\" & "*.xlsx")
Do While fileName2 <> ""
Set WB2 = Workbooks.Open(Pathname2 & vFile2 & "\" & fileName2)
    WB2.Application.ScreenUpdating = False
    WB2.ActiveSheet.Columns("M").Copy
    WB2.ActiveSheet.Close SaveChanges:=False

    Workbooks.Open (Pathname & vFile & "\" & fileName2)
    ActiveSheet.Columns("D").Select
    ActiveSheet.Paste
    ActiveSheet.Close SaveChanges:=True
Loop
Next

End Sub

I want to open a file . Copy a column. close it. open another file with same name. paste it. .... Thats all... But error occurs. Pls help me. Thanks in advance.

Try to change the code to `ActiveWorkbook.Close (True)` . Worksheet does not support close method. — keong kenshih, Jun 22 '15 at 07:40

score 1 · Answer 1 · answered Jun 22 '15 at 07:43

1

ActiveSheet.Close SaveChanges:=True

You can't close a sheet. You can only close a workbook. Change that the workbook that you want closed.

You forgot to set the line below to a workbook object.

Workbooks.Open (Pathname & vFile & "\" & fileName1)

answered Jun 22 '15 at 07:43

Amen Jlili

1,884
4
28
51

I think this would help http://stackoverflow.com/questions/14396998/how-to-clear-memory-to-prevent-out-of-memory-error-in-excel-vba You need to terminate useless objects that cramming up your memory – Amen Jlili Jun 22 '15 at 07:57
it worked only for one copy- paste and the loop didn't work – Abdul Shiyas Jun 22 '15 at 08:36
Instead of copying entire columns, why don't copy and paste ranges. Determine the last cell of data in your sheet relevant to your range and then copy paste. A single column can be 3 million cells which is a lot of memory. – Amen Jlili Jun 22 '15 at 08:50

score 1 · Answer 2 · answered Jun 22 '15 at 14:38

Be careful of declaring several variables on a single line Dim CSVfolder, CSVfolder1, CSVfolder2 As String because here you've only declared the last one as String, the others are all Variant. If you want them on one line, declare the type each time Dim CSVfolder As String, CSVfolder1 As String, CSVfolder2 As String.

When you open a workbook always Set a workbook variable to reference it.

Don't repeat blocks of code but instead put the code into another procedure or function and then call that.

When using the Dir function to loop through files, you need to remember to call Dir again but with no parameters inside the loop:

filename = Dir("some_path")

Do While filename <> ""
    ' do something here
    ...
    filename = Dir ' this finds the next filename
Loop

In your existing code you make use of the vFile variable but nowhere does this get set. You set the vArr variable but nowhere do you use it.

Here's an example of how your code could look.

Private Sub CommandButton1_Click()

Dim CSVfolder As String, CSVfolder1 As String, CSVfolder2 As String
Dim XlsFolder As String, XlsFolder1 As String, XlsFolder2 As String
Dim vArr As Variant, vArr1 As Variant, vArr2 As Variant

Dim Pathname As String
Dim Pathname2 As String
Dim Pathname1 As String

    vArr = Array("Bangalore")   ' never gets used
    CSVfolder = "C:\Charts\0\"
    CSVfolder1 = "C:\Charts\1\"
    CSVfolder2 = "C:\Charts\2\"
    XlsFolder = "C:\Charts\0\"
    XlsFolder1 = "C:\Charts\1\"
    XlsFolder2 = "C:\Charts\2\"
    vArr1 = Array("Bangalore")
    vArr2 = Array("Bangalore")

    Pathname2 = "c:\Charts\2\"

    Pathname = "c:\Charts\0\"
    Pathname1 = "c:\Charts\1\"

    Application.ScreenUpdating = False

    CopyTheColumn vArr1, Pathname1, Pathname, "M", "C"

    CopyTheColumn vArr2, Pathname2, Pathname, "M", "D"

    Application.ScreenUpdating = True

End Sub

Private Sub CopyTheColumn(ByRef fileNamesArray As Variant, ByRef sourcePath As String, ByRef destPath As String, ByRef sourceColumnLetter As String, ByRef destColumnLetter As String)

' Copies the sourceColumnLetter column from all files found in sourcePath
' and pastes into destColumnLetter in file with same name in destPath

Dim vFile As Variant
Dim sourceFileName As String, destFileName As String
Dim sourceBook As Workbook, destBook As Workbook

    For Each vFile In fileNamesArray
        sourceFileName = Dir(sourcePath & vFile & "\" & "*.xlsx")
        Do While sourceFileName <> ""

            Set sourceBook = Workbooks.Open(sourcePath & vFile & "\" & sourceFileName)
            sourceBook.ActiveSheet.Columns(sourceColumnLetter).Copy
            sourceBook.Close SaveChanges:=False

            Set destBook = Workbooks.Open(destPath & vFile & "\" & sourceFileName)
            destBook.ActiveSheet.Columns(destColumnLetter).Paste
            destBook.Close SaveChanges:=True

            sourceFileName = Dir
        Loop
    Next

End Sub

score 0 · Answer 3 · answered Jun 23 '15 at 09:07

Using Copy|Paste is like asking for trouble. Instead I use Range.Copy ...Destination as described here: https://msdn.microsoft.com/en-us/library/office/ff837760.aspx

With Copy|Paste there is a threat that in the middle in another Windows program (say Word) you might use Copy|Paste. This way you are going to paste into Word the contents of your copied Columns. And in the same time you are going to paste into Excel content of Word. This is because all windows programs share the same clipboard.

With Range.Copy|Destination it is more difficult because you have to have both files open at the same time, but Excel does not allow to have two files open with the same name. The solution is to use temp file and in this pseudo code:

Sub CopyDestination(SourceFile, Path1, Path2, MyRange)
   Set Temp as Workbook
   Set File1 = open Path1 & SourceFile
   File1.Sheet.MyRange.Copy Destination:= Temp.Sheet.MyRange
   File1.Close False
   Set File2 = open Path2 & SourceFile
   Temp.Sheet.MyRange.Copy Destination:= File2.Sheet.MyRange
   File2.Close True
   Temp.Clear
End Sub

This code is longer and uses additional resources but is more reliable and safe.

Cheers

Excel VBA: copy excel columns from one file to other file (in different folder) of same name

3 Answers3