Pasting in a new workbook Problem

[youngda]

Hello, so far this program works except for one major problem. I will explain the point as quickly as possible. Multiple users will submit this workbook each day (about 15). The person who recieves them will hit a "upload schwab button" where the data input by the users is pasted into another workbook (called OutputBook). Notice that the data that needs to be copied is determined in teh first few lines, (this works fine). Next, thanks to you guys, the sheet determines if OutputBook has already been created for the day. If it has not, Outputbook is created and the data is pasted into the first few lines. Everything up to this point works.

If OutputBook already exists, the file is opened, and I want the data to paste below the previsouly pasted data. (the program is successful in deciding where to paste it) The only problem here is that the program has now forgot the pasted range. I've isolated the problem to when the OutputBook is opened. (See the --><---) Before this line is executed, the pasted data is still int eh computers memory. Afterward, it is gone. I've tried recopying the data from the original worksheet, but that seems to generate its own set of errors. Anyone know why?



Public Function UploadSchwab()

Dim PreviousOutput As Range
Dim Location As Integer
Dim OutputBook As String


'This section copies each trade recorded on the Schwab output sheet. This is done by copying a range of cells _
A2 : to cells(OutputTotal, Column 21). Outputtotal is a variable created on the Advisor sheet and reflects the _
total number of trades input, plus 1 row to account for the master account header. The Advisor view sheet is _
reactivated so the user is unaware of what has happened.

Worksheets("Schwab Output").Activate
Worksheets("Schwab Output").Range(Cells(2, 1), Cells(Worksheets("Advisor View").Range("Outputtotal").Value + 1, 21)).Select
Selection.Copy
Worksheets("Advisor View").Activate

' This section first determines the name of today's output workbook. The code then asks, does this file exist(Outputbook = "")? _
If not, then it adds a workbook, pastes the output data in the beginning rows, adds some extraneous data in cell a1 _
and b1, and saves the new workbook as the name of the Output Workbook

OutputBook = "C:\Documents and Settings\All Users\Desktop\" & Format(Date, "yymmdd") & " Schwab Trades.csv"
If Dir(OutputBook) = "" Then
Workbooks.Add
Set wbOut = ActiveWorkbook
Cells(2, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("a1").Value = 8007706
Range("b1").Value = 2
wbOut.SaveAs OutputBook

'This section exists in case the OutputBook has already been created, and instead the user is adding export data in addition _
to the data already present in the book. It determines what the previousOutput was by counting how many lines are blank _
in the first 100 rows. It minuses that number by 101, so if fifty rows of previous data existed, the cell where new Data should be pasted _
is row 51 (101-50 blank rows, column 1 "Cells(Location, 1)" The data is then pasted and the workbook is simply saved.


Else


-------------> Workbooks.Open (OutputBook)<-----------------------------
Set PreviousOutput = Range("a2:a100")
Location = 101 - Application.WorksheetFunction.CountBlank(PreviousOutput)
Cells(Location, 1).Select
Selection.PasteSepcial Paste:=x1PasteValues
ActiveWorkbook.Save
End If
End Function

 

[VoG]

I don't know if this is the problem but


Selection.PasteSepcial Paste:=x1PasteValues

is a typo; should be

Selection.PasteSpecial Paste:=xlPasteValues

Edit: double typo actually but follow Norie's advice.

 

[Norie]

Why not just change the order of things?

ie do the copy just before the paste, after the workbook has been created/opened.

Note there's no need to select anything either.

Public Sub UploadSchwab()
Dim wbOut As Workbook
Dim PreviousOutput As Range
Dim Location As Integer
Dim OutputBook As String

    OutputBook = "C:\Documents and Settings\All Users\Desktop\" & Format(Date, "yymmdd") & " Schwab Trades.csv"
    If Dir(OutputBook) = "" Then
        Workbooks.Add
        Set wbOut = ActiveWorkbook
        Worksheets("Schwab Output").Range(Cells(2, 1), Cells(Worksheets("Advisor View").Range("Outputtotal").Value + 1, 21)).Copy
        With wbOut.ActiveSheet
            .Cells(2, 1).PasteSpecial Paste:=xlPasteValues
            .Range("a1").Value = 8007706
            .Range("b1").Value = 2
        End With
        wbOut.SaveAs OutputBook
    Else
        Set PreviousOutput = Range("a2:a100")
        Location = 101 - Application.WorksheetFunction.CountBlank(PreviousOutput)
        Worksheets("Schwab Output").Range(Cells(2, 1), Cells(Worksheets("Advisor View").Range("Outputtotal").Value + 1, 21)).Copy
        ActiveSheet.Cells(Location, 1).PasteSepcial Paste:=xlPasteValues
        ActiveWorkbook.Save
    End If
End Sub

 

[youngda]

I did try that briefly, the reason it didnt work is when copying the data, the program no longer knows which workboook to copy the data from.

(The line that begins "Worksheets("Schwab Output).range etc etc is the location of the error)

And because so many of these wbooks will created each day, they are given automatic, but relatively random numbers for names by another computer program. So I need a way to let the program know that "hey, remember that you want to copy data from tradesheet 3428684, worksheet("Schwab Output").range etc etc., even though you've left that workbook awhile back.

I think the solution would be to define a variable as the activeworkbook early on in the program, and then refer back to it when trying to copy. Here's my general idea, again, the syntax is beyond me. BTW, thank you so much for your help, this program is much needed and I was nominated to make it despite my lack of experience.

(OpenedTradingsheet = activeworkbook) at the functions beginning

and this line when Im trying to copy:
Workbooks("OpenedTradingSheet").worksheets("schwab output").range(etc etc).copy

 

[Norie]

Yes that's probably the way to go.

If you don't specify explicitly workbooks/worksheets then code like this will probably refer to the active sheet in the active workbook.

Cells(2, 1).Select

Now you might have selected/activated that sheet in the code but that doesn't necessarily mean VBA considers it the active sheet.

 

[youngda]

I still cant get the syntax right. I've already written Set wbin = activeworkbook, but the following line doesn't work. Grrr...

Workbooks(wbin).Worksheets("Schwab Output").Range(Cells(2, 1), Cells(Worksheets("Advisor View").Range("Outputtotal").Value + 1, 21)).Copy

 

[Norie]

You don't need to use Workbooks - you've already created a reference to the workbook object using the set statement.

wbin.Worksheets("Schwab Output").Range(Cells(2, 1), Cells(Worksheets("Advisor View").Range("Outputtotal").Value + 1, 21)).Copy

Note I'm still not sure that will work since you still have unqualified references. eg Cells(2,1)

 

[youngda]

sorry, didn't see your first reply.

Im trying to copy a range that will vary everytime the function is run. It will always begin in Cells 2,1, which just refers to the fact that I want to only copy a range starting at cell a2 in the Schwab Output Sheet. THe end of the range is determined by the variable "OutputTotal"+1, which basically means if tehre are 10 lines to be copied, then I start at cell a2, and I copy to cell (11,21) [11 comes from OutputTotal(10) +1), 21 is the last column I need]

I will always be copying this range(which has a size that varys) from workbook the original function was launched from. I changed the line as you suggested, it still doesn't work, saying "appilcation defined or object defined error", which I assume means it doesn't know what range my code is referring too.