Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data keeps getting overwritten by macro
I have a workbook called book1.xls and a workbook called specifications.xls
I have a macro: Dim wbA As Workbook Dim wbB As Workbook Dim shA As Worksheet Dim shB As Worksheet Set wbA = ThisWorkbook Set wbB = Workbooks.Open(ThisWorkbook.Path & "\book1.xls") Set shA = wbA.Sheets("BOM") shA.Range("A1:O66").Copy Set shB = wbB.Sheets.Add ActiveSheet.Paste Destination:=shB.Range("A1") 'shB.Name = shB.Range("C62") With shB .Columns("O:O").EntireColumn.AutoFit .Columns("C:C").ColumnWidth = 10.71 .Columns("C:C").ColumnWidth = 13.57 .Columns("B:B").ColumnWidth = 12 .Range("A1").Select End With wbB.Close SaveChanges:=True End Sub this copies what ever is in "BOM" sheet on specifications.xls and then opens book1.xls and paste the data on a new worksheet.. This works fine the first time but then the second time it creates a new worksheet on book1.xls (which is a good thing) but it over writes the previous worsheet! why is this happening? I need it to copy the data to a new worksheet and not overwrite any data... Any help? Thank you, TG |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data keeps getting overwritten by macro
Your code worked fine for me.
But I would have used: shB.Paste Destination:=shB.Range("A1") instead of: ActiveSheet.Paste Destination:=shB.Range("A1") My test code was in a general module of activeworkbook (specifications.xls in your sample). TG wrote: I have a workbook called book1.xls and a workbook called specifications.xls I have a macro: Dim wbA As Workbook Dim wbB As Workbook Dim shA As Worksheet Dim shB As Worksheet Set wbA = ThisWorkbook Set wbB = Workbooks.Open(ThisWorkbook.Path & "\book1.xls") Set shA = wbA.Sheets("BOM") shA.Range("A1:O66").Copy Set shB = wbB.Sheets.Add ActiveSheet.Paste Destination:=shB.Range("A1") 'shB.Name = shB.Range("C62") With shB .Columns("O:O").EntireColumn.AutoFit .Columns("C:C").ColumnWidth = 10.71 .Columns("C:C").ColumnWidth = 13.57 .Columns("B:B").ColumnWidth = 12 .Range("A1").Select End With wbB.Close SaveChanges:=True End Sub this copies what ever is in "BOM" sheet on specifications.xls and then opens book1.xls and paste the data on a new worksheet.. This works fine the first time but then the second time it creates a new worksheet on book1.xls (which is a good thing) but it over writes the previous worsheet! why is this happening? I need it to copy the data to a new worksheet and not overwrite any data... Any help? Thank you, TG -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data keeps getting overwritten by macro
Dave,
can you try this multiple times.. and let me know what happens? when I run it the first time is fine but when I run it the second time it over writes all of the newly created worksheets except the first one... I am using this macro in a button if that helps... thanks "Dave Peterson" wrote: Your code worked fine for me. But I would have used: shB.Paste Destination:=shB.Range("A1") instead of: ActiveSheet.Paste Destination:=shB.Range("A1") My test code was in a general module of activeworkbook (specifications.xls in your sample). TG wrote: I have a workbook called book1.xls and a workbook called specifications.xls I have a macro: Dim wbA As Workbook Dim wbB As Workbook Dim shA As Worksheet Dim shB As Worksheet Set wbA = ThisWorkbook Set wbB = Workbooks.Open(ThisWorkbook.Path & "\book1.xls") Set shA = wbA.Sheets("BOM") shA.Range("A1:O66").Copy Set shB = wbB.Sheets.Add ActiveSheet.Paste Destination:=shB.Range("A1") 'shB.Name = shB.Range("C62") With shB .Columns("O:O").EntireColumn.AutoFit .Columns("C:C").ColumnWidth = 10.71 .Columns("C:C").ColumnWidth = 13.57 .Columns("B:B").ColumnWidth = 12 .Range("A1").Select End With wbB.Close SaveChanges:=True End Sub this copies what ever is in "BOM" sheet on specifications.xls and then opens book1.xls and paste the data on a new worksheet.. This works fine the first time but then the second time it creates a new worksheet on book1.xls (which is a good thing) but it over writes the previous worsheet! why is this happening? I need it to copy the data to a new worksheet and not overwrite any data... Any help? Thank you, TG -- Dave Peterson . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data keeps getting overwritten by macro
I tried it 3 or 4 times with no problems.
How many times is multiple? TG wrote: Dave, can you try this multiple times.. and let me know what happens? when I run it the first time is fine but when I run it the second time it over writes all of the newly created worksheets except the first one... I am using this macro in a button if that helps... thanks "Dave Peterson" wrote: Your code worked fine for me. But I would have used: shB.Paste Destination:=shB.Range("A1") instead of: ActiveSheet.Paste Destination:=shB.Range("A1") My test code was in a general module of activeworkbook (specifications.xls in your sample). TG wrote: I have a workbook called book1.xls and a workbook called specifications.xls I have a macro: Dim wbA As Workbook Dim wbB As Workbook Dim shA As Worksheet Dim shB As Worksheet Set wbA = ThisWorkbook Set wbB = Workbooks.Open(ThisWorkbook.Path & "\book1.xls") Set shA = wbA.Sheets("BOM") shA.Range("A1:O66").Copy Set shB = wbB.Sheets.Add ActiveSheet.Paste Destination:=shB.Range("A1") 'shB.Name = shB.Range("C62") With shB .Columns("O:O").EntireColumn.AutoFit .Columns("C:C").ColumnWidth = 10.71 .Columns("C:C").ColumnWidth = 13.57 .Columns("B:B").ColumnWidth = 12 .Range("A1").Select End With wbB.Close SaveChanges:=True End Sub this copies what ever is in "BOM" sheet on specifications.xls and then opens book1.xls and paste the data on a new worksheet.. This works fine the first time but then the second time it creates a new worksheet on book1.xls (which is a good thing) but it over writes the previous worsheet! why is this happening? I need it to copy the data to a new worksheet and not overwrite any data... Any help? Thank you, TG -- Dave Peterson . -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data keeps getting overwritten by macro
Keeps adding more worksheets to Book1.xls with the data from WbA BOM! A1:O66
Adds a new sheet each time it runs, does not overwrite anything. You state........... when I run it the first time is fine but when I run it the second time it over writes all of the newly created worksheets except the first one... If you run it once there will be only one newly created worksheet so where does "all" come from? I would add one line to the macro Application.CutCopyMode = False just above End With Gord Dibben MS Excel MVP On Fri, 23 Oct 2009 13:37:01 -0700, TG wrote: Dave, can you try this multiple times.. and let me know what happens? when I run it the first time is fine but when I run it the second time it over writes all of the newly created worksheets except the first one... I am using this macro in a button if that helps... thanks "Dave Peterson" wrote: Your code worked fine for me. But I would have used: shB.Paste Destination:=shB.Range("A1") instead of: ActiveSheet.Paste Destination:=shB.Range("A1") My test code was in a general module of activeworkbook (specifications.xls in your sample). TG wrote: I have a workbook called book1.xls and a workbook called specifications.xls I have a macro: Dim wbA As Workbook Dim wbB As Workbook Dim shA As Worksheet Dim shB As Worksheet Set wbA = ThisWorkbook Set wbB = Workbooks.Open(ThisWorkbook.Path & "\book1.xls") Set shA = wbA.Sheets("BOM") shA.Range("A1:O66").Copy Set shB = wbB.Sheets.Add ActiveSheet.Paste Destination:=shB.Range("A1") 'shB.Name = shB.Range("C62") With shB .Columns("O:O").EntireColumn.AutoFit .Columns("C:C").ColumnWidth = 10.71 .Columns("C:C").ColumnWidth = 13.57 .Columns("B:B").ColumnWidth = 12 .Range("A1").Select End With wbB.Close SaveChanges:=True End Sub this copies what ever is in "BOM" sheet on specifications.xls and then opens book1.xls and paste the data on a new worksheet.. This works fine the first time but then the second time it creates a new worksheet on book1.xls (which is a good thing) but it over writes the previous worsheet! why is this happening? I need it to copy the data to a new worksheet and not overwrite any data... Any help? Thank you, TG -- Dave Peterson . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data keeps getting overwritten by macro
How about 12?
I don't have a life so got lots of time<g Gord On Fri, 23 Oct 2009 16:01:46 -0500, Dave Peterson wrote: I tried it 3 or 4 times with no problems. How many times is multiple? TG wrote: Dave, can you try this multiple times.. and let me know what happens? when I run it the first time is fine but when I run it the second time it over writes all of the newly created worksheets except the first one... I am using this macro in a button if that helps... thanks "Dave Peterson" wrote: Your code worked fine for me. But I would have used: shB.Paste Destination:=shB.Range("A1") instead of: ActiveSheet.Paste Destination:=shB.Range("A1") My test code was in a general module of activeworkbook (specifications.xls in your sample). TG wrote: I have a workbook called book1.xls and a workbook called specifications.xls I have a macro: Dim wbA As Workbook Dim wbB As Workbook Dim shA As Worksheet Dim shB As Worksheet Set wbA = ThisWorkbook Set wbB = Workbooks.Open(ThisWorkbook.Path & "\book1.xls") Set shA = wbA.Sheets("BOM") shA.Range("A1:O66").Copy Set shB = wbB.Sheets.Add ActiveSheet.Paste Destination:=shB.Range("A1") 'shB.Name = shB.Range("C62") With shB .Columns("O:O").EntireColumn.AutoFit .Columns("C:C").ColumnWidth = 10.71 .Columns("C:C").ColumnWidth = 13.57 .Columns("B:B").ColumnWidth = 12 .Range("A1").Select End With wbB.Close SaveChanges:=True End Sub this copies what ever is in "BOM" sheet on specifications.xls and then opens book1.xls and paste the data on a new worksheet.. This works fine the first time but then the second time it creates a new worksheet on book1.xls (which is a good thing) but it over writes the previous worsheet! why is this happening? I need it to copy the data to a new worksheet and not overwrite any data... Any help? Thank you, TG -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
overwritten files | Excel Discussion (Misc queries) | |||
Overwritten Equations | Excel Discussion (Misc queries) | |||
Overwritten Fil | Excel Discussion (Misc queries) | |||
overwritten file | Charts and Charting in Excel | |||
.XLB OverWritten | Excel Discussion (Misc queries) |