Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range("A1,F96").Select - What's wrong with that?
It's been years since I tried to do anything with excel that involved
any more than a simple macro, so this project is much harder that it would've been back then. I'm trying to copy the contents of a range of cells from one worksheet to another. My code looks like this: Private Sub Jenn_Qte() Application.ScreenUpdating = False ActiveSheet.Unprotect Password:="1234" 'unprotects the destination worksheet Sheets("Items, Cat").Select 'select source worksheet ActiveSheet.Unprotect Password:="1234" 'unprotect source worksheet Selection.AutoFilter Field:=1, Criteria1:="<" 'filter data, hide all blanks Range("A1,F96").Select 'THIS IS WHERE IT STOPS - supposed to select the filtered data Selection.Copy 'copy data Sheets("Quote").Select 'select destination worksheet Range("A14").Select 'select cell where data should be pasted Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Sheets("Items, Cat").Select 'back to the source worksheet Selection.AutoFilter Field:=1 'un-filter it Range("C3").Select 'cursor in this cell ActiveSheet.Protect Password:="1234" 'protect the worksheet Sheets("Quote Body").Select 'to the destination sheet again Range("F6").Select 'cursor in F6 please ActiveSheet.Protect Password:="1234" 'lock it back up :) Application.ScreenUpdating = True End Sub Is there a glaring problem there that I just don't see? Does anyone have any suggestions? Anything will be very appreciated. Thanks in advance, Jenn |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range("A1,F96").Select - What's wrong with that?
I may be wrong but shouldn't each value in the range be in quotes:
Range("A1", "F96") "Jennifer" wrote: It's been years since I tried to do anything with excel that involved any more than a simple macro, so this project is much harder that it would've been back then. I'm trying to copy the contents of a range of cells from one worksheet to another. My code looks like this: Private Sub Jenn_Qte() Application.ScreenUpdating = False ActiveSheet.Unprotect Password:="1234" 'unprotects the destination worksheet Sheets("Items, Cat").Select 'select source worksheet ActiveSheet.Unprotect Password:="1234" 'unprotect source worksheet Selection.AutoFilter Field:=1, Criteria1:="<" 'filter data, hide all blanks Range("A1,F96").Select 'THIS IS WHERE IT STOPS - supposed to select the filtered data Selection.Copy 'copy data Sheets("Quote").Select 'select destination worksheet Range("A14").Select 'select cell where data should be pasted Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Sheets("Items, Cat").Select 'back to the source worksheet Selection.AutoFilter Field:=1 'un-filter it Range("C3").Select 'cursor in this cell ActiveSheet.Protect Password:="1234" 'protect the worksheet Sheets("Quote Body").Select 'to the destination sheet again Range("F6").Select 'cursor in F6 please ActiveSheet.Protect Password:="1234" 'lock it back up :) Application.ScreenUpdating = True End Sub Is there a glaring problem there that I just don't see? Does anyone have any suggestions? Anything will be very appreciated. Thanks in advance, Jenn |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range("A1,F96").Select - What's wrong with that?
If you want cells A1 and F96 then Range("A1", "F96")
If you want cells A1 thru F96 then Range("A1:F96") "Jennifer" wrote: It's been years since I tried to do anything with excel that involved any more than a simple macro, so this project is much harder that it would've been back then. I'm trying to copy the contents of a range of cells from one worksheet to another. My code looks like this: Private Sub Jenn_Qte() Application.ScreenUpdating = False ActiveSheet.Unprotect Password:="1234" 'unprotects the destination worksheet Sheets("Items, Cat").Select 'select source worksheet ActiveSheet.Unprotect Password:="1234" 'unprotect source worksheet Selection.AutoFilter Field:=1, Criteria1:="<" 'filter data, hide all blanks Range("A1,F96").Select 'THIS IS WHERE IT STOPS - supposed to select the filtered data Selection.Copy 'copy data Sheets("Quote").Select 'select destination worksheet Range("A14").Select 'select cell where data should be pasted Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Sheets("Items, Cat").Select 'back to the source worksheet Selection.AutoFilter Field:=1 'un-filter it Range("C3").Select 'cursor in this cell ActiveSheet.Protect Password:="1234" 'protect the worksheet Sheets("Quote Body").Select 'to the destination sheet again Range("F6").Select 'cursor in F6 please ActiveSheet.Protect Password:="1234" 'lock it back up :) Application.ScreenUpdating = True End Sub Is there a glaring problem there that I just don't see? Does anyone have any suggestions? Anything will be very appreciated. Thanks in advance, Jenn |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range("A1,F96").Select - What's wrong with that?
I think both your examples will be A1:F96 (all 576 cells).
Range("a1,f96") will be just two cells. JLGWhiz wrote: If you want cells A1 and F96 then Range("A1", "F96") If you want cells A1 thru F96 then Range("A1:F96") "Jennifer" wrote: It's been years since I tried to do anything with excel that involved any more than a simple macro, so this project is much harder that it would've been back then. I'm trying to copy the contents of a range of cells from one worksheet to another. My code looks like this: Private Sub Jenn_Qte() Application.ScreenUpdating = False ActiveSheet.Unprotect Password:="1234" 'unprotects the destination worksheet Sheets("Items, Cat").Select 'select source worksheet ActiveSheet.Unprotect Password:="1234" 'unprotect source worksheet Selection.AutoFilter Field:=1, Criteria1:="<" 'filter data, hide all blanks Range("A1,F96").Select 'THIS IS WHERE IT STOPS - supposed to select the filtered data Selection.Copy 'copy data Sheets("Quote").Select 'select destination worksheet Range("A14").Select 'select cell where data should be pasted Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Sheets("Items, Cat").Select 'back to the source worksheet Selection.AutoFilter Field:=1 'un-filter it Range("C3").Select 'cursor in this cell ActiveSheet.Protect Password:="1234" 'protect the worksheet Sheets("Quote Body").Select 'to the destination sheet again Range("F6").Select 'cursor in F6 please ActiveSheet.Protect Password:="1234" 'lock it back up :) Application.ScreenUpdating = True End Sub Is there a glaring problem there that I just don't see? Does anyone have any suggestions? Anything will be very appreciated. Thanks in advance, Jenn -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range("A1,F96").Select - What's wrong with that?
Yeah, sometimes I have these Senior moments.:-(
"Dave Peterson" wrote: I think both your examples will be A1:F96 (all 576 cells). Range("a1,f96") will be just two cells. JLGWhiz wrote: If you want cells A1 and F96 then Range("A1", "F96") If you want cells A1 thru F96 then Range("A1:F96") "Jennifer" wrote: It's been years since I tried to do anything with excel that involved any more than a simple macro, so this project is much harder that it would've been back then. I'm trying to copy the contents of a range of cells from one worksheet to another. My code looks like this: Private Sub Jenn_Qte() Application.ScreenUpdating = False ActiveSheet.Unprotect Password:="1234" 'unprotects the destination worksheet Sheets("Items, Cat").Select 'select source worksheet ActiveSheet.Unprotect Password:="1234" 'unprotect source worksheet Selection.AutoFilter Field:=1, Criteria1:="<" 'filter data, hide all blanks Range("A1,F96").Select 'THIS IS WHERE IT STOPS - supposed to select the filtered data Selection.Copy 'copy data Sheets("Quote").Select 'select destination worksheet Range("A14").Select 'select cell where data should be pasted Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Sheets("Items, Cat").Select 'back to the source worksheet Selection.AutoFilter Field:=1 'un-filter it Range("C3").Select 'cursor in this cell ActiveSheet.Protect Password:="1234" 'protect the worksheet Sheets("Quote Body").Select 'to the destination sheet again Range("F6").Select 'cursor in F6 please ActiveSheet.Protect Password:="1234" 'lock it back up :) Application.ScreenUpdating = True End Sub Is there a glaring problem there that I just don't see? Does anyone have any suggestions? Anything will be very appreciated. Thanks in advance, Jenn -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range("A1,F96").Select - What's wrong with that?
On Oct 1, 9:07 pm, JLGWhiz wrote:
Yeah, sometimes I have these Senior moments.:-( "Dave Peterson" wrote: I think both your examples will be A1:F96 (all 576 cells). Range("a1,f96") will be just two cells. JLGWhiz wrote: If you want cells A1 and F96 then Range("A1", "F96") If you want cells A1 thru F96 then Range("A1:F96") "Jennifer" wrote: It's been years since I tried to do anything with excel that involved any more than a simple macro, so this project is much harder that it would've been back then. I'm trying to copy the contents of a range of cells from one worksheet to another. My code looks like this: Private Sub Jenn_Qte() Application.ScreenUpdating = False ActiveSheet.Unprotect Password:="1234" 'unprotects the destination worksheet Sheets("Items, Cat").Select 'select source worksheet ActiveSheet.Unprotect Password:="1234" 'unprotect source worksheet Selection.AutoFilter Field:=1, Criteria1:="<" 'filter data, hide all blanks Range("A1,F96").Select 'THIS IS WHERE IT STOPS - supposed to select the filtered data Selection.Copy 'copy data Sheets("Quote").Select 'select destination worksheet Range("A14").Select 'select cell where data should be pasted Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Sheets("Items, Cat").Select 'back to the source worksheet Selection.AutoFilter Field:=1 'un-filter it Range("C3").Select 'cursor in this cell ActiveSheet.Protect Password:="1234" 'protect the worksheet Sheets("Quote Body").Select 'to the destination sheet again Range("F6").Select 'cursor in F6 please ActiveSheet.Protect Password:="1234" 'lock it back up :) Application.ScreenUpdating = True End Sub Is there a glaring problem there that I just don't see? Does anyone have any suggestions? Anything will be very appreciated. Thanks in advance, Jenn -- Dave Peterson Thank you all for your suggestions. I did change the statement to Range("A1:F96"). Select but it didn't help. I'm still getting the 'Run-Time: Application-defined or object-defined error'. Still stumped, I tried to accomplish the same task another way, so now my code looks like this: Sheets("Items, Cat").Select ActiveSheet.Unprotect Password:="1234" Selection.AutoFilter Field:=1, Criteria1:="<" Range("B3").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Now I get the 'Run-time: Selection method of Range class failed' error. Can you help again, please? Thanks in advance (again) - Jenn |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range("A1,F96").Select - What's wrong with that?
I looked at your original post and got kind of confused. You refer to the
activesheet. Is that one of the sheets that you refer to by name? And sometimes, your code used Sheets("Quote")... and sometimes Sheets("Quote Body")... Is that a typo in your post or code or do you really have different worksheets. Anyway, maybe you can do the work without selecting anything. This compiled, but I didn't test it. It also expects that the autofilter is already applied to your data (starting in column A). Option Explicit Sub Jenn_Qte2() Dim ItemWks As Worksheet Dim QuoteWks As Worksheet Dim RngToCopy As Range Dim DestCell As Range Set ItemWks = Worksheets("Items, Cat") Set QuoteWks = Worksheets("Quote") Application.ScreenUpdating = False With ItemWks .Unprotect Password:="1234" 'show all the data If .FilterMode Then .ShowAllData End If 'hide the blanks .AutoFilter.Range.Columns(1).AutoFilter Field:=1, Criteria1:="<" If .AutoFilter.Range.Columns(1).Cells _ .SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then 'only the headers are shown 'what should happen Else With .AutoFilter.Range 'header row included -- A:F (6 columns in the .resize portion) Set RngToCopy = .Resize(.Rows.Count, 6) _ .Cells.SpecialCells(xlCellTypeVisible) End With Set DestCell = QuoteWks.Range("A14") QuoteWks.Unprotect Password:="1234" RngToCopy.Copy DestCell.PasteSpecial Paste:=xlPasteAll, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False QuoteWks.Protect Password:="1234" End If .Protect Password:="1234" End With Application.ScreenUpdating = True End Sub ================= And just a note about your problem. If your code is behind a worksheet, it could fail. The unqualified range: Range("B3").Select will refer to the worksheet that owns the code--not the activesheet. This is different than the behavior you see when your code is in a General module. Jennifer wrote: <<snipped Thank you all for your suggestions. I did change the statement to Range("A1:F96"). Select but it didn't help. I'm still getting the 'Run-Time: Application-defined or object-defined error'. Still stumped, I tried to accomplish the same task another way, so now my code looks like this: Sheets("Items, Cat").Select ActiveSheet.Unprotect Password:="1234" Selection.AutoFilter Field:=1, Criteria1:="<" Range("B3").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Now I get the 'Run-time: Selection method of Range class failed' error. Can you help again, please? Thanks in advance (again) - Jenn -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range("A1,F96").Select - What's wrong with that?
Dave:
Wow! I deleted everything I had and pasted in your code and - ta da - it works! Thank you so much! I didn't know about the unqualified range - "Range("B3").Select will refer to the worksheet that owns the code--not the activesheet." That's important info that is now permanently burned into my memory. Thanks! I really appreciate your help! Jennifer |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range("A1,F96").Select - What's wrong with that?
Glad you got it working!
Jennifer wrote: Dave: Wow! I deleted everything I had and pasted in your code and - ta da - it works! Thank you so much! I didn't know about the unqualified range - "Range("B3").Select will refer to the worksheet that owns the code--not the activesheet." That's important info that is now permanently burned into my memory. Thanks! I really appreciate your help! Jennifer -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range("Reset Sheet!F13").Select Makes VBA Code Fail | Excel Programming | |||
How to change "automax" to "autosum"? (probablyl wrong terminology) | Excel Discussion (Misc queries) | |||
use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming | |||
How do I select a range of cells without doing Range("a3", "f3").. | Excel Programming | |||
Using "Cells" to write "Range("A:A,H:H").Select" | Excel Programming |