Subscript out of range error, not sure why

[siggisigg]

Im trying to make a code that will let me select another excel document, i want to copy a cell, and paste it into my original excel file. This keeps giving various errors, and i cant seem to find the best way to do this. Would appreciate any help. (the code isnt done either, i just dont want to make the rest before i know that this part works.)

This is my code so far

Sub test()
Dim wb As Workbook
Dim wb2 As Workbook
Dim vFile As Variant
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual

Set wb = ActiveWorkbook

vFile = Application.GetOpenFilename("Excel-files,*.xlsx", _
1, "Select One File To Open", , False)

If TypeName(vFile) = "Boolean" Then Exit Sub
Workbooks.Open vFile
'Set targetworkbook
Set wb2 = ActiveWorkbook
wb2.Worksheets("Sheet1").Range("C12").Copy
Set wb = ActiveWorkbook

wb.Worksheets("Data").Range("B1").Select ' This step keeps yielding the error
Selection.PasteSpecial Paste:=xlPasteValues


End Sub

[ragulduy]

It looks to me as though you are trying to copy the C12 on Sheet1 in the vFile workbook to B1 on the Data sheet in vFile. Given that you are using getopenfilename, presumably the file that is open sometimes doesn't have a sheet named Data?

Maybe try:

Please Login or Register  to view this content.

[siggisigg]

is the
Set wb = activeworkbook
what makes it not work? i tried replacing that step with
wb.Sheets("Data").Activate
but now that step is the one not working

[siggisigg]

i do have a sheet called data where i want to paste my data. i tried your code and it gives me "automated error on this step:
wb.Sheets("Data").Range("B1").Value = wb2.Sheets(1).Range("C12").Value

[siggisigg]

Maybe i didnt explain it proper in the start. I have one workbook, where i want to be able to import data to, by clicking a button, allowing me to select a file, and then the needed data will be picked out. On this starting workbook, i have 2 sheets, the overview page where the data will be presented in graphs later, and the data page where i want the data to import to.

The file that is opened does not have a sheet named data, it has "sheet1" and "sheet2", data is on the workbook that im originally on, and where i want to paste the data.

[ragulduy]

In your original code the second set wb = activeworkbook will overwrite the first so both will be copying to the same workbook.

I just double checked my code and tested it, providing there is a sheet called Data in the active workbook when you run the code then it seems to run ok.

It might be worth trying:

Please Login or Register  to view this content.

which will copy the data into the workbook the code is saved in, rather than the active one.

[siggisigg]

that worked! thank you so much. Im sure ill be back with a new thread as soon as the next problem arises :P

[siggisigg]

Alright so now i need to make things worse. when i put data into the data sheet, i need it not necessarily to put it in b1, i need to have it in row 1, first available column. suggestions?