Copy values from one cell to range of cells

[wpryan]

Hello, I have a situation where I need a value from one cell to be copied to another worksheet, and repeated in the same column the same number of times as appears a dynamic range in another part of the worksheet. Example:

Sheet 1 contains the dynamic range from range("D19:D" & LastRowWS1), and that I need copied to Sheet2.
Cell B2 contains a value that i need copied also to Sheet 2, in a column next to the range copied from Sheet1.

I'm stuck on how to do this. Any help?

 

[My Aswer Is This]

So you want to copy Sheet(1) Range("D19") to the last filled row in column D

And paste where on Sheet(2)

You said:

and that I need copied to Sheet2.

But you did not say where on sheet2

Then you said:

Cell B2 contains a value that i need copied also to Sheet 2, in a column next to the range copied from Sheet1.

But what does that mean??
You need to tell me exactly where you want it pasted Next to is not specific.​

​

 

[wpryan]

Thanks for your reply. Good questions, all of them. My challenge is that I have to take a form which is a very nice looking form for Auditors to look at, and somehow get the data in a useful format. Therefore, this is a small part of a bigger data transfer from worksheet to worksheet. Basically what I need to do is to:

1. Copy range in Sheet1, D19:D & last row to Sheet 2, U2
2. Count the rows which were copied
3. Take the value from sheet 1, range B2, and copy it to sheet2, range A2, for as many rows as was counted in step 1.

I will need to do the same for some other cells as well, but the number of rows to be copied will always be the same. I should be able to handle the rest...

I hope that's clear. Thanks again for your help.

 

[Joe4]

1. Copy range in Sheet1, D19:D & last row to Sheet 2, U2
2. Count the rows which were copied
3. Take the value from sheet 1, range B2, and copy it to sheet2, range A2, for as many rows as was counted in step 1.

Try this:

Sub MyMacro()

    Dim lastRow As Long
    Dim rowCount As Long
    
'   Find last row with data on sheet 1, column D
    lastRow = Sheets("Sheet1").Cells(Rows.Count, "D").End(xlUp).Row
    
'   Copy data to sheet 2
    Sheets("Sheet1").Range("D19:D" & lastRow).Copy Sheets("Sheet2").Range("U2")
    
'   Count the number of cells copied
    rowCount = lastRow - 19 + 1
    
'   Copy value from sheet 1 cell b2 to sheet 2
    Sheets("Sheet2").Range("A2:A" & rowCount + 1) = Sheets("Sheet1").Range("B2")
    
End Sub

 

[wpryan]

That works just like I wanted, thanks a lot!

 

[Joe4]

You are welcome.