# How to write a macro that copy values?

#### andy-s

##### Board Regular
My workbook contains to sheets name Sheet1 and Sheet2.

Cell A1 inn Sheet1 refers to a value that I want to be copied to the next empty/available cell in column B in Sheet2 from a top/down perspective, but only if the corresponding column A contains a value.

Could anyone help me with a macro that can do this?

An example:

Sheet1
A1=100

Sheet2
A1=AA and B1=99
A2=BB
A3=CC

The macro should copy the value in Sheet1!A1 to cell Sheet2!B2 and Sheet2!B3, but not Sheet2!B4 since Sheet2!A4 contains no value.

Sincerely
Andy

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### hayati

##### Board Regular
Andy,

Can you explain a bit more. What is the logic of copying Sheet1!A1 into Sheet2!B3.

I understand copying it to Sheet2!B2 but don't understand the logic of where it should stop?

Regards,
Matt

#### andy-s

##### Board Regular
Thx for writing.

After the macro have copied the value in Sheet1!A1 to the next available cell inn column B in Sheet2, the value in Sheet!A1 will change.

Think of Sheet1!A1 like a =RND() formula.

--

The macro I need help with is a small part in a bigger macro I'm writing. The final macro would then be copied into different workbooks where Sheet2 can contains value from row 10 to ca 10,000.

#### Nimrod

##### MrExcel MVP
Code:
``````Public Sub TransferAndAutoFill()
With Sheets("Sheet2")
NxRow = .Cells(65536, 2).End(xlUp).Row + 1
BotARow = .Cells(65536, 1).End(xlUp).Row

'Transfer info
.Cells(NxRow, 2).Value = Sheets("Sheet1").Range("A1").Value

'AutoFill info
.Cells(NxRow, 2).AutoFill Destination:=.Range("B" & NxRow & ":B" & BotARow)

End With
End Sub``````

#### Andywiz

##### Board Regular
This is rudimentary but it works - and doesn't continue to add values in column B if there is no corresponding value in column A.

Code:
``````Sub Move_data()

x = Sheets("sheet1").Range("a1")
y = Sheets("sheet2").Range("B65536").End(xlUp).Row + 1
z = Sheets("Sheet2").Cells(y, 1)

If z = "" Then Exit Sub Else Sheets("Sheet2").Cells(y, 2) = x

End Sub``````

Hope it helps some,
Andywiz

#### andy-s

##### Board Regular
Thx a lot - I've one last question.

Cell G3 contains a number. Thats the number I want to run a macro named Copy.

Sincerely,
Anyd-s

#### Nimrod

##### MrExcel MVP
Who ?? AnyWiz or Nimrod ??

Replies
10
Views
672
Replies
0
Views
135
Replies
4
Views
446
Replies
2
Views
222
Replies
0
Views
540

1,195,749
Messages
6,011,436
Members
441,614
Latest member
TiaGtz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back