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

 

[My Aswer Is This]

In your original post you said:

If ws.Name <> "BoM" And ws.Name <> "Summary" And ws.Name <> "​

Packet Storage​

" Then

But in your last post you said:

​

Packet Capture​

​

_{^{<strike>
</strike>}}​

 

[davisshannon]

Yeah, sorry, BoM, Summary, and Packet Storage sheets should be ignored.

 

[davisshannon]

My original script works ok, but it just needs to paste into the next available row. My brain starts hurting a bit working through the loops to get this to happen. Does anyone have any ideas?

Thanks

 

[My Aswer Is This]

And that is happening.

Look at the script and you will see that line of code. Change those sheet names if they are wrong.

If it's still not doing what you want.
Someone else here at Mr. Excel will I'm sure be able to help you.

What exactly now is it doing wrong?

 

[davisshannon]

Thanks for your help. Yeah, the sheet names are fine, but for some reason it's grabbing data from the Packet Storage sheet (last one in the workbook), and the image from column B somewhere (most sheets have the same image in that column).

I'll see if someone else comes back with anything, but thank you very much for trying to help me out.

And that is happening.

Look at the script and you will see that line of code. Change those sheet names if they are wrong.

If it's still not doing what you want.
Someone else here at Mr. Excel will I'm sure be able to help you.

What exactly now is it doing wrong?

 

[My Aswer Is This]

OK:
I still suspect the sheet name itself is not:

Packet Storage​

Or the name shown in the script is not correct.

These names must be exact including spaces.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[davisshannon]

Thanks. Yeah, I've checked the name a few times. And the image doesn't exist in that sheet. My script used to copy stuff from Packet Storage until I put that line in to ignore it, and then it worked ok, except for the overwriting line issue.

 

[My Aswer Is This]

Here is a test to see if your sheet names are correct:

Run this script.

If you get a error it's because your sheet names are not correct.

Sub Test_Me()
Sheets("BoM").Activate
Sheets("Summary").Activate
Sheets("Packet Storage").Activate
    
End Sub

 

[davisshannon]

Yep, those sheets come back fine with that test, so the name is ok. Not sure why it's grabbing data from Packet Storage (the very last sheet), along with an image from B2 in another sheet (most have the same image), where C2 in every row is a blank cell?

Does anyone else have any ideas on how to just get this pasting loop working without overwriting the previous sheet's data?

 

[My Aswer Is This]

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.