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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Maybe like this

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

daniels012

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

Legend
Joined
Jun 19, 2002
Messages
63,650
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")
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219

ADVERTISEMENT

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

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

Well-known Member
Joined
Jan 13, 2005
Messages
5,219

ADVERTISEMENT

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
 

daniels012

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

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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")
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
If you look at my last post I have all of the code that I tried. With sheet names

Michael D
 

Watch MrExcel Video

Forum statistics

Threads
1,109,461
Messages
5,528,935
Members
409,847
Latest member
Foster034
Top