Copy rows containing data & paste in first blank row

[redhots4]

(1.) What is the VB code to select only rows containing data? Start in row B and end in Row X where X is the last row containing data.

(2.) Once item 1 above is satisfied, I need to go to another sheet ("Data"), find the first blank row, below all existing data and paste the data copied in step 1.

Thanks in advance. I'm ready to pull my hair out over here.

 

[Ali Kırksekiz]

(1.) What is the VB code to select only rows containing data? Start in row B and end in Row X where X is the last row containing data.

Sub columnselect()
Range("B:B").SpecialCells(xlCellTypeConstants).Select
End Sub

(another sheet ("Data"), find the first blank row

For A column

Sub Find()
Sheets("Data").Select
Selection.SpecialCells(xlBlanks).Areas(1).Cells(1).Select
End Sub

 

[redhots4]

I'm not seeing that this code is doing what I want. Can you please re-read my post and make sure you're giving me an answer to the right question?

 

[redhots4]

Can anyone else help me with this issue? Thanks in advance!!

 

[Ali Kırksekiz]

Try this

You must copy these codes in Sheet1 code page

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Worksheets("Sheet1").Cells(65536, 2).End(xlUp).Address Then
    Worksheets("Data").Cells(65536, 2).End(xlUp).Offset(1, 0).Value = Target.Value
    Worksheets("Data").Cells(65536, 2).End(xlUp).Offset(1, 1).Value = Target.Offset(0, 1).Value
End If
End Sub

 

[erik.van.geit]

I'm not seeing that this code is doing what I want. Can you please re-read my post and make sure you're giving me an answer to the right question?

Hi,

perhaps you could rephrase your question
to my sense it can be interpreted in different ways

a little example would help

kind regards,
Erik

 

[redhots4]

More information on copy / paste question

My situation, specifically, is this:

1. Info is stored on sheet "Paste" in 5 columns, A:E
2. Row 1 contains headings in "Paste" A1:E1
3. Need to copy data contained in "Paste" Columns A:E, starting in row 2 (A2) and going down to the final row containing data (Might be row 82, might be row 2475, depending upon the amount of data for the report run.)
4. Once copied, data needs to be pasted on "Data" sheet starting in the first blank row below the data already on that page. Find the first blank row, go to column A and paste.

I hope this is enough information.

Thanks!

 

[Norie]

redhots4

Try this.

With Sheets("Paste")
    .Range("A2:E" & .Range("A" & Rows.Count).End(xlUp).Row).Copy Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
End With

 

[redhots4]

Problem

Code is choking on the second '.Range' below. (?)

With Sheets("Paste")
.Range("A2:E" & .Range ("A" & Rows.Count).End(xlUp).Row).Copy Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
End With

 

[Norie]

Well it works for me.

You do have data in column A?

By the way, how exactly is it 'choking'?