VBA for writing code to copy data from one cell to a cell in another workbook

[gyraceguy]

As the title explains, I'm looking for code that would take data from a cell in workbook1 sheet1 and, based on a change macro I already created, populate a cell in another workbook. Specifically I need this to happen when a certain if statement is executed, but I have code for that. I'm going to create code that creates to variable one to assign the new column and one to assign the new row for the destination cell. How do I write code to designate where the information is headed?

 

[ExcelGuyDude]

Within VBA Code, there is a hierarchy that you need to use... Application (Generally Excel) > Workbook > Worksheet > Range > Attribute(s)

You only have to specify as far as you need to. e.g. If you are in the workbook that you want to run the code in, you don't need to specify the application or workbook. If you are in the worksheet that you want to run the code in, you don't need to specify the worksheet in the code.

In this instance, you want to specify the workbook, and the easiest way to do that is by using variables. Do something like this:

Dim ThisWb As Workbook, OtherWb As Workbook

Set ThisWb = ThisWorkbook 'this will set the variable to the workbook running the code.
Set OtherWb = Workbooks("Other Workbook.xlsm")

    'Assuming we want information from "Other Workbook" moved to "This workbook"
    ThisWb.Sheets("Sheet 1").Range("A1") = OtherWb.Sheets("Sheet 1").Range("G4").Value

 

[gyraceguy]

Within VBA Code, there is a hierarchy that you need to use... Application (Generally Excel) > Workbook > Worksheet > Range > Attribute(s)

You only have to specify as far as you need to. e.g. If you are in the workbook that you want to run the code in, you don't need to specify the application or workbook. If you are in the worksheet that you want to run the code in, you don't need to specify the worksheet in the code.

In this instance, you want to specify the workbook, and the easiest way to do that is by using variables. Do something like this:

Dim ThisWb As Workbook, OtherWb As Workbook

Set ThisWb = ThisWorkbook 'this will set the variable to the workbook running the code.
Set OtherWb = Workbooks("Other Workbook.xlsm")

    'Assuming we want information from "Other Workbook" moved to "This workbook"
    ThisWb.Sheets("Sheet 1").Range("A1") = OtherWb.Sheets("Sheet 1").Range("G4").Value

I actually need to move data from "This workbook" to "other workbook" but thanks so much this really helps!! Now is there any way to make the destination cell a variable that changes based on the location of the source cell? I can try working on it for now, but you know offhand, it'd be greatly appreciated.

 

[ExcelGuyDude]

It would be something like this:

    Dim ThisRng As String

    ThisRng = ActiveCell.Address

    OtherWb.Sheets("Sheet1").Range(ThisRng) = ThisWb.Sheets("Sheet1").Range(ThisRng).Value

You can do the same thing with the sheet name as well.