Hi Guys,
Not much clue what I'm talking about here being brand new to VBA and hope I'm following the forum rules appropriately.
I've set up a test scenario where I'm copying data from one workbook and pasting it to a particular range on another workbook. I can get it to work fine, but I'm trying to reduce the number of lines of code I have to maintain should something change in the destination sheet, using variables.
So the format of all sheets on "wbto" are the same, I was hoping I could set the range regardless of sheet to sit as a variable and then reference that in my destination.
Any hints and tips? Like I say the code works if I make "FindRng" on action 1 the same as action 2 and 3, I'm just badly attempting to make it more manageable in the future.
TIA
Not much clue what I'm talking about here being brand new to VBA and hope I'm following the forum rules appropriately.
I've set up a test scenario where I'm copying data from one workbook and pasting it to a particular range on another workbook. I can get it to work fine, but I'm trying to reduce the number of lines of code I have to maintain should something change in the destination sheet, using variables.
So the format of all sheets on "wbto" are the same, I was hoping I could set the range regardless of sheet to sit as a variable and then reference that in my destination.
Any hints and tips? Like I say the code works if I make "FindRng" on action 1 the same as action 2 and 3, I'm just badly attempting to make it more manageable in the future.
TIA
VBA Code:
Sub COPY()
Dim Rng As Range
Dim FindRng As Range
Dim wbfrom As Workbook
Dim wbto As Workbook
Set wbfrom = Workbooks("Test from.xlsx")
Set wbto = Workbooks("Test to.xlsx")
Set Rng = wbfrom.Worksheets("Sheet1").Range("d3")
Set FindRng = Range("C6:G6")
wbfrom.Worksheets("Sheet1").Range("E7:E10").COPY
wbto.Worksheets("Sheet1").FindRng.Find(Rng).Offset(5, 0).PasteSpecial Paste:=xlPasteValues
wbfrom.Worksheets("Sheet1").Range("E7:E10").COPY
wbto.Worksheets("Sheet2").Range("C6:G6").Find(Rng).Offset(5, 0).PasteSpecial Paste:=xlPasteValues
wbfrom.Worksheets("Sheet1").Range("E7:E10").COPY
wbto.Worksheets("Sheet3").Range("C6:G6").Find(Rng).Offset(5, 0).PasteSpecial Paste:=xlPasteValues
End Sub