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.
>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.
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
if data is found in B1,
you want to copy B2-D6 ?
ABCD
1□□□□
2□■■■
3□■■■
4□■■■
5□■■■
6□■■■
7
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.