copy paste data from a different workbook to current worksheet

Question

I am basically stuck. I have a code which allows me to browse a file, once the file is selected it copies all the data in that file and then allows me to select a worksheet, from any workbook that is open at that time. Once the worksheet is selected [this is where i get stuck] i want it to paste it into j7. instead it doesn't do that, baring in mind i will be changing the file name everyday as it has the current days date on it. here is my code:

Sub Macro4()
'
' Macro4 Macro
'

'
Range("A1").Select
Dim fileStr As String

fileStr = Application.GetOpenFilename()

If fileStr = "False" Then Exit Sub

Workbooks.Open fileStr

Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Window.Sheets(Array("Forecast_workings")).Select{**this is where i want to be able to select a worksheet from any open workbook and it will paste the data in cell J7 of that worksheet.**
Range("J7").Select
Application.CutCopyMode = False
Range("C16:C27").Select
Selection.Copy
Range("E16").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("G16:G27").Select
Selection.Copy
Range("C16").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("O16").Select
End Sub

score 2 · Accepted Answer · edited May 23 '17 at 12:21

I can see lot of errors in your code.

First things first. You avoid the use of .Select. INTERESTING READ

If I understand you correctly then to get the name of the sheet which user selects at runtime, you can use Application.InputBox with Type:=8. This will return a range and from that you can use .Parent.Name to get the name of the worksheet.

Is this what you are trying?

Your code can be written as (UNTESTED)

Sub Macro4()
    Dim fileStr As String
    Dim wb As Workbook, thiswb As Workbook
    Dim ws  As Worksheet, thisws As Worksheet
    Dim Lcol As Long, LRow As Long
    Dim Ret As Range

    '~~> Set an object for thisworkbook and worksheet
    Set thiswb = ThisWorkbook
    '~~> Change this to the sheet from where you want to copy
    Set thisws = thiswb.Sheets("Sheet1")

    '~~> Let user choose a file
    fileStr = Application.GetOpenFilename()

    If fileStr = "False" Then Exit Sub

    '~~> Set an object for workbook opened and it's worksheet
    Set wb = Workbooks.Open(fileStr)

    On Error Resume Next
    Set Ret = Application.InputBox("Select a cell from the sheet you want to choose", Type:=8)
    On Error GoTo 0

    If Ret Is Nothing Then Exit Sub

    Set ws = wb.Sheets(Ret.Parent.Name)

    With thisws
        '~~> Find Last column in row 2
        Lcol = .Cells(2, .Columns.Count).End(xlToLeft).Column
        '~~> Find last cell in Col 1
        LRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Copy your range directly to new worksheet selected
        .Range(.Cells(2, 1), .Cells(LRow, Lcol)).Copy ws.Range("J7")
        .Range("C16:C27").Copy ws.Range("E16")
        .Range("G16:G27").Copy ws.Range("C16")
        Application.CutCopyMode = False
    End With
End Sub

@Siva: This is the 2nd time you have downvoted me without leaving any explanation. You are on a revenge path. I have no option but to report you to a moderator. I have taken the snapshot of your profile. — Siddharth Rout, Nov 29 '13 at 20:04
@Shiva: [Siva](http://stackoverflow.com/users/325521/shiva) I see you removed the downvote and your score has gone up by 1. It only shows that it was you. Stop doing it or i will inform the moderator. LAST WARNING. — Siddharth Rout, Nov 29 '13 at 20:11

score 0 · Answer 2 · answered Nov 29 '13 at 22:51

when working with multiple workbooks, dont use range() but wb.range(), where wb is defined with the set function. Also activesheet can be tricky. Preferably name the sheet you are using sheets("whatever"). And for last, to copy things dont use activate/select, just do as this:

wb.sheets("whatever").range() thisworkbook.sheets("watever2").range("").

I also saw you dont use application.enableevents=false/true, so events will trigger like crazy and your activesheet (or cell) will change like crazy if you have code in worksheet_change section.

copy paste data from a different workbook to current worksheet

2 Answers2