Copying columns across workbooks

[gsuperti]

Hello,

I have several workbooks with multiple worksheets and each worksheet has the collumns A to O filled with data. All worksheets are named as month/year and all columns of all worksheets has the same column format. The input of informations start in row 7 but the total amount of rows may be different.
I need to extract just some columns of the last worksheet of each workbook and paste it into another worksheet, different of the worksheets that contains the information. The informations of all worksheets of each workbook must be pasted into the same worksheet, starting at row 2. As explained below:

- workbook 1 worksheet 1 has 25 rows;
- workbook 2 worksheet 1 has 10 rows;
- workbook 3 worksheet 1 has 15 rows;

The workbook 1 worksheet 1 information will be pasted at row2; the workbook 2 worksheet 1 information will be pasted at row 27 (first empty row after information of workbook 1 was pasted). The information of workbook3 will be pasted at row37 (first empty row after information of workbook 1 and workbook2 were pasted).
Some columns of original worksheets has formulas but i need to paste just the values not the formulas.
Does any one knows how to do it?

Thank you very much

[k64]

Yes.

Set wb = thisWB
With workbooks.open("path/file.xlsx")
.Sheets(1).usedrange.copy wb.sheets(1).Range("a50000").end(xlup).offset(1)
.close false
End With

You can use a loop to do multiple if they have predictable file paths and names.

[gsuperti]

Hi k64,

where can I specify the collumns that will be copied and pasted? remember that I need informations of only some columns of each workbook.

Thank you

[k64]

This copies the 2nd column on the 1st sheet of the other workbook to the next open cell in column A. You can change the numbers as needed.

Set wb = thisWB
With workbooks.open("path/file.xlsx")
.Sheets(1).columns(2).copy wb.sheets(1).Range("a50000").end(xlup).offset(1)
.close false
End With

[gsuperti]

It didnīt work. I think is because the first 6 rows has different formats of the destination worksheet because they containg common informations of all worksheets. The exclusive informations starts at row 7.
The attachment shows an image of the worksheet that I need to extract information.

[k64]

Try

Set wb = thisWB
With workbooks.open("path/file.xlsx")
.Sheets(1).Range("B7:B" & Range("B50000").end(xlup).row).copy wb.sheets(1).Range("a50000").end(xlup).offset(1)
.close false
End With

[gsuperti]

Still having the same problem at the line:
Set wb = thisWB

I think I am missing something.
My original worksheets has 14 columns and I need to extract just 11, but this extracted columns must be pasted at different columns of the original one.

[k64]

Oh, my bad. It should be Set wb = ThisWorkbook

[gsuperti]

No problem,

Two things happens

1 - It pastes the entire worksheet specified at the "path" instead of just few columns
2 - After run the macro the information pasted disappears almost instantly. If I change the code .close false to .close true the same thing happens, but after some seconds.

[k64]

Hey gsuperti,
We're 9 posts in and you haven't given me a sample workbook. If you want me to troubleshoot the code with you, I'm happy to do that, but I can't do it blind.

[gsuperti]

Hi K64, my bad.

Here it goes the example. The informations that will be pasted starts at row 7. I need to extract information of columns A, C, E, G-L, N, O.
This is one of the several worksheets that I need to copy/paste informations but they are all with the same fformat.

Thanks...

[k64]

Here, you should be able to adapt this to what you need. I'm adding everything to sheet 1. If you want it added to other sheets, you can change that in the code.

Sub importdata()
Set wb = ThisWorkbook
columnlist = Array("A", "C", "E", "G", "H", "I", "J", "K", "L", "N", "O")
folder = "C:\folder\"
filelist = Array("file1", "file2", "file3")
For x = LBound(filelist) To UBound(filelist)
    With Workbooks.Open(folder & filelist(x) & ".xlsx")
        For y = 1 To .Sheets.Count
            With Sheets(y)
                For i = LBound(columnlist) To UBound(columnlist)
                    .Sheets(y).Range(Cells(7, i), Cells(50000, i).End(xlUp)).Copy wb.Sheets(1).Range(i & "50000").End(xlUp).Offset(1)
                Next i
            End With
        Next y
        .Close False
    End With
Next x
End Sub