VBA Code to copy data from one workbook to another

[SaraB802]

Ok, so this is my dilema, I am trying to set up a template that can copy data from a sheet in one workbook and paste it in to another. So why am I failing.

The code is below. The first part opens the workbook that is designated in cell B3. Next I need to copy the data from that workbook on the database worksheet that starts at A10, the range varies so I have used currentRegion.copy. And finally it needs to paste it on the worksheet named project 1 in Portfolio Rollup Sample.xlsm file. I have tried to use paste special so that the numbers remain the correct format and no links to the original spreadsheet are retained.

Ultimately I am going to have to get this to loop through to repeat with different source files, all set up the same as the first, on to sheets project 2, 3 etc.


Sub ImportDatabases()
Dim ExtFile As String
Dim ExtBk As Workbook

ExtFile = Range("B3").Value
If Not ExtFile = "" And Dir(ExtFile) <> "" Then
Else
ExtFile = Application.GetOpenFilename(FileFilter:="microsoft excel files (*.xls), *.xls", Title:="Please Select A File")
End If
On Error Resume Next
Set ExtBk = Workbooks(Dir(ExtFile))
On Error GoTo 0
If ExtBk Is Nothing Then
Application.Workbooks.Open ExtFile
Set ExtBk = Workbooks(Dir(ExtFile))
End If
'TO HERE OPENS THE FIRST FILE


'THIS NEXT BIT IS TO COPY AND PASTE THE DATABASE
Worksheets("Database").Activate
Range("A10").CurrentRegion.Copy
Workbooks("Portfolio Rollup Sample.xlsm").Worksheets("Project 1").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats



End Sub


All help to resolve what I am sure is a simple problem much appreciated.

 

[RCBricker]

where is the error occurring in your code?

Open the sub and use F8 to tab through the code line by line. when the error occurs chose debug and see which line is highlighted.

Or if no error, but no results. Use the above and see what happens compared to what you expect to happen. See where those two ideals differ.

Some thoughts...

I would set your Portfolio Rollup Sample workbook to a variable. Easier to write code for it later. I would do the same for the Project Sheets. you can use an integer that cycles up in your loop for help setting the project sheets...something like:

set wsPRO=wbPORT.sheets("Project " & intPro)

couple questions:

How do you know that cell B3 represents a file used for Project 1?
is Cell B3 just the file name or is it the full path and file name?

 

[SaraB802]

Well, the good news is I have resolved the issue, but how I am not sure.

The solution that now works is:

Sub ImportDatabases()
Dim ExtFile As String
Dim ExtBk As Workbook

ExtFile = Range("B3").Value
If Not ExtFile = "" And Dir(ExtFile) <> "" Then
Else
ExtFile = Application.GetOpenFilename(FileFilter:="microsoft excel files (*.xls), *.xls", Title:="Please Select A File")
End If
On Error Resume Next
Set ExtBk = Workbooks(Dir(ExtFile))
On Error GoTo 0
If ExtBk Is Nothing Then
Application.Workbooks.Open ExtFile
Set ExtBk = Workbooks(Dir(ExtFile))
End If
'TO HERE OPENS THE FIRST FILE


Worksheets("database").Select
Sheets("Database").Range("A10").CurrentRegion.Select
Selection.Copy


' select current workbook and paste the values starting at A1
Windows("Portfolio Rollup Sample.xlsm").Activate
Worksheets("Project 1").Select
Sheets("Project 1").Range("A1").Select
Worksheets("Project 1").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats


End Sub

 

[RCBricker]

ok well glad you got it working