CODEWITHSUNDEEP
excelvba
Greg
Greg

Reputation: 11

copying selected data from one workbook to another with readdressing cells

Guys I need some help with those funky vba commands. I have two spreadsheets, first labownik-mil-2dl8.xls and second zestawienie.xls and I want to select some rows in first one to copy to second one but not A1 to A1. So if my selection is rows from 5270 to 5273 I want to get for example E5272 to D7 and AK5272 to E7 and so on. Would be nice if it could be done by button press in second spreadsheet (firstly just making selection in first spreadsheet). Makro should paste into first empty row in second spreadsheet. I have something like this:

Sub Get_Data()
    Dim lastrowDB As Long, lastrow As Long
    Dim arr1, arr2, i As Integer

    With Sheets("zestawienie")
        lastrowDB = .Cells(.Rows.Count, "D").End(xlUp).Row + 1
    End With

    arr1 = Array("E", "AK", "B", "D", "F", "G", "H")
    arr2 = Array("D", "E", "F", "H", "L", "M", "N")

    For i = LBound(arr1) To UBound(arr1)
        With Sheets("Labownik")
            lastrow = Application.Max(3, .Cells(.Rows.Count, arr1(i)).End(xlUp).Row)
            .Range(.Cells(3, arr1(i)), .Cells(lastrow, arr1(i))).Copy
            Sheets("zestawienie").Range(arr2(i) & lastrowDB).PasteSpecial xlPasteValues
        End With
    Next
    Application.CutCopyMode = False
End Sub

but it is only working when both sheets are in one file, and makro is copying the whole data not from selection. I have no idea how to do it properly.
I am sorry if it is hard to read but English is not my native language.

Thank you in advance

Upvotes: 1

Views: 50

Answers (2)

Slai
Slai

Reputation: 22876

One way can be with Application.Intersect :

Dim rngFrom As Range, rngTo As Range, index As Long

Set rngTo = Workbooks("zestawienie.xls").Sheets("Arkusz1").Range("D7,E7,F7,H7,L7,M7,N7") ' the columns "D", "E", "F", "H", "L", "M", "N" on row 7

Set rngFrom = Workbooks("labownik-mil-2dl8.xls").Sheets("Labownik - 2SLU").Range("E:E,AK:AK,B:B,D:D,F:F,G:G,H:H") ' the columns "E", "AK", "B", "D", "F", "G", "H"

Set rngFrom = Application.Intersect( rngFrom, rngFrom.Worksheet.Range("5270:5273") ) ' the columns "E", "AK", "B", "D", "F", "G", "H" intersected with rows from 5270 to 5273 gives the ranges E5270:E5273,AK5270:AK5273,B5270:B5273,D5270:D5273,F5270:F5273,G5270:G5273,H5270:H5273

For index = 1 To rngTo.Areas.Count 

    rngFrom.Areas(index).Copy
    rngTo.Areas(index).PasteSpecial xlPasteValues

Next

Upvotes: 0

Kenzington
Kenzington

Reputation: 11

You have to add a workbook object so that it would know how to work with both workbooks. Your code is treating them as worksheets in the same workbook. 

Dim wbSecond as Workbook

Set wbSecond = Workbook.Open(parameters)

That will allow you to now reference one workbook at wbSecond and you can always reference the workbook you're in as MyWorkbook.

Then you can use the syntax:

MyWorkbook.Sheets(sheet_name).cells(row,col) = wbSecond.Sheets(sheet_name).cells(row, col)

Upvotes: 1

Related Questions