Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Help
I am having some problems getting this code to work like it has in the past.
I am sure that there is an easier way to accomplish this task, however, I must be missing something. To give you the appropriate background, I have a workbook that contains numerous pages. I have built a macro which creates a new workbook named NWB. Then it copies selected worksheets into the new workbook. The first part of the ode below works fine to eliminate the buttons found on worksheet named SRN. However, when I get to the line with the . I get a type mismatch error, and everything stops. I am using the 'With' since I am activating this code from a worksheet in the original workbook. In simple terms, I just want to start at a known cell in workbook NWB and worksheet SRN and find out the bottom row of the table with an entry in column 3. Then I copy it to the clipboard and past back the cell contents as values. This is being done since I don't copy the worksheet with the data that these cells reference into the new workbook. Please give me some guidance on how to accomplish this task. Thanks! ' remove command buttons from Sheet Dim i As Integer, N As Integer Dim II As Integer With NWB.Worksheets(SRN) 'get the number of OLEObjects on the sheet N = .OLEObjects.Count For i = N To 1 Step -1 'check the type of object. If a command button, delete it If LCase(TypeName(.OLEObjects(i).Object)) = _ "commandbutton" Then .OLEObjects(i).Delete Next ' convert the MM and MN cells to values II = 20 ' start on row 20 Do Until .Cells(II, 3) = "" ' find end of column II = II + 1 Loop II = II - 1 ' get back to last row .Range(.Cells(20, 3), .Cells(II, 4)).Copy ' get the range onto the clipboard .Range(.Cells(20, 3), .Cells(II, 4)).PasteSpecial (xlValues) End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Help
Ray,
If you have a lot of rows, than Dim II as Long (not as Integer). -- sb "Ray Batig" wrote in message nk.net... I am having some problems getting this code to work like it has in the past. I am sure that there is an easier way to accomplish this task, however, I must be missing something. To give you the appropriate background, I have a workbook that contains numerous pages. I have built a macro which creates a new workbook named NWB. Then it copies selected worksheets into the new workbook. The first part of the ode below works fine to eliminate the buttons found on worksheet named SRN. However, when I get to the line with the . I get a type mismatch error, and everything stops. I am using the 'With' since I am activating this code from a worksheet in the original workbook. In simple terms, I just want to start at a known cell in workbook NWB and worksheet SRN and find out the bottom row of the table with an entry in column 3. Then I copy it to the clipboard and past back the cell contents as values. This is being done since I don't copy the worksheet with the data that these cells reference into the new workbook. Please give me some guidance on how to accomplish this task. Thanks! ' remove command buttons from Sheet Dim i As Integer, N As Integer Dim II As Integer With NWB.Worksheets(SRN) 'get the number of OLEObjects on the sheet N = .OLEObjects.Count For i = N To 1 Step -1 'check the type of object. If a command button, delete it If LCase(TypeName(.OLEObjects(i).Object)) = _ "commandbutton" Then .OLEObjects(i).Delete Next ' convert the MM and MN cells to values II = 20 ' start on row 20 Do Until .Cells(II, 3) = "" ' find end of column II = II + 1 Loop II = II - 1 ' get back to last row .Range(.Cells(20, 3), .Cells(II, 4)).Copy ' get the range onto the clipboard .Range(.Cells(20, 3), .Cells(II, 4)).PasteSpecial (xlValues) End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Help
I think we cannot use Cells(II,3) straightaway for comparing values.
Using Cells(II,3).value = "" might be the reason for the error. "steve" wrote in message ... Ray, If you have a lot of rows, than Dim II as Long (not as Integer). -- sb "Ray Batig" wrote in message nk.net... I am having some problems getting this code to work like it has in the past. I am sure that there is an easier way to accomplish this task, however, I must be missing something. To give you the appropriate background, I have a workbook that contains numerous pages. I have built a macro which creates a new workbook named NWB. Then it copies selected worksheets into the new workbook. The first part of the ode below works fine to eliminate the buttons found on worksheet named SRN. However, when I get to the line with the . I get a type mismatch error, and everything stops. I am using the 'With' since I am activating this code from a worksheet in the original workbook. In simple terms, I just want to start at a known cell in workbook NWB and worksheet SRN and find out the bottom row of the table with an entry in column 3. Then I copy it to the clipboard and past back the cell contents as values. This is being done since I don't copy the worksheet with the data that these cells reference into the new workbook. Please give me some guidance on how to accomplish this task. Thanks! ' remove command buttons from Sheet Dim i As Integer, N As Integer Dim II As Integer With NWB.Worksheets(SRN) 'get the number of OLEObjects on the sheet N = .OLEObjects.Count For i = N To 1 Step -1 'check the type of object. If a command button, delete it If LCase(TypeName(.OLEObjects(i).Object)) = _ "commandbutton" Then .OLEObjects(i).Delete Next ' convert the MM and MN cells to values II = 20 ' start on row 20 Do Until .Cells(II, 3) = "" ' find end of column II = II + 1 Loop II = II - 1 ' get back to last row .Range(.Cells(20, 3), .Cells(II, 4)).Copy ' get the range onto the clipboard .Range(.Cells(20, 3), .Cells(II, 4)).PasteSpecial (xlValues) End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Help
Thanks to both of you. I tried both suggestions independently and together,
however, they did not solve the problem.. Any more suggestions? Thanks for trying! Ray Kamal wrote in message ... I think we cannot use Cells(II,3) straightaway for comparing values. Using Cells(II,3).value = "" might be the reason for the error. "steve" wrote in message ... Ray, If you have a lot of rows, than Dim II as Long (not as Integer). -- sb "Ray Batig" wrote in message nk.net... I am having some problems getting this code to work like it has in the past. I am sure that there is an easier way to accomplish this task, however, I must be missing something. To give you the appropriate background, I have a workbook that contains numerous pages. I have built a macro which creates a new workbook named NWB. Then it copies selected worksheets into the new workbook. The first part of the ode below works fine to eliminate the buttons found on worksheet named SRN. However, when I get to the line with the . I get a type mismatch error, and everything stops. I am using the 'With' since I am activating this code from a worksheet in the original workbook. In simple terms, I just want to start at a known cell in workbook NWB and worksheet SRN and find out the bottom row of the table with an entry in column 3. Then I copy it to the clipboard and past back the cell contents as values. This is being done since I don't copy the worksheet with the data that these cells reference into the new workbook. Please give me some guidance on how to accomplish this task. Thanks! ' remove command buttons from Sheet Dim i As Integer, N As Integer Dim II As Integer With NWB.Worksheets(SRN) 'get the number of OLEObjects on the sheet N = .OLEObjects.Count For i = N To 1 Step -1 'check the type of object. If a command button, delete it If LCase(TypeName(.OLEObjects(i).Object)) = _ "commandbutton" Then .OLEObjects(i).Delete Next ' convert the MM and MN cells to values II = 20 ' start on row 20 Do Until .Cells(II, 3) = "" ' find end of column II = II + 1 Loop II = II - 1 ' get back to last row .Range(.Cells(20, 3), .Cells(II, 4)).Copy ' get the range onto the clipboard .Range(.Cells(20, 3), .Cells(II, 4)).PasteSpecial (xlValues) End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|