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

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Maybe like this

Code:
Workbooks.Open DBPathEstim
ThisWorkbook.Range("B5:B18").Copy Destination:=Workbooks("DBPathEstim.xls").Range("A1")
 
Upvote 0
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
 
Upvote 0
Sorry, you'll need to specify a sheet as well. Try like this

Rich (BB code):
Workbooks.Open DBPathEstim
ThisWorkbook.Range("B5:B18").Copy Destination:=Workbooks("Estimating2010.xls").Sheets(1).Range("B5")
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Here is what I have:
Code:
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
 
Upvote 0
Here is all the code.
Maybe something else is messing us up:
Code:
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
 
Upvote 0
You need a sheet number or name where shown in red

Rich (BB code):
Workbooks(myFileName).Worksheets("Worksheet").Range("B5:B18").Copy Destination:=Workbooks("Estimating.xls").Sheets(1).Range("B5")
 
Upvote 0
If you look at my last post I have all of the code that I tried. With sheet names

Michael D
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top