Copy/Paste

[fredwill]

Hi,

I need to use VBA code to copy a line from the first worksheet to the second worksheet in the same workbook. the line would need to be pasted to the next empty line.


I downloaded a fact sheet from called Range.Copy (Bill Jenson) dated the 06/08/2017 and copied the sample code verbatim.

Unfortunately it did not work. the following line seemed to be a problem as it highlighted Yellow when I tried to run the code.

FinalRow = Cells(Rows.Count, 1).End(x1Up).Row.

Any advise you can give me would be much appreciated as I have to use the file over our Xmas break. I am trying to learn to use VBA code, it is however a slow process.

Regards

 

[fredwill]

Hi vcoolio,

I have fixed up my errors with the input of the code however, now when I run the code the message box appears that says "No values found". ( I assume it is working?)

I have the "y" in column AG of my first row as I previously mentioned but the code obviously cannot find the row.

Due to headings etc on my Data sheet my first row of input does not start until row 4. That should not make any difference I would have thought, as the code looks at the whole worksheet?

Any ideas?

Regards

 

[My Aswer Is This]

I wrote the code and the script does not look for "y" till row 2
Most people have headers in row(1)

But you say your data does not start to row(4)
So why do you have a "y" in row(1)

Are you saying you want Row(1) to always be copied to sheet complete.

Then that can be done but not require a y to be in row(1)

And if the script still not running or says no values found it's because you have no "y" in rows(2) to last row

If you have no "y" in rows(2) to lastrow what do you want this script to do.

 

[vcoolio]

Hello Fredwill,

Without seeing the code its difficult to tell however I would assume that the code is working but can't find the values because it is searching/filtering above your data set first or the criterion "y" is in another column.

Due to headings etc on my Data sheet my first row of input does not start until row 4. That should not make any difference I would have thought, as the code looks at the whole worksheet?

Where the data starts does make a difference. A direct reference to the actual data set is essential as "peripherals" just hinder the code. I now also assume that your headings are in row3 with data starting in row4.

You may want to dump the code that you found online and just alter the code that M.A.I.T. supplied to allow for the filtering to start on AG3 or, as an alternative, you could try the following code:-

Sub Test()

Dim ws As Worksheet: Set ws = Sheets("Data")
Dim ws1 As Worksheet: Set ws1 = Sheets("Complete")

Application.ScreenUpdating = False

With ws.[A3].CurrentRegion
        .AutoFilter 33, "y"
        .Offset(1).Copy
        ws1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
        .AutoFilter
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I would also be interested to see your answers to M.A.I.T's questions in post #12 .

Cheerio,
vcoolio.