Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ReDim Problem
I'm new to VBA. I could use your help. Below is my code. What is
supposed to happen, is a sheet named CE(1) is supposed to get copied and placed at the end of all the sheets. Then the second part is supposed to clear certain data from the new sheet, but instead it clears it from the sheet before it. Many thanks for any help you can give. Private Sub CommandButton1_Click() ' ' Add_New_CE Macro ' Macro recorded 10/13/2002 by Casey Wilkins ' ' Dim Lastsheet As Long Lastsheet = Sheets.Count Sheets("CE(1)").Select Sheets("CE(1)").Copy After:=Sheets(Lastsheet) 'This bit of code is supposed to clear the old data from the new sheet, 'however, it clears the data from the sheet before it. As an example if 'the new sheet created above is the 8th sheet the following code clears 'the data from the seventh sheet. I figure Lastsheet needs to re dimention 'it shelf, just not sure how. Sheets(Lastsheet).Select Sheets(Lastsheet).Range("M7:M8").Select Selection.ClearContents Sheets(Lastsheet).Range("F45:F46").Select Selection.ClearContents Sheets(Lastsheet).Range("A13:I31").Select Selection.ClearContents 'This takes the user back to the beginning of the input range Sheets(Lastsheet).Range("M7").Select End Sub --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ReDim Problem
Casey
easiest way is to repeat the line: Lastsheet = Sheets.Count after you've copied the sheet. Lastsheet = Sheets.Count Sheets("CE(1)").Select Sheets("CE(1)").Copy After:=Sheets(Lastsheet) Lastsheet = Sheets.Count Then you don't have to recode any of the following statements. You've actually increased the number of sheets so "LastSheet" no longer refers to the last sheet but the one before that. Regards Trevor "Casey " wrote in message ... I'm new to VBA. I could use your help. Below is my code. What is supposed to happen, is a sheet named CE(1) is supposed to get copied and placed at the end of all the sheets. Then the second part is supposed to clear certain data from the new sheet, but instead it clears it from the sheet before it. Many thanks for any help you can give. Private Sub CommandButton1_Click() ' ' Add_New_CE Macro ' Macro recorded 10/13/2002 by Casey Wilkins ' ' Dim Lastsheet As Long Lastsheet = Sheets.Count Sheets("CE(1)").Select Sheets("CE(1)").Copy After:=Sheets(Lastsheet) 'This bit of code is supposed to clear the old data from the new sheet, 'however, it clears the data from the sheet before it. As an example if 'the new sheet created above is the 8th sheet the following code clears 'the data from the seventh sheet. I figure Lastsheet needs to re dimention 'it shelf, just not sure how. Sheets(Lastsheet).Select Sheets(Lastsheet).Range("M7:M8").Select Selection.ClearContents Sheets(Lastsheet).Range("F45:F46").Select Selection.ClearContents Sheets(Lastsheet).Range("A13:I31").Select Selection.ClearContents 'This takes the user back to the beginning of the input range Sheets(Lastsheet).Range("M7").Select End Sub --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ReDim Problem
Try this one Casey
Sub test() Sheets("CE(1)").Copy After:=Sheets(ThisWorkbook.Sheets.Count) With ActiveSheet .Range("M7:M8,F45:F46,A13:I31").ClearContents .Range("M7").Select End With End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Casey " wrote in message ... I'm new to VBA. I could use your help. Below is my code. What is supposed to happen, is a sheet named CE(1) is supposed to get copied and placed at the end of all the sheets. Then the second part is supposed to clear certain data from the new sheet, but instead it clears it from the sheet before it. Many thanks for any help you can give. Private Sub CommandButton1_Click() ' ' Add_New_CE Macro ' Macro recorded 10/13/2002 by Casey Wilkins ' ' Dim Lastsheet As Long Lastsheet = Sheets.Count Sheets("CE(1)").Select Sheets("CE(1)").Copy After:=Sheets(Lastsheet) 'This bit of code is supposed to clear the old data from the new sheet, 'however, it clears the data from the sheet before it. As an example if 'the new sheet created above is the 8th sheet the following code clears 'the data from the seventh sheet. I figure Lastsheet needs to re dimention 'it shelf, just not sure how. Sheets(Lastsheet).Select Sheets(Lastsheet).Range("M7:M8").Select Selection.ClearContents Sheets(Lastsheet).Range("F45:F46").Select Selection.ClearContents Sheets(Lastsheet).Range("A13:I31").Select Selection.ClearContents 'This takes the user back to the beginning of the input range Sheets(Lastsheet).Range("M7").Select End Sub --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ReDim Problem
I happened to try Ron solution first and it worked perfectly, but thanks
to both Ron and Trevor for the help guys. I'm pretty good at the heavy formula stuff, but this VBA can take my work a lot farther and make it tons easier, if I can get the hang of it. Thanks again, you guys are brilliant. --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ReDim Problem
Casey
I was just being lazy. Ron did a "proper job" ;-) It's neat and it'll be quicker. Regards Trevor "Casey " wrote in message ... I happened to try Ron solution first and it worked perfectly, but thanks to both Ron and Trevor for the help guys. I'm pretty good at the heavy formula stuff, but this VBA can take my work a lot farther and make it tons easier, if I can get the hang of it. Thanks again, you guys are brilliant. --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
VBA syntax help: ReDim Preserve an array | Excel Discussion (Misc queries) | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
ReDim an Array | Excel Programming | |||
Redim MyArray | Excel Programming |