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

 

[My Aswer Is This]

I see your new here on the forum.
To speed up things you need to always provide specific details so we do not need to ask for details.

Now with your question we need to know.

Both sheet names

The copy from sheet name is??
The copy to sheet name is ??

Copy what row?

Do you mean the active row.
Do you always have values in column A of copy from sheet if not what column on copy from sheet always have a value entered

We need to know this so we can know what row on copy to sheet to past this copy from row.

And please provide any more specific details we may need.

 

[My Aswer Is This]

And if you have code that is not working always show us your code.

 

[fredwill]

Hi. Yes I am new. I have been using Excel for many years now and have used formula to achieve my results. I apologise for my ignorance, but I was getting desperate.

The reason for my request is that although the workbook has been operational for many months by multi users via drop box, I am retiring and will no longer have control over the file.

Answers -

Copy From = "Data"
Copy To = "Complete"

The Data worksheet is 36 columns wide (A - AJ) the If condition applies to column AG using a "y/n" trigger. Information will only be added to row cells as required and all or only some of the row cells may or may not be used.

The whole row needs to be copy and paste once the process is complete, we filter out and hide the completed rows on the "Data" sheet once it is done.

Not sure what I meant by Active row, I only copied the code, ??

I appreciate any assistance you can give me.

Regards





The "Complete" sheet will be static and only as a storage of completed projects

 

[My Aswer Is This]

Try this:

Looks in column AG for "y"

Sub Filter_Me_Please()
'Modified  12/17/2018  3:26:39 AM  EST
Application.ScreenUpdating = False
Sheets("Data").Activate
Dim lastrow As Long
Dim c As Long
Dim s As Variant
c = 33 ' Column Number Modify this to your need
s = "y" 'Search Value Modify to your need
lastrow = Cells(Rows.Count, c).End(xlUp).Row
With ActiveSheet.Cells(1, c).Resize(lastrow)
    .AutoFilter 1, s
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Complete").Rows(2)
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

 

[fredwill]

Hi,
Thank you very much for your reponse and the code, could not wait to get it all going. Sorry I have not replied sooner.

I created a new module and enterered your code as shown however, when i went to run the code, it showed the following

Compile Error
Else withouty if

and the top line
Sub Filter_FilterMe_Please() went yellow?

not sure what I am doing wrong. I can send you a screen shop if it helps.

Regards

 

[My Aswer Is This]

I just tested the script and it works for me

You need a sheet named "Data'
You need a sheet named "Complete"

The script looks for "y" in column AG

 

[vcoolio]

Hello Fredwill,

I can't see why M.A.I.T.'s code won't work as I also have tested it without any problems.

However, in the code that you found online by Bill Jenson, you may notice a subtle error that may be causing the code to error.

With the line you supplied in your opening post:-

FinalRow = Cells(Rows.Count, 1).End([COLOR=#ff0000]x1Up[/COLOR]).Row

the bit in red should read as :-

FinalRow = Cells(Rows.Count, 1).End([COLOR=#0000cd]xlUp[/COLOR]).Row

You have a 1 in the red bit when it should be a lower case L (the blue bit).

Try making that adjustment in the original code that you tried to see if it helps.

Cheerio,
vcoolio.

 

[fredwill]

Will give it a try.

thanks for all your help, I appreciate immensely.

Regards

 

[vcoolio]

Hello Fredwill,

No worries. Let us know how it works out. If it still doesn't work, we could always try something else.

Cheerio,
vcoolio.