copy from one workbook to another

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

steven - 16 Feb 2007 08:35 GMT

Hello people,

Id like to construct a macro that copies all rows from the current worksheet
(copy the whole lenght of the row just as leftclicking the 1st row
number and holding and draging down until the last row with data is
selected would), and then copying it to another workbok, workbook2, sheet2
row2 , just as
rightclicking row 2 and chosing insert copied cells would. any suggestions?

Thank you in advance.

Reply to this Message

Joel - 16 Feb 2007 11:53 GMT

I don't know how much experience you have in VBA. If you arre experience
then try these statements

Worksheets(SavedWorksheetName).Activate
PlotRange.RefEditPlotCell.SetFocus
PlotRange.Show
PlotRangeString = PlotRange.RefEditPlotCell.Value

This will puu up a nice pop up window to select cells.

> Hello people,
>
[quoted text clipped - 6 lines]
>
> Thank you in advance.

Reply to this Message

steven - 16 Feb 2007 16:20 GMT

Hi, thanks for your input but i am really not experienced. I tried a little
with the code you gave me but i can't figure it out... can I please have the
actual code if it's not much of a fuss... it the path of the file for the
data to be copied at is a problem lets say its:
C:\Documents and Settings\User\Desktop\b.xls

thanks,

S.

> I don't know how much experience you have in VBA. If you arre experience
> then try these statements
[quoted text clipped - 16 lines]
> >
> > Thank you in advance.

Reply to this Message

Joel - 16 Feb 2007 19:39 GMT

It took me a while to gedt the bugs out of the code. Excel is very picky
with the format

You have to set up a user form.
follow these istructions

1) In Visual Basic editor - Insert User form
2)Make sure Standard Toolbar is enabled
3) In standard toolbar select the Wrench and hammer
4) Slide The Reff Edit box and the commmand button into the userform1
5) double click the command button or click right mouser button and view
code. Add to Sub CommandButton1_Click()
UserForm1.Hide
end Sub

Add Code below to a Module

Sub Copy_worksheet()
'
'

'
oldworkbook_name = ActiveWorkbook.Name

Worksheets("Sheet1").Activate
UserForm1.RefEdit1.SetFocus
UserForm1.Show
oldcopyrangeString = UserForm1.RefEdit1.Value

Range(oldcopyrangeString).Select
Selection.Copy

atpfilename = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
Workbooks.Open Filename:=atpfilename

'getworkbook name
Do While (InStr(atpfilename, "\") <> 0)
x = InStr(atpfilename, "\")
atpfilename = Mid(atpfilename, InStr(atpfilename, "\") + 1)
Loop

UserForm1.RefEdit1.SetFocus
UserForm1.Show
copyrangeString = UserForm1.RefEdit1.Value

If InStr(copyrangeString, "!") <> 0 Then
copyrangeString = Mid(copyrangeString, InStr(copyrangeString, "!") +
1)
End If

Workbooks(oldworkbook_name).Activate
Range(oldcopyrangeString).Select
Selection.Copy

Windows(atpfilename).Activate

Range(copyrangeString).Select

ActiveSheet.Paste

> Hello people,
>
[quoted text clipped - 6 lines]
>
> Thank you in advance.

Reply to this Message

Joel - 16 Feb 2007 19:50 GMT

I willhelp you clean up the command box if we get it to work. You can e-mail
me at joel.warburg@itt.com. I will be leavving work in about 1/2 hour. I
will check my e-mail over the weekend.

> Hello people,
>
[quoted text clipped - 6 lines]
>
> Thank you in advance.

Reply to this Message