• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Repeat text/date on Pasted Sheet

chin

New Member
I have programmatically copy/paste Range ("A1:B4") from Sheet1 to be pasted

on Sheet2.

However, on Sheet2, I want A2:A4 to display the same date as in A1,meaning populating the cells in Column A, where there is a non-Empty cell in Column B.

Any suggestion on how to code it, making the transfer to Sheet2 more complete with dates for each item on Range("B2-B4") appearing on Sheet2?


COLUMN A COLUMN B

12-10-17 (CellA1) Apple(CellB1)

Orange(CellB2)

Mango(CellB3)

Papaya (CellB4)
 
Hi ,

A simple VBA script will be :
Code:
Public Sub CopyPaste()
          With ThisWorkbook.Worksheets("Sheet2")
                ThisWorkbook.Worksheets("Sheet1").Range("A1:B4").Copy .Range("A1")
                .Range("A1:A4").FillDown
          End With
End Sub
Narayan
 
In A2 paste :

Code:
=IF(B2>0,A1,"")

Then drag down Col A as far as required.

Hi, Thomas!
This is not what I am looking for.

I need a code to automatically update and fill with the text/dates without dragging manually.
Thanks for your interest.
 
Hi ,

A simple VBA script will be :
Code:
Public Sub CopyPaste()
          With ThisWorkbook.Worksheets("Sheet2")
                ThisWorkbook.Worksheets("Sheet1").Range("A1:B4").Copy .Range("A1")
                .Range("A1:A4").FillDown
          End With
End Sub
Narayan

Thanks again , Narayan!
The code works only when in sheet2 the Filled-down cells are identified and known.
What if:-

Sheet1 is added another set of new range items and then copied and pasted to Sheet2, and that should be displayed after A4 and B4 line. For example:

Sheet2 A5>>12/01/2017 B5 >> Banana
A6 empty B6 >> Peach
A7 empty B7 >> Apricot

How to fill A6,A7 programatically without pre-knowing only 2 cells are to be filled down, as this depends on the existence of non-Empty cells in Column B?
 
Hi ,

See if this works :
Code:
Public Sub CopyPaste()
          With ThisWorkbook.Worksheets("Sheet2")
              lastrow2 = .Range("B" & Rows.Count).End(xlUp).Row
              lastrow1 = ThisWorkbook.Worksheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
            
              ThisWorkbook.Worksheets("Sheet1").Range("A1:B" & lastrow1).Copy .Range("A" & lastrow2+1)
              lastrow1 = .Range("B" & Rows.Count).End(xlUp).Row
                .Range("A" & lastrow2 & ":A" & lastrow1).FillDown
          End With
End Sub
If this does not work , upload your workbook with real-life data.

Narayan
 
Last edited:
OK ... try this :

Code:
Option Explicit

Sub CopyPaste()
Dim lastrow2 As Variant
Dim lastrow1 As Variant

Application.ScreenUpdating = False
          With ThisWorkbook.Worksheets("Sheet2")
              lastrow2 = .Range("B" & Rows.Count).End(xlUp).Row
              lastrow1 = ThisWorkbook.Worksheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
           
              ThisWorkbook.Worksheets("Sheet1").Range("A1:B" & lastrow1).Copy .Range("A" & lastrow2)
              lastrow1 = .Range("B" & Rows.Count).End(xlUp).Row
               
          End With
         
Dim rng As Range
Dim i As Long
Dim cell As Variant

    'Set the range in column A you want to loop through
    Set rng = Range("B2:B100")
    For Each cell In rng
        'test if cell is empty
        If cell.Value <> "" Then
            'write to adjacent cell
            cell.Offset(0, -1).Value = Range("A1").Value
        End If
    Next
   
Application.ScreenUpdating = True
End Sub

If the above does not accomplish the goal, post your workbook with BEFORE AND AFTER EXAMPLES of what you expect to see.
 
Back
Top