How to write a macro that copy values?

[andy-s]

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

 

[hayati]

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]

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]

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]

ADVERTISEMENT

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.

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]

Thx a lot - I've one last question.

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

Could you please help me with writing this seccond macro as well?

Sincerely,
Anyd-s

 

[Nimrod]

Who ?? AnyWiz or Nimrod ??