VBA Code To Copy From One Sheet & Paste To Another Sheet

Hi

I know there have been plenty of threads on this, but just couldn't find exactly what I was after.

I need to create some VBA code to copy data from an active worksheet to a sheet in a different file. I have 10 macros written which get run on a newly downloaded each day. The macros hide columns and rows of unwanted selections, leaving only selections for the day. I then have some code at the bottom of the macro to highlight and center the selections for manual copying and pasting, but I'd now like to try and automate the process a little more.

This is the code I have at the bottom of each macro, just before the End With and End Sub lines.

Please Login or Register  to view this content.

This next code is my full macro and the 2 red lines are what I copied from an existing thread here on this forum. I have tried to tweak them to meet my needs, but it doesn't appear they are correct. I wonder if someone might pass their eye over them to see what is wrong. The code works uo to those 2 red lines. Those lines should copy the data from A2 to the last visible cell in BR and paste it into the first available cell in column A of the file New Results File.xlsm and the sheet Safe Bets

Please Login or Register  to view this content.

Thanks so much in advance

Originally Posted by AlphaFrog

Please Login or Register  to view this content.

Thanks so much for your reply AlphaFrog

Unfortunately when I change the code to the above and run it, I am presented with a Run-time error '9': Subscript out of range. The line it takes me to when debugging is the Workbooks line. I am wondering whether the fact that it is opened via Dropbox has anything to do with it. There a handful of people in the team and all have access to the majority of files, so they are centrally stored and accessed via Dropbox. The New Results File is open when I run the macro, so not sure what is causing this.

Any thoughts at all?

Regards

As I indicated above, the file is open when I run the code and adding the extension made no difference. I had already tried that prior to my last post. I even tried saving the file locally instead of opening it from the central Dropbox location, but it did not change anything except now there is no error message popping up, just the debugger showing the Workbooks line highlighted.

Here is the new VBA code with the columns now changed to your suggested wording rather than using Selection

Please Login or Register  to view this content.

Any idea why that is still not running correctly?

cheers

cheers AlphaFrog. That has fixed the issue. All 10 macros now work fine, except when there is nothing to copy, then the macros copy and paste the header row. In the end, I guess my code from earlier which selects everything bar the header row and then centers it for manual copying and pasting, doesn't help here now. Now that copying and pasting is not a manual thing, it is probably best to either hide the header row instead, or have something say if the cells below it are empty, do nothing.

I think I can add code to hide the header row easily enough (Rows("1:1").EntireRow.Hidden = True) but I am not quite sure how to change the line below Dim LR As Long and the line Range ("A" & Rows.Count) etc.

Thanks so much for your help to date, as it is so close now

ah bakerman2

That is absolutely ideal. I have now altered it so it suits all 10 macros, as some had different columns to hide and used different filters, but it works perfectly, even when there is nothing to be copied

Thanks so much for your effort

Regards

One quick question re adapting this for something which requires no filters at all.

I deleted all of the code to do with filters and hiding columns, but kept the line on centering, as still need to do that. I need this to paste into the next available row in column B, so changed the Range to be B instead of A

Please Login or Register  to view this content.

When I run this as a macro I get a run-time error 91 message. The line it highlights on debugging is the .Autofilter line. The error message says Object variable or block variable not set, yet all that has been removed are the lines concerning filters to be run and the columns to be hidden. The rest is identical

Any thoughts on what is stopping it from running now?

cheers

cheers bakerman2. It was missing an End With, so added that second one it all appears to be fine.

Again thanks so much for your time. I was close. I can see what you did removing the End With after HorizontalAlignment and dumping .AutoFilter.Range before the offset.

Cheers mate

cheers mate. Have now marked this as solved and clicked the star on various answers.

Thanks again