Copy cells....

ExcelNovice

Well-known Member
Joined
May 12, 2002
Messages
583
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...
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

attc

Board Regular
Joined
Oct 11, 2002
Messages
87
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

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
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
 

Forum statistics

Threads
1,143,707
Messages
5,720,386
Members
422,282
Latest member
psunith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top