excel copy all from workbook 2, paste in workbook

Question

I am trying to copy all data from a workbook on my server and paste the values to B2 in another workbook.

This is what I have so far. It brings me to the workbook 2, but I have to manually select all and copy then paste in workbook 1.

Sub UpdateTSOM()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim StartCell As Range

    Set sht = Sheet5
    Set reportsheet = Sheet5
    Set StartCell = Range("B2")

    'Refresh UsedRange
    Worksheets("TSOM").UsedRange

    'Find Last Row
    LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    'Select Range
    sht.Range("B2:B" & LastRow).Select
    With Range("B2:B" & LastRow)

        If MsgBox("Clear all Transmission Stock data?", vbYesNo) = vbYes Then

            Worksheets("TSOM").Range("B2:N2000").ClearContents

            MsgBox ("Notes:" & vbNewLine & vbNewLine & _ 'This is not needed if I can automate the copy and paste.
            "Copy ALL" & vbNewLine & _
            "Paste as Values")
        End If
    End With
    Workbooks.Open "P:\ESO\1790-ORL\OUC\_Materials\Stock Status\Transmission Stock Status **-**-**.xlsx"
    ThisWorkbook.Activate

    reportsheet.Select
    Range("B2").Select

    whoa: 'If filename changes then open folder
    Call Shell("explorer.exe" & " " & "P:\ESO\1790-ORL\OUC\_Materials\Stock Status", vbNormalFocus)
    Range("B2").Select
    Application.ScreenUpdating = True
End Sub

Thanks

score 1 · Answer 1 · answered Mar 22 '17 at 19:38

A few guesses as you haven't provided all the details

Sub UpdateTSOM()

Application.ScreenUpdating = False

Dim LastRow As Long
Dim StartCell As Range
Dim sht As Worksheet
Dim wb As Workbook

Set sht = Sheet5
Set StartCell = sht.Range("B2")

'Find Last Row
LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

If MsgBox("Clear all Transmission Stock data?", vbYesNo) = vbYes Then
    Worksheets("TSOM").Range("B2:N2000").ClearContents
End If

Set wb = Workbooks.Open("P:\ESO\1790-ORL\OUC\_Materials\Stock Status\Transmission Stock Status **-**-**.xlsx")
wb.Sheets(1).UsedRange.Copy
StartCell.PasteSpecial xlValues

Application.ScreenUpdating = True

End Sub

score 1 · Answer 2 · edited May 23 '17 at 10:30

1

Avoid SendKeys, and since you are pasting values only, you don't need to use either Copy or Paste/PasteSpecial.

With wsCopyFrom.Range("A1:N3000")
    wsCopyTo.Range("B2").Resize(.Rows.Count, .Columns.Count).Value = .Value 
End With

Here are several other ways to copy values from one file to another:

Copy from one workbook and paste into another

edited May 23 '17 at 10:30

Community

1
1

answered Mar 27 '17 at 13:53

David Zemens

53,033
11
81
130

score 0 · Accepted Answer · answered Mar 27 '17 at 13:23

This is what I got to work. It brings up a select file folder and copies all the data from it into my current workbook. It then names B1 (my header) with the filename without the extension.

Sub UpdateTSOM()
Application.ScreenUpdating = False
Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet
Dim s As String

Set mycell = Worksheets("TSOM").Range("B1")
Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet

If MsgBox("Update transmission Stock Status data?", vbYesNo) = vbYes Then
Worksheets("TSOM").Range("B2:N3000").ClearContents
Else: Exit Sub
End If

'Locate file to copy data from
vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Excel File", "Open", False)

'Assign filename to Header
s = Mid(vFile, InStrRev(vFile, "\") + 1)
s = Left$(s, InStrRev(s, ".") - 1)
mycell.Value = s

Set wbCopyFrom = Workbooks.Open(vFile)
Set wsCopyFrom = wbCopyFrom.Worksheets(1)

'Copy Range
wsCopyFrom.Range("A1:N3000").Copy
wsCopyTo.Range("B2").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

SendKeys "Y"
SendKeys ("{ESC}")

'Close file that was opened
wbCopyFrom.Close SaveChanges:=False
Application.Wait (Now + 0.000005)
Call NoSelect
Exit Sub

End Sub

Also, instead of your `s = Mid(vFile, InstrRev(vFile, "\"), + 1)` just do `s = Dir(vFile)`. — David Zemens, Mar 27 '17 at 13:30
my `sendkeys` are used for dismissing a possible "Large copy" popup box. Anyway around it @DavidZemens ? — Matt Taylor, Mar 27 '17 at 13:45
Yes, disable `Application.DisplayAlerts` should force the *default* option and suppress that dialog. Or you can do a direct value assignment (without invoking either `Copy` or `Paste/PasteSpecial`) which will avoid any warning altogether. — David Zemens, Mar 27 '17 at 13:50

excel copy all from workbook 2, paste in workbook

3 Answers3