Copy cells....

[ExcelNovice]

Hi all,

Is it possible to write a macro to copy a particular range of cells to a new sheet in a known workbook?

This known workbook will be different each time, so the user will have to be given an option to indicate the name of the workbook the range of cells should be copied to.

Three things are therefore needed:
1. The macro will prompt the user to indicate the workbook to which the range of cells will be copied.
2. The macro will create a new sheet in that workbook
3. The macro will copy the range of cells that workbook

Thanks for your help...

 

[attc]

as no-one else has replied yet, I'll give this a try:

Sub customCopy()

Dim bName As String
Dim flag As Boolean

On Error Resume Next

bName = InputBox("Enter destination workbook:")
flag = Len(Workbooks(bName).Name)

If (flag) Then

Selection.Copy
Workbooks(bName).Activate
Sheets.Add
Selection.PasteSpecial Paste:=xlValues

Else
MsgBox ("Please check destination workbook is open")

End If

End Sub


Notes/Assumptions
(i) The current selection should be copied
(ii) VALUES only sb copied
(iii) Destination on new sheet is A1

HTH

 

[jimboy]

try this;

Sub macro1()

fname = InputBox("What is the filename?")
firstcell = InputBox("What is the first cell in range?")
lastcell = InputBox("What is the last cell in range?")
'sname = InputBox("What sheet name?") 'can take this out if you don't need the sheet name to be selected
Workbooks.Open FileName:="C:" & fname & ".xls"
' Sheets(sname).Select ' and this
Range(firstcell, lastcell).Select
Selection.Copy
Worksheets.Add
ActiveSheet.Paste
End Sub