Paste Values Into Next Blank Row

[davisshannon]

Hi,

I have a workbook with multiple sheets where I'm trying to copy rows where the value in column C > 0. I then want to paste these rows into a another sheet, called BoM. I'm ignoring the BoM sheet, along with a sheet called summary, and another sheet called Packet Storage.

I'm got my code partially working, but I'm having trouble getting the rows from each subsequent sheet to begin pasting on the next available row. I can loop through each sheet, but then the next sheet just starts pasting at the top again overwriting the last sheet's data. I'm also getting the row headers from each sheet pasted into the destination sheet, which would be ok the first time, but I don't want that from each sheet thereafter.

I'm sure there's a bit of ugliness in my code here (I'm a networking guy), so be nice. Thanks for your help on this in advance.

Here's my code:

Sub CopyRow()

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "BoM" And ws.Name <> "Summary" And ws.Name <> "Packet Storage" Then
ws.Activate
Dim LastRow As Long
Dim x As Long
Dim rng As Range
x = 1

For Each rng In ActiveSheet.Range("C:C")
If rng > 0 Then
rng.EntireRow.Copy
With Sheets("BoM").Cells(x, 1)
.PasteSpecial xlValues
.PasteSpecial xlFormats
End With
x = x + 1
End If
Next rng
End If
Next


Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

 

[davisshannon]

Hi,

No, my original script grabs the correct data, but just overwrites. Your script only grabs that single picture from another sheet in B2, and then row 1 from the Packet Storage sheet, and then a single header row from one of the sheets (the same header row is across all sheets, so I'm not sure which one).

I just looked, and my column A isn't populated, data starts on column B. So I changed:

Lastrowa = Sheets("BoM").Cells(Rows.Count, "A").End(xlUp).Row + 1

to

Lastrowa = Sheets("BoM").Cells(Rows.Count, "B").End(xlUp).Row + 1

Which is now giving me data. But I'm still getting that picture from another sheet, and then row 1 from the Packet Storage sheet.

I do not know what to say.
I have tested the script and it works for me.

I have asked and I'm not sure if you has answered.
Is everything working properly except for pulling in data from wrong sheet.

As far as images. I don't understand what type images you have. I guess if you have image attached to row then when it copies row to Master sheet it would copy in the image.

And if you have formulas in sheet Mom for example that refer to a row in sheet you not want to pull data in from the value would be pulled in because of the formula pulling in the data from the sheet you do not want to pull data in from.

What sort of data is in these rows.

 

[My Aswer Is This]

I want you to change the sheet name of that sheet you do not want to get data from

Name the sheet Me

Then change the script to exclude sheet named Me
And see what happens.

 

[davisshannon]

Same thing. I change the sheet to Me, and this line in. Still grabbing row 1 from that sheet and the image from B2 in another sheet.

If Sheets(i).Name <> "BoM" And Sheets(i).Name <> "Summary" And Sheets(i).Name <> "Me" Then

 

[My Aswer Is This]

I have run this script 20 times or more in the last several days.
And I have a sheet named Packet Storage

And it never picks up data from that sheet.

Now you fixed the other problem by changing last row.

That is because in most all cases I assume most all users have data in column A

It's very unusual to for people to have column A empty.

As far as the image you will have to sort that out.

You should be able to see the image in your master sheet and determine where it came from.
And determine why it's there.

Do you have all sorts of images in your sheets and cannot determine how it got on the master sheet.

I even inserted a image on one of my sheets and it did not copy over.

I asked earlier what type image is this. Do you use the insert image on your sheets.
Or do you have some sort of background image on your sheets

 

[My Aswer Is This]

Are you sure you do not have a hidden sheet where this data that should not be there is coming from.

The script will copy data from hidden sheets.

 

[footoo]

Your original code amended :

Sub CopyRow()
[COLOR=#ff0000]x = 1[/COLOR]
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "BoM" And ws.Name <> "Summary" And ws.Name <> "Packet Storage" Then
ws.Activate
Dim LastRow As Long
Dim x As Long
Dim rng As Range


For Each rng In ActiveSheet.Range("C:C")
If rng > 0 Then
rng.EntireRow.Copy
With Sheets("BoM").Cells(x, 1)
.PasteSpecial xlValues
.PasteSpecial xlFormats
End With
x = x + 1
End If
Next rng
End If
Next




Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub

 

[davisshannon]

I've got it working with this code now. Thanks for everyone helping. I took a couple lines from @My Aswer Is This and it's now looping through without overwriting.

Sub CopyRow()

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "BoM" And ws.Name <> "Summary" And ws.Name <> "Packet Storage" Then
ws.Activate
Dim LastRow As Long
Dim x As Long
Dim rng As Range
Dim Lastrowa As Long

For Each rng In ActiveSheet.Range("C19:C200")
If rng > 0 Then
rng.EntireRow.Copy
Lastrowa = Sheets("BoM").Cells(Rows.Count, "B").End(xlUp).Row + 1
With Sheets("BoM").Cells(Lastrowa, 1)
.PasteSpecial (xlPasteValuesAndNumberFormats)
.PasteSpecial (xlPasteColumnWidths)
End With
End If
Next rng
End If
Next


Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

 

[My Aswer Is This]

Glad to see you worked it out.

 

[footoo]

davisshannon
All you had to do in your original code was to put x=1 before (instead of inside) the For Each ws ... Next