VBA CODE -- Find and insert

[MichaelJ300]

Hello,

I’m not sure how to explain what I need here…
I need to identify the first blank space then copy and paste information in that area. Then find the second space and copy and paste information in this area.

Step 1: identify the first empty row and copy and paste from a different tab named “Control” cell A21.
Step 2: Identify the second empty row copy and paste from a different tab named “Control” cell A22.

Thanks in advance…

 

[tsas321]

Hello,

I’m not sure how to explain what I need here…
I need to identify the first blank space then copy and paste information in that area. Then find the second space and copy and paste information in this area.

Step 1: identify the first empty row and copy and paste from a different tab named “Control” cell A21.
Step 2: Identify the second empty row copy and paste from a different tab named “Control” cell A22.

Thanks in advance…

Hi Michael,
Try this code it, it looks in column A for the first empty cell and copies cell A21 into it, then looks for the second empty cell and copies Cell A22 into it.

Sub EmptyRow()    
    With Worksheets(1)
        .Activate
        .Cells(1, 1).End(xlDown).Offset(1, 0) = Worksheets("Control").Cells(21, 1)
        .Cells(1, 1).End(xlDown).Offset(1, 0) = Worksheets("Control").Cells(22, 1)
    End With
        


End Sub

 

[MichaelJ300]

This works!!! Is there a way that I can take the format from the Worksheets("Control").Cells(21, 1)?

 

[tsas321]

I suppose the easiest in my mind would be to copy and paste.

Sub EmptyRow()    Worksheets("Control").Cells(21, 1).Copy
    Worksheets(1).Cells(1, 1).End(xlDown).Offset(1, 0).PasteSpecial
    
    Worksheets("Control").Cells(22, 1).Copy
    Worksheets(1).Cells(1, 1).End(xlDown).Offset(1, 0).PasteSpecial
    
    Worksheets(1).Activate
End Sub

 

[MichaelJ300]

nope didn't work

I tried this still nothing just a little modification.

Sheets("TA TOTALS").Select
Worksheets("Control").Cells(21, 1).Copy
Worksheets(1).Cells(1, 1).End(xlDown).Offset(1, 0).PasteSpecial
Worksheets("Control").Cells(22, 1).Copy
Worksheets(1).Cells(1, 1).End(xlDown).Offset(1, 0).PasteSpecial

 

[tsas321]

Did it not work in terms of copying the format, or did it not even copy the values? When I run it, it copies text to the sheet and keeps the color of the text. What formatting are you trying to keep?

 

[MichaelJ300]

It wouldn't copy anything. I'm trying to copy a merge cell from Worksheets("Control").Cells(21, 1).Copy. It does have color and boldness. The first code worked great other than the formatting. Not sure what I'm doing wrong here. I tried a couple different varaition but the same result.

 

[MichaelJ300]

p

 

[MichaelJ300]

Insted of taking 1 cell is there code to take the entire row from Worksheets("Control"). 21?

 

[tsas321]

Yes, Worksheets("Control").Rows(21).Copy. The issue with copying a merge cell is you need to paste it into an equal size range of cells I believe. Copying the row should work though.