Opening file, copy and paste range

[Feethurt]

Hello everyone,
I'm sure this is a very basic question, but I'm new at this. I'm trying to prompt the user to select an Excel workbook, then copy and paste a range of cells from that workbook into the active one. This is my attempt below:

Sub CommandButton1_Click()


Dim WS As Worksheet


Dim wb As Workbook, wb2 As Workbook
Dim vFile As Variant

'Set source workbook
Set wb = ActiveWorkbook

'Open the target workbook
vFile = Application.GetOpenFilename("Excel-files,*.xls*", _
    1, "Select One File To Open", , False)

'if the user didn't select a file, exit sub
If TypeName(vFile) = "Boolean" Then Exit Sub
Workbooks.Open vFile

'Set selectedworkbook
Set wb2 = ActiveWorkbook

'Select cells to copy
wb2.Worksheets("Output").Range("A10:C15").Select
Selection.Copy

'Go back to original workbook you want to paste into
wb.Activate

'Paste starting at the last empty row
wb.Worksheets("Master").Range("A10:C15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True

'Close and save the workbook you copied from
wb2.Save
wb2.Close

End Sub

The dialog box successfully opens, and the selected file successfully opens, and then I get a run-time error 91 on the following line:

y.Sheets("Autoreport1.xlsm").Range("A10:C14").Value = x.Sheets("strFileToOpen").Range("A10:C14")

Again, this is probably something really stupid but all the same I need help. Thanks!

[JLGWhiz]

I know my eyesight is not what it used to be, but I could swear that the line you are having a problem with is not in the procedure you posted. BTW, this forum requires that you use code tags when you post macros or parts of macros. You can edit your original post, select the code portion of the post and click the pound (#) symbol to apply the code tags. It will keep the moderator from scolding you. Is there a different code that you meant to post that contains the problem line?

[dangelor]

This line

y.Sheets("Autoreport1.xlsm").Range("A10:C14").Value = x.Sheets("strFileToOpen").Range("A10:C14")

isn't in the code you posted.

[alansidman]

Code Tags Added
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

[Feethurt]

Sorry about all of that! I was in a hurry last night. I did in fact post an error line from the wrong program. I have read the rules and will be sure to follow them from now on.

Good news is, I've gotten closer to my goal. I made a new program, and I'm having a new problem, but it's very much a related problem so I think I'll keep it in this same thread.

The code below is meant to copy data ranges from two different user-selected spreadsheets (wbThis an wbThat) and paste them into two different tabs of wbTarget. It is mostly working, it is prompting the user to select both workbooks, but when it's done, wbTarget has data from wbThat on both tabs, instead of wbThis on one tab and wbThat on the other. WbThis seems to be getting overwritten. Does anyone know why that might be?

Option Explicit

Sub cy_pst()
'Copy range of cells to Master Spreadsheet
    Dim lr As Long
    Dim lrC As Long
    Dim wbTarget As Workbook    'Current Open Workbook
    Dim wbThis As Workbook      'Source Data CW
    Dim wbThat As Workbook      'Source Data CCW
    Dim strFileToOpen As String 'Path for Source Data Spreadsheet

    Application.ScreenUpdating = False

    'set the current active workbook
    Set wbTarget = ThisWorkbook
    'set the target workbook name
   

strFileToOpen = Application.GetOpenFilename _
(Title:="Please choose the clockwise data file", _
FileFilter:="Excel Files *.xls* (*.xls*),")


If strFileToOpen = "False" Then
    MsgBox "No file selected.", vbExclamation, "Sorry!"
    Exit Sub
Else
    Set wbThis = Workbooks.Open(Filename:=strFileToOpen)
End If

   
   
    'clear any thing on the clipboard to mazimize available memory
    Application.CutCopyMode = False
    'Copy Data in range A10:BG5233:
    wbThis.Sheets("H28_F116 Data").Range("A10:BG5233").Copy
    'paste the data to the Comments Worksheet
    wbTarget.Sheets("H28_F116 CW Data").Range("A10:BG5233").PasteSpecial
    Application.ScreenUpdating = True
  
  
    strFileToOpen = Application.GetOpenFilename _
(Title:="Please choose the counterclockwise data file", _
FileFilter:="Excel Files *.xls* (*.xls*),")


If strFileToOpen = "False" Then
    MsgBox "No file selected.", vbExclamation, "Sorry!"
    Exit Sub
Else
    Set wbThat = Workbooks.Open(Filename:=strFileToOpen)
End If

 'clear any thing on the clipboard to mazimize available memory
    Application.CutCopyMode = False
'Copy Data in range A10:BG5233:
    wbThat.Sheets("H28_F116 Data").Range("A10:BG5233").Copy
    'paste the data to the Comments Worksheet
    wbTarget.Sheets("H28_F116 CCW Data").Range("A10:BG5233").PasteSpecial
    'Clear the clipboard
    Application.CutCopyMode = False
    wbTarget.Save
    Application.ScreenUpdating = True

    'clear memory
    Set wbTarget = Nothing
    Set wbThis = Nothing
    Set wbThat = Nothing
    MsgBox "Data Transferred"
End Sub

[JLGWhiz]

wbTarget.Sheets("H28_F116 CW Data").Range("A10:BG5233").PasteSpecial

The above statement is the reason it is overwriting. The code is telling it to paste data from This and That to the same range. If you want to avoid that, assuming that column A will be as lengthy with data as any of the other columns then for the That workbook data you can use:

wbTarget.Sheets("H28_F116 CCW Data").Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues

Which should put the data From workbook That under the previously pasted data.

[Feethurt]

Thanks for the tip, but that doesn't seem to be the problem. "H28_F116 CW Data" and "H28_F116 CCW Data" are two separate sheets within the same workbook. I can't see any reason that anything should be getting overwritten.


I want to put two datasets from two different user-selected files on two different sheets within the same "target" workbook: In cells A10:BG5233 of sheet "H28_F116 CW Data", and A10:BG5233 of sheet "H28_F116 CCW Data".

Thanks for all the help so far. Does anyone see what the problem might be?

[dangelor]

Didn't see a problem with your code except the pastespecial statement, so I modified it a bit. See if it works...

Sub cy_pst()
    Dim wbTarget As Workbook, wbSource As Workbook
    Dim strFileToOpen As String, vData As Variant
    
    Set wbTarget = ThisWorkbook
    strFileToOpen = Application.GetOpenFilename("Excel Files *.xls* (*.xls*),", , "Please choose the clockwise data file")
    If strFileToOpen = "False" Then
        MsgBox "No file selected.", vbExclamation, "Sorry!"
        Exit Sub
    Else
        Set wbSource = Workbooks.Open(strFileToOpen, , 1)
    End If
    
    vData = wbSource.Sheets("H28_F116 Data").Range("A10:BG5233")
    wbTarget.Sheets("H28_F116 CW Data").Range("A10:BG5233") = vData
    wbSource.Close
    
    strFileToOpen = Application.GetOpenFilename("Excel Files *.xls* (*.xls*),", , "Please choose the counterclockwise data file")
    If strFileToOpen = "False" Then
        MsgBox "No file selected.", vbExclamation, "Sorry!"
        Exit Sub
    Else
        Set wbSource = Workbooks.Open(strFileToOpen, , 1)
    End If
    
    vData = wbSource.Sheets("H28_F116 Data").Range("A10:BG5233")
    wbTarget.Sheets("H28_F116 CCW Data").Range("A10:BG5233") = vData
    wbSource.Close
    
    wbTarget.Save
    MsgBox "Data Transferred"
End Sub

[Feethurt]

Dangelor, thank you for the suggestion. What I ended up doing was programming wbThis and wbThat to close once I was done with them, and that fixed the problem. I may try your suggestion though.

I'm now having a new problem: I'm trying to paste data to the first empty row in a spreadsheet. Despite lots of resources addressing this common and seemingly simple task, I can't come up with code that works. I'm going to start a new thread as that problem doesn't really match this title.