Macro copy data from sheet and insert.

JackDanIce

Well-known Member

Joined

Feb 3, 2010

Messages

9,922

Office Version

1. 365

Platform

1. Windows

• Oct 25, 2010

• #2

Yes it is possible. What are the names of the source and destination workbook and worksheets? Can you post the code you have so far?

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Oct 25, 2010

• #3

The code would be like this

Code:

ActiveSheet.UsedRange.Copy Destination:=Workbooks("Book2.xls").Worksheets("Sheet1").Range("A1")

 

Upvote 0

D

daniel.s

New Member

Joined

Jun 9, 2010

Messages

27

• Oct 25, 2010

• #4

Hi Jack,

Unfortunately the source worksheet/book name will change. The destination will remain the same. The Desintation is called DATA (sheet name) Cost Validation (workbook name).

Many thanks.

 

Upvote 0

D

daniel.s

New Member

Joined

Jun 9, 2010

Messages

27

• Oct 25, 2010

• #5

Code so far...

Sub Button3_Click()
Sheets("DATA").Select
resp = MsgBox(prompt:="Please select the AB invoice backup that you" _
& " would like to add, make sure the file is NOT currently open.", _
Buttons:=vbOKCancel)
If resp = vbCancel Then
Exit Sub
End If
'the data would then be inserted into the Sheet named on the top line.

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Oct 25, 2010

• #6

Try something like this

Code:

Sub Button3_Click()
Dim sFile As Variant, wb As Workbook
sFile = Application.GetOpenFilename("Files (*.xls),*.xls", , "Select A File")
If sFile = "False" Then Exit Sub
Set wb = Workbooks.Open(sFile)
wb.Sheets("Sheet1").UsedRange.Copy Destination:=ThisWorkbook.Worksheets("DATA").Range("A1")
wb.Close savechanges:=False
End If

 

Upvote 0

D

daniel.s

New Member

Joined

Jun 9, 2010

Messages

27

• Oct 25, 2010

• #7

This does not seem to want to open the sheet - I don't think the source worksheet is always called 'Sheet1'.

Thanks.

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Oct 25, 2010

• #8

Will it always be the first sheet?

 

Upvote 0

D

daniel.s

New Member

Joined

Jun 9, 2010

Messages

27

• Oct 25, 2010

• #9

Yes...definintely.

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Oct 25, 2010

• #10

This worked for me

Code:

Sub Button3_Click()
Dim sFile As Variant, wb As Workbook
sFile = Application.GetOpenFilename("Files (*.xls),*.xls", , "Select A File")
If sFile = "False" Then Exit Sub
Set wb = Workbooks.Open(sFile)
wb.Sheets(1).UsedRange.Copy Destination:=ThisWorkbook.Worksheets("DATA").Range("A1")
wb.Close savechanges:=False
End Sub

 

Upvote 0