VBA to copy data, close then Open another sheet and paste

[daniels012]

I cannot get the order of how to do this.
I know this is incorrect, but is this close?

Range("B5:B18").Copy
ActiveWorkbook.Close
Application.Workbooks.Open DBPathEstim
ActiveSheet.PasteSpecial Format:text
Range("B5")

I wonder if I should have a destination workbook and a current workbook?
And some how Open the workbook: DBPathEstim as my Destination workbook.

Then copy the current data then close the current workbook.

I don't know?
Let me know if you need more info
Thank You,
Michael D

 

[VoG]

Maybe like this

Workbooks.Open DBPathEstim
ThisWorkbook.Range("B5:B18").Copy Destination:=Workbooks("DBPathEstim.xls").Range("A1")

 

[daniels012]

Here is the path:

DBPathEstim = Left(Application.ThisWorkbook.Path, Len(Application.ThisWorkbook.Path) - 19) & "SurfaceSystemsFIles\Estimating2010.xls"

So using your code all I would need is this:

Workbooks.Open DBPathEstim
ThisWorkbook.Range("B5:B18").Copy Destination:=Workbooks(DBPathEstim).Range("B5")

Right?

Thank You,
Michael D

 

[VoG]

Sorry, you'll need to specify a sheet as well. Try like this

Workbooks.Open DBPathEstim
ThisWorkbook.Range("B5:B18").Copy Destination:=Workbooks("Estimating2010.xls").Sheets(1).Range("B5")

 

[daniels012]

Nice!
Is there anything I can add to close the old file.
I am copying the data from the old file to the newly opened WB and now I want to close the old one so I can start working on the new one.

Michael D

 

[daniels012]

I spoke to soon, I thought it was going to work.
It gives an error at this line:

ThisWorkbook.Range("B5:B18")

It is saying you cannot use Range with ThisWorkbook

Michael

 

[daniels012]

Here is what I have:

Workbooks.Open DBPathEstim
Workbooks(myFileName).Worksheets("Worksheet").Range("B5:B18").Copy Destination:=Workbooks("Estimating.xls").Range("B5")
                Workbooks(myFileName).Close

Is says subscript out of range?
Ideas?

Error on this line:

Workbooks(myFileName).Worksheets("Worksheet").Range("B5:B18").Copy Destination:=Workbooks("Estimating.xls").Range("B5")

Michael

 

[daniels012]

Here is all the code.
Maybe something else is messing us up:

Dim DBPath As String, myFileName As String, DTAddress As String, ans As String
        myFileName = Worksheets("Estimating").Range("A10").Value & ".xls"
        DBPath = Left(Application.ThisWorkbook.Path, Len(Application.ThisWorkbook.Path) - 19) & "Clients\"
        DBPathEstim = Left(Application.ThisWorkbook.Path, Len(Application.ThisWorkbook.Path) - 19) & "SurfaceSystemsFIles\Estimating2010.xls"
        DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
            If Dir(DBPath, vbDirectory) <> "" Then
                ActiveWorkbook.SaveAs DBPath & myFileName
            Else
                ActiveWorkbook.SaveAs DTAddress & myFileName
                MsgBox "Be sure to go to the desktop, move your saved file into to dropbox. "
            End If
        ans = MsgBox("Do you want to do another Estimating sheet FOR THIS SAME CLIENT?", vbQuestion + vbYesNo, "???")

            If ans = vbNo Then
                ActiveWorkbook.Close
            Else
                Workbooks.Open DBPathEstim
                Workbooks(myFileName).Worksheets("Worksheet").Range("B5:B18").Copy Destination:=Workbooks("Estimating.xls").Range("B5")
                Workbooks(myFileName).Close
                'EraseWorksheetLeaveAddress
            End If

Michael

 

[VoG]

You need a sheet number or name where shown in red

Workbooks(myFileName).Worksheets("Worksheet").Range("B5:B18").Copy Destination:=Workbooks("Estimating.xls").Sheets(1).Range("B5")

 

[daniels012]

If you look at my last post I have all of the code that I tried. With sheet names

Michael D