PDA

View Full Version : VBA: referencing cell in one worksheet and using it as range start for another sheet



ArenA777
08-15-2017, 04:28 PM
I want to select a range in worksheet "2008" with the start of the range being a cell in Worksheet "State. Since the cell in Worksheet B will change, I want to format my code to take the change into consideration.

Sheets("State").Select
Dim county As Range
Set county = Sheets("State").Range("A2").Copy

Sheets("2008").Select
Dim data As Range
Set data = Cells.Find(What:=county)
Range(data, data.End(xlDown)).Copy Sheets("State").Range("D2")

I appreciate the help.

mana
08-15-2017, 09:04 PM
>Dim county As Range
>Set county = Sheets("State").Range("A2").Copy


Dim county As String
county = Sheets("State").Range("A2").Value

ArenA777
08-15-2017, 10:39 PM
Thanks, that worked great. I know want it to copy the cells to the right of the data.offset(1,0)

Sheets("2008").Select
Dim data As Range
Set data = Cells.Find(What:=county)
Range(data.offset(1,0), data.End(xlDown)).Copy Sheets("State").Range("D2")


I want the beginning of the range to be the entire row after the data.offset(1,0). If the data.offset(1,0) is cell A2 (for example), I want it to select it from A2:A100.

mana
08-15-2017, 10:50 PM
Can you post your workbook?

ArenA777
08-15-2017, 11:20 PM
Can you post your workbook?

This macro is for a workbook that changes. What inwant is to select several columns starting with the offsetted column and ending with the column before the empty column. I want to selct those columns down to the row until the row right before the empty row

mana
08-16-2017, 12:04 AM
if data is found in B1,
you want to copy B2-D6 ?


 ABCD
1□□□□
2□■■■
3□■■■
4□■■■
5□■■■
6□■■■
7

mana
08-16-2017, 01:32 AM
This is my guess.


Option Explicit

Sub test()
Dim wsS As Worksheet
Dim wsD As Worksheet
Dim r As Range
Dim m

Set wsS = Sheets("2008")
Set wsD = Sheets("State")

Set r = wsS.Range("A1").CurrentRegion
m = Application.Match(wsD.Range("A2"), r.Rows(1), 0)
If IsError(m) Then Exit Sub
Set r = Intersect(r, r.Offset(1, m - 1))

r.Copy wsD.Range("D2")

End Sub