Forum Discussion
Selecting an Excel Range in VBA
I have attached the spreadsheet.
Go to the "Weekly" sheet and click on the "Import Print Data" command button to see the error occur
If I comment the line of code that causes the error and un-comment the line of code above the error line, the code works as wanted.
Sheets("Groups").Range(Cells(2, 2), Cells(34, 12)).Select ' this is the line of code that gets the error.
I need to be able to select a range using numeric rows and columns instead of numeric rows and alpha columns.
I have placed comment lines above and below the code with rows of asterisks
WallisMcMath as I suspected the "cells" references are referring back to the Weekly sheet instead of the Groups sheet. Besides, your technique of using .select to continuously change focus throughout your code is not a good idea for a number of reasons. after a brief look at your code you should declare a variable for those sheets (e.g. Dim wstGroups as worksheet) and then set wstGroups = Sheets("Groups") and then just use that variable to find areas and such.
Also as previously mentioned, you can just take action of those areas instead of doing the .select
I this example code I am using the "with" statement to make life easier and prefix the "cells" with a period to say use the "Groups" sheet instead of whatever sheet it wants (and it worked for me but it hit another error later in your code):
.Range(.Cells(2, 2), .Cells(34, 12)).Copy
End With
Sheets("Weekly").Range("A1").Select 'xx
ActiveSheet.Pictures.Paste(Link:=True).Select
'************************************************************************************************************
I am obviously still confused.
I tried three ways to make the selection based on what I thought you were saying.
Example 1
I am in the "Weekly" worksheet
Dim wsGroups As Worksheet
Set wsGroups = Sheets("Groups")
wsGroups.Range(Cells(2, 2), Cells(34, 12)).Copy 'Get error message on this line of code
Range("A1").Select 'xx
ActiveSheet.Pictures.Paste(Link:=True).SelectExample 2
I am in the "Weekly" worksheet
Dim wsGroups As Worksheet
Set wsGroups = Sheets("Groups")With wsGroups
.Range(Cells(2, 2), Cells(34, 12)).Copy 'Get error message on this line of code
End WithExample 3
I am in the "Weekly" worksheet
Dim wsGroups As Worksheet
Set wsGroups = Sheets("Groups")With Sheets("Groups")
.Range(Cells(2, 2), Cells(34, 12)).Copy 'Get error message on this line of code
End With
Range("A1").Select
ActiveSheet.Pictures.Paste(Link:=True).SelectDo you have the name of a good book or other reference on using vba like you are showing me
WallisMcMath I don't have time right now to test and send but the problem before and therefore almost certainly still have in your 3 examples is that "cells" inside the () of the range are trying to refer to a different sheet. see modified examples (changes in red) that I think will fix this problem:
Example 1
I am in the "Weekly" worksheet
Dim wsGroups As Worksheet
Set wsGroups = Sheets("Groups")
wsGroups.Range(wsGroups.Cells(2, 2), wsGroups.Cells(34, 12)).Copy 'Get error message on this line of code
Range("A1").Select 'xx
ActiveSheet.Pictures.Paste(Link:=True).SelectExample 2
I am in the "Weekly" worksheet
Dim wsGroups As Worksheet
Set wsGroups = Sheets("Groups")With wsGroups
.Range(.Cells(2, 2), .Cells(34, 12)).Copy 'Get error message on this line of code
End WithExample 3
I am in the "Weekly" worksheet
Dim wsGroups As Worksheet
Set wsGroups = Sheets("Groups")With Sheets("Groups")
.Range(.Cells(2, 2), .Cells(34, 12)).Copy 'Get error message on this line of code
End With
Range("A1").Select
ActiveSheet.Pictures.Paste(Link:=True).Select
Thanks Much!
I will convert my code as you suggested. Thanks again for your help. I still have a lot to learn.