Copy data into next empty cell in new sheet [Solved/Closed]

nikwak 1 Posts Wednesday July 23, 2008Registration date July 24, 2008 Last seen - Jul 24, 2008 at 10:23 AM - Latest reply: rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen
- Dec 1, 2012 at 10:21 AM
Hello,

I am trying to make a 2 sheet workbook which has the input details in Sheet1
A1 and this is changed weekly but the info in this cell should then be
inserted into the first blank cell in Column A in Sheet 2.

I have tried this code (below) but it doesn't seem to work and maybe I am
doing something wrong. Could someone let me know if this code should work
for this purpose or is there another code I could try?

Code used at present:

Sub copytonextsheet()
With Sheets("Sheet2")
n = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Cells(n, "A").Resize(, 4).Value = _
Cells(1, "A").Resize(, 4).Value
End With
End Sub


Thanks
Nick
See more 

15 replies

Best answer
56
Thank you
Sub CopyRowsWithNumbersInG()
Dim X As Long
Dim LastRow As Long
Dim Source As Worksheet
Dim Destination As Worksheet
Dim RowsWithNumbers As Range
Set Source = Worksheets("name")
Set Destination = Worksheets("name")
With Source
LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
For X = 2 To LastRow
If IsNumeric(.Cells(X, "E").Value) And .Cells(X, "E").Value <> "" Then
If RowsWithNumbers Is Nothing Then
Set RowsWithNumbers = .Cells(X, "E")
Else
Set RowsWithNumbers = Union(RowsWithNumbers, .Cells(X, "E"))
End If
End If
Next
If Not RowsWithNumbers Is Nothing Then
RowsWithNumbers.EntireRow.Copy Destination.Range("A4")
End If
End With
MsgBox "Data has been updated !!", vbExclamation + vbInformation, "Company Name"
End Sub

Thank you, Kioskea_1 56

Something to say? Add comment

CCM has helped 1789 users this month

say you had 2 text strings (constants) that you wanted to check on sheet1 and if found you wanted the entire row pasted to sheet2 bearing in mind that there are multiple instances of each constant how would one adapt the code to search and return only those specified rows?
My data is not numeric. What command could I use other than "IsNumeric" to find the text or date listed in my sheet?
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jul 6, 2010 at 06:58 PM
Mimi, what exactly is your question. If you just want to see if you have data or not, then just check it

if (Cells(1, "A") = "") then
msgbox "Cell A1 is blank"
else
msgbox "cell a1 is not blank"
end if
This is fantastic, thank you. I'm sorry to ask what is, I am sure, a very simple question, but I am really struggling. I would like to only paste specified columns of the data you have filtered. I presume instead of the .EntireRow. command I should specify the columns e.g .Columns("N:S").
However, this returns empty cells. I would be very grateful for your advice.
Many thanks
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Dec 1, 2012 at 10:21 AM
@ tomrossor : " would like to only paste specified columns of the data you have filtered. I presume instead of the .EntireRow. command I should specify the columns e.g .Columns("N:S"). "

Close. You have to pick range of cells, not columns
18
Thank you
My question is same as dBrowns...

I have a worksheet in which I copy data that fit a certain criteria onto another sheet. From the original sheet, the one where the original data is copied from, I then go back and do some filtering. After I do the filtering I want copy that info and put it on the sheet with the previous data I had copied. How do I put that new data into the next available row in the new sheet.

Please could someone help me with this. If DBrown you have got a reply could you temme.
Thanks!
14
Thank you
Any way to do this from 2 or more sheets, and consolidate them into one long list on a final sheet?
7
Thank you
Hello Nick,

I am not getiing the result using ur code, I am also facing the same problem, I am unable to copy data from 1 sheet to another sheet which has empty columns, I want to paste the values from one excel to another excel which has first column whihc is empty, please help me ASAP.

Regards,
Shinny dead.
6
Thank you
Replace "E" with your column and the destination and source names and the company name for the message box - this should work for what you need
1
Thank you
P.S "A4" is the row the new data is transfererred to
Hello,

I have aquestion you may be able to help with based on one of your post.

I have a worksheet in which I copy data that fit a certain criteria onto another sheet. From the original sheet, the one where the original data is copied from, I then go back and do some filtering. After I do the filtering I want copy and that info and put it on the sheet with the previous data I had copied. How do I put that new data into the next available row in the new sheet.

Thanks!
how to make codes for weekly report in vb.net... through inventory system.