copy and pasting data from one workbook into another

Question

I am trying to copy a select range of cells from one workbook into another. This is my select range and copy code so far used in the first workbook:

Sub Copy()

'This selects the range of active cells, adds a border and copies all data.
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
End Sub

While this selects and copies the cells in the 1st workbook, I am having difficulties incorporating it with a macro that pastes it into another workbook. Here is a sample of a macro that pastes data from one workbook into another:

Sub Paste()
Dim x As Workbook
Dim y As Workbook

'## Open both workbooks first:
Set x = Workbooks.Open(" path to copying book ")
Set y = Workbooks.Open(" path to destination book ")

'Now, transfer values from x to y:
y.Sheets("sheetname").Range("A1").Value = x.Sheets("name of copying sheet").Range("A1") 

'Close x:
x.Close

End Sub

Two things:

The 1st workbook with the initial data is not a saved document on my computer. It's an exported sheet from the internet I am trying to paste into a saved workbook on my computer. Therefore, I don't think a file path or worksheet name for the first workbook is possible to get.
I am hoping to paste the data in the first available blank cell in column A of the second workbook. I believe the code for that is something like: CurrentRow = Range("A1").End(xlDown).Offset(1, 0).Row and then obviously paste into that row starting in the A column.

Can someone help me incorporate these two codes into one?

Thank you very much!

Sfadal Sfadal · Accepted Answer · 2016-03-23T02:29:19

Here are two snippets of code I have used in recent times which should help you in your predicament.

This first code allows you to find a specific worksheet by name (or index as stated by Office Documentation). This does not need you to specify the workbook as it loops through all currently open worksheets.

Dim Sheet As Worksheet
Dim sheetName As String

sheetName = "Sheet1"

For Each Sheet In Worksheets
    If Sheet.Name = sheetName Then
        Set GetSheet = Worksheets(sheetName)
        Exit For
    End If
Next Sheet

The next Code snippet (I can not take credit for, but have lost info of origin) which will search a specified worksheet for any and all data contained with-in and create a range object with the cell range found.

(This may not be what you want as I am unsure from your question as to if you want all the data or just a selection).

    Dim dataRange as Range
    Dim lastCol As Long
    Dim lastRow As Long
    Dim sheetCells As range

    Set sheetCells = Sheet.Cells


    If WorksheetFunction.CountA(sheetCells) > 0 Then
        'Search for any entry, by searching backwards by Rows.
        lastRow = sheetCells.Find(What:="*", after:=[A1], _
                   SearchOrder:=xlByRows, _
                   SearchDirection:=xlPrevious).Row
               '    Search for any entry, by searching backwards by Columns.
        lastCol = sheetCells.Find(What:="*", after:=[A1], _
                   SearchOrder:=xlByColumns, _
                   SearchDirection:=xlPrevious).Column

            ' Set the range from 1st cell of sheet to last cell found to contain data
            Set dataRange = Sheet.range(Sheet.Cells(1, 1), Sheet.Cells(lastRow, lastCol))


    End If

Once you have a range object, there is a lot you can do with it but to simply insert values into another worksheet:

Dim newSheet as WorkSheet

set newSheet = ThisWorkbook.Worksheets("New Sheet") ' Just an example

'Using fields from last code snippet
newSheet.Range(newSheet.Cells(1,1), newSheet.Cells(lastRow,lastCol)).value = dataRange.Value

copy and pasting data from one workbook into another

1 Answers