Solved: Copy Xrows from WrkBk to New WrkBk

[mike31z]

I have this little code that I modified and can not get it to work.
Source File Name= Event1.xls with one Tab named Event1 (This was created by importing a txt file in csv format.

The destinagation file is E1Mgmt.xls exsisting tab "import" There are other tabs but I would like the data being copied on the existing "import" tab.

I get the code to work but it creates new worksheet named "Import (1) and so on" I need it to overwrite the exisiting import worksheet.

[VBA]Sub copyEvt()
'
' copyEvt Macro
' Macro recorded 7/26/2011 by Mike
'
Windows("Event1.xls").Activate
Sheets("Event1").Select
Sheets("import").Copy Before:=Workbooks("E1Mgmt.xls").Sheets(1)
Cells.Select
Selection.Interior.ColorIndex = xlNone
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("B3").Select

End Sub[/VBA]

Thanks for looking

Mike in Wisconsin

[mancubus]

hi.
assuming both workbooks are open; try:

[VBA]
Sub CopyRngToAnotherWB()

Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Workbooks("Event1.xls").Sheets("Event1")
Set ws2 = Workbooks("E1Mgmt.xls").Sheets("import")

ws1.UsedRange.Copy ws2.Range("A1")

with ws2
.Cells.Interior.ColorIndex = xlNone
.Rows(1).Insert
End With

End Sub
[/VBA]

you can call this sub from a third workbook.

[mike31z]

Thanks mancubus
That works just just fine. It looks easy to modify for other worksheet copy and paste functions.
Thanks for the quick response.

Mike in Wiscsonsin

[mancubus]

You're wellcome.
Sure..

and thanks for marking the thread solved.