copy from one workbook to another
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
"steven" wrote:
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.
|