try to Copy entire Row if active cell equals variable

[Mfederman]

Hey all,

I've been working on this for about a week and am having some troubles with the final steps. This is my very first attempt at VBA with ZERO experience with it, so please be kind if this is an extremely Noobie question.

I cannot for the life of me get the macro to copy the entire row. I know it has to be a something I'm doing wrong in the code. The closest I have come is getting the macro to copy a single cell. Below is the macro code as it sits right now. Any help would be very much appreciated.

Please Login or Register  to view this content.

[Sintek]

Hi Mfederman

Welcome to the Forum.
In future please view the forum rules before posting. Your post does not comply with rule #3.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window.

Would be ideal to see a sample workbook. Start with changing this line of code

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Mfederman]

My apologies. I have fixed the Code issue and have added an attachment that is a sample from the workbook I'm working with.

Thank you very much for the suggestion Sintek! any other advice you can offer?

I'm also trying to get the Macro to save the newly created workbook based on the variable Payrolldate, is that possible?

[Sintek]

Please rather add a sample file...not a image. Cannot do tests on a image.
Originally Posted by Pete_UK
To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

[Mfederman]

VBA didn't like the Range.offset, so I changed it back to Activecell.offset and that got rid of my error there. Now I'm getting another error that is as follows.

"Run-time error '91':
Object Variable or with blocl variable not set"

When I hit the Debug button, it highlights the like where I display the input box to set the variable.

[Mfederman]

ok, Sorry about that. I have uploaded a portion of the actual worksbook.

[Sintek]

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Please rather add a sample file...not a image. Cannot do tests on a image.

[Sintek]

Are you wanting to copy all the rows that match the payroll input date to a new workbook.
Your variable

Please Login or Register  to view this content.

is giving the error

[Mfederman]

Yes I do. The end goal is to create a CSV file of the current payroll date (payrolldate variable) to be uploaded to our payroll company's website.

[Mfederman]

What should the payrolldate variable be then? Would it not be a range?

[Sintek]

Try this: Not happy with the loop. Perhaps someone can add code with a Do While syntax

Please Login or Register  to view this content.

[Mfederman]

You are awesome. I REALLY appreciate you taking the time to put this together!
I just copied and pasted the code in to a new module and tried to step through and got a new error:

"Run-time error '6': Overflow"

I'm not sure what that means.

The line that gets highlighted when I hit debug is

Please Login or Register  to view this content.

"

[Sintek]

Go with this code...Do while loop

Please Login or Register  to view this content.

[Sintek]

You are awesome. I REALLY appreciate you taking the time to put this together!
I just copied and pasted the code in to a new module and tried to step through and got a new error:

"Run-time error '6': Overflow"

I'm not sure what that means.

The line that gets highlighted when I hit debug is

Please Login or Register  to view this content.

"

Yep, sorry...Need to declare i As a Double and not Integer
So

Please Login or Register  to view this content.

must be

Please Login or Register  to view this content.

EDIT: But seriously go with my second code...Post # 13 Works much better.

[Mfederman]

This one does not find anything after I enter the date in the input box.

Go with this code...Do while loop

Please Login or Register  to view this content.

[Mfederman]

This one now works great, but also does not return any results when I enter the payroll date I'm looking for.

Again, I can't thank you enough for your help on this!

Yep, sorry...Need to declare i As a Double and not Integer
So

Please Login or Register  to view this content.

must be

Please Login or Register  to view this content.

[Mfederman]

Thank you for adding in the errorhandler. However, it's still not coming back with any results that it's copying and pasting into the new workbook. I know without a doubt that I am entering a valid string as a date.

Go with this code...Do while loop

Please Login or Register  to view this content.

[Sintek]

Oops sorry....My destination folderpath is in code
Herewith amended....See both files and amended code in Post13

[Mfederman]

I'm not sure why, but I still keep getting the msgbox that shows "No payroll records with that state date exists"

It's still not "finding" anything in my original file. The original file has over 39,000 rows in it currently and will continue to grow with each week that passes. I'm not sure if that matters or not.

Oops sorry....My destination folderpath is in code
Herewith amended....See both files and amended code in Post13

[Sintek]

Have you tested my attached and seen it works.

Search for a date that is in the first couple of rows and Dim i As Long

[Mfederman]

I did try. It doesn't work for me. Is it possibly something to do with our different versions of excel? I see you're in 2007 and I'm using 2010.

Have you tested my attached and seen it works.

[Sintek]

Search for a date that is in the first couple of rows and

Please Login or Register  to view this content.

[Sintek]

Is it possibly something to do with our different versions of excel? I see you're in 2007 and I'm using 2010.

Don't think so...

[Mfederman]

I made that change and tried to find the very first payroll date in coloum F. It was able to find that without a problem. But it won't find anything after that first payroll date

[Sintek]

And your dates are in that format YYYYMMDD
EDIT:
Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Mfederman]

yep. Definitely. They have to be since that's how it comes from the database that's linked to sheet 1

[Mfederman]

I changed that, but still does the same thing. It will run and do exactly what it's supposed to but only for the first payroll date on the sheet.

[Sintek]

Not sure what else to do....Create a new sample with a few of your current entries and test the code again....Also upload so that we can test. See if there is a difference.
Edit:

Herewith code that solves this Threads issue.

Please Login or Register  to view this content.