PDA

View Full Version : Solved: Copy Xrows from WrkBk to New WrkBk



mike31z
04-08-2012, 01:11 PM
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.

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

Thanks for looking

Mike in Wisconsin

mancubus
04-08-2012, 03:05 PM
hi.
assuming both workbooks are open; try:


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


you can call this sub from a third workbook.

mike31z
04-09-2012, 04:26 AM
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:friends:

mancubus
04-09-2012, 05:20 AM
You're wellcome.
Sure..

and thanks for marking the thread solved.