VBA Code to copy a worksheet to another Excel Workbook
 

I am a newbie in VBA and need your help.

I want to do the following from an open Workbook (Destination).

1. Open an Excel Workbook (Source) somewhere on my hard drive - name
of path and file name varies from user to user. Therefore code must
allow user to choose (variable)

2. copy contents of a specific worksheet (Tab name known) to the
active open Workbook (Destination)

The code I have started with this code - (please don't laugh) - but
it lets a lot to be desired.

myFile = Application.GetOpenFilename("All Files,(*.*),*.*")

Workbooks.Open Filename:=myFile
Sheets("Consolidated").Select
Cells.Select
Selection.Copy


I do not know how to bring it into the destination workbook and
specific sheet.

3. Once the cells are copied to the Worksheet, I then want the Soruce
Wprkbook to be closed


Looking forward to your help

VBA Code to copy a worksheet to another Excel Workbook
 

Try recording it once to get the idea and then modify to suit.

--
Don Guillett
SalesAid Software

"Hummel" wrote in message
om...
I am a newbie in VBA and need your help.

I want to do the following from an open Workbook (Destination).

1. Open an Excel Workbook (Source) somewhere on my hard drive - name
of path and file name varies from user to user. Therefore code must
allow user to choose (variable)

2. copy contents of a specific worksheet (Tab name known) to the
active open Workbook (Destination)

The code I have started with this code - (please don't laugh) - but
it lets a lot to be desired.

myFile = Application.GetOpenFilename("All Files,(*.*),*.*")

Workbooks.Open Filename:=myFile
Sheets("Consolidated").Select
Cells.Select
Selection.Copy


I do not know how to bring it into the destination workbook and
specific sheet.

3. Once the cells are copied to the Worksheet, I then want the Soruce
Wprkbook to be closed


Looking forward to your help

VBA Code to copy a worksheet to another Excel Workbook
 

Since you want to copy all the cells, how about just copying the whole
worksheet?

Option Explicit
Sub testme()
Dim myFileName As Variant
Dim SourceWkbk As Workbook
Dim CurrentWkbk As Workbook
Dim testWks As Worksheet

myFileName = Application.GetOpenFilename("Excel files,*.xls")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Set CurrentWkbk = ActiveWorkbook
Set SourceWkbk = Workbooks.Open(Filename:=myFileName)

Set testWks = Nothing
On Error Resume Next
Set testWks = SourceWkbk.Worksheets("Consolidated")
On Error GoTo 0

If testWks Is Nothing Then
MsgBox "Missing the worksheet!"
Else
testWks.Copy _
befo=CurrentWkbk.Worksheets(1)
End If

SourceWkbk.Close savechanges:=False

End Sub



Hummel wrote:

I am a newbie in VBA and need your help.

I want to do the following from an open Workbook (Destination).

1. Open an Excel Workbook (Source) somewhere on my hard drive - name
of path and file name varies from user to user. Therefore code must
allow user to choose (variable)

2. copy contents of a specific worksheet (Tab name known) to the
active open Workbook (Destination)

The code I have started with this code - (please don't laugh) - but
it lets a lot to be desired.

myFile = Application.GetOpenFilename("All Files,(*.*),*.*")

Workbooks.Open Filename:=myFile
Sheets("Consolidated").Select
Cells.Select
Selection.Copy


I do not know how to bring it into the destination workbook and
specific sheet.

3. Once the cells are copied to the Worksheet, I then want the Soruce
Wprkbook to be closed

Looking forward to your help

--

Dave Peterson

VBA Code to copy a worksheet to another Excel Workbook
 

testWks was a variable that I used to test to see if the worksheet existed.

If it existed, then I could use it further down in the code. If "consolidated"
didn't exist, then the code issues a warning and doesn't do anything.

In the following, I used column A to determine the next available row (in the
EMEA worksheet). Can you pick out a column that you could use for that?

Option Explicit
Sub testme()
Dim myFileName As Variant
Dim SourceWkbk As Workbook
Dim CurrentWkbk As Workbook
Dim testWks As Worksheet
Dim DestCell As Range

myFileName = Application.GetOpenFilename("Excel files,*.xls")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Set CurrentWkbk = ActiveWorkbook
Set SourceWkbk = Workbooks.Open(Filename:=myFileName)

Set testWks = Nothing
On Error Resume Next
Set testWks = SourceWkbk.Worksheets("Consolidated")
On Error GoTo 0

If testWks Is Nothing Then
MsgBox "Missing the worksheet!"
Else
With CurrentWkbk.Worksheets("emea")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
With testWks
'choose one of these
.Range("a1", .Cells.SpecialCells(xlCellTypeLastCell)).Copy
DestCell.PasteSpecial Paste:=xlPasteValues
'or to copy formulas|formatting...
.Range("a1", .Cells.SpecialCells(xlCellTypeLastCell)).Copy _
Destination:=DestCell
End With
End If

SourceWkbk.Close savechanges:=False

End Sub

Hummel wrote:

Dave Peterson wrote in message ...
Since you want to copy all the cells, how about just copying the whole
worksheet?

Option Explicit
Sub testme()
Dim myFileName As Variant
Dim SourceWkbk As Workbook
Dim CurrentWkbk As Workbook
Dim testWks As Worksheet

myFileName = Application.GetOpenFilename("Excel files,*.xls")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Set CurrentWkbk = ActiveWorkbook
Set SourceWkbk = Workbooks.Open(Filename:=myFileName)

Set testWks = Nothing
On Error Resume Next
Set testWks = SourceWkbk.Worksheets("Consolidated")
On Error GoTo 0

If testWks Is Nothing Then
MsgBox "Missing the worksheet!"
Else
testWks.Copy _
befo=CurrentWkbk.Worksheets(1)
End If

SourceWkbk.Close savechanges:=False

End Sub



Hummel wrote:

I am a newbie in VBA and need your help.

I want to do the following from an open Workbook (Destination).

1. Open an Excel Workbook (Source) somewhere on my hard drive - name
of path and file name varies from user to user. Therefore code must
allow user to choose (variable)

2. copy contents of a specific worksheet (Tab name known) to the
active open Workbook (Destination)

The code I have started with this code - (please don't laugh) - but
it lets a lot to be desired.

myFile = Application.GetOpenFilename("All Files,(*.*),*.*")

Workbooks.Open Filename:=myFile
Sheets("Consolidated").Select
Cells.Select
Selection.Copy


I do not know how to bring it into the destination workbook and
specific sheet.

3. Once the cells are copied to the Worksheet, I then want the Soruce
Wprkbook to be closed

Looking forward to your help

I need to copy the contents of the worksheet onto another worksheet in
the destination file, as I have another workbook tht conolsidated the
data in the destination worksheets.

I would appreciate an update on this. I could not amend the code you
kindly sent, as I did not understand some of the codes e.g. - testwks
etc.??

--

Dave Peterson