Copy selected worksheets between workbooks onto named worksheets

[AndrewSimpson87]

Hi,

New to VBA and this is my first shot at some code. Trying to cycle through a user specified directory for all excel workbooks. It is going to copy two sheets from each of these workbooks back into the ConsolidatedWorkbook. Each workbook has a unique name, which i have set up as a worksheet on the main ConsolidatedWorkbook. In an ideal world, it would check if the sheet existed first. If not, create one.

All of Sheet 3 is copied over, but only a selection from sheet 2.

MY below code worked the first time i ran it, but now fails to run.

All help and suggestions welcome...like i say, first crack at coding in VBA so go easy.

Please Login or Register  to view this content.

[jaslake]

Hi Andrew

Welcome to the Forum!!

Where (or how) is it failing?

[AndrewSimpson87]

Hi,

Thanks for the quick reply. Seems to be failing here on the first copy...


MyFolder = GetFolder("C:\", "Select an Input Folder")
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
Workbooks.Open filename:=MyFolder & "\" & MyFile
Set sourceBook = Workbooks(MyFile)


Windows(MyFile).Activate
Sheets("Sheet3").Select
Cells.Select
Selection.Copy

Get a runtime error as if there is no selection made prior to the copy. However, when you step into this you can see the correct sheet is "selected" ready to copy.

[jaslake]

Hi Andrew

Please use Code Tags around any Code you post.

Your Code could do with a bit of surgery but this Line of Code, I suspect, is the one that's failing

Please Login or Register  to view this content.

It should probably read

Please Login or Register  to view this content.

[AndrewSimpson87]

Hi,

Apologies. In my excitement at getting a reply so quick, I forgot to wrap my code in the appropriate tags. Noted for the future.

Tried your suggestion, but still failing at the same section.

[jaslake]

Hi Andrew

I'll need to see your File(s) in order to trouble shoot.

Please attach the Workbook the Code Runs from (include the Code in the Workbook) and attached 2 or 3 examples of the Files you'll be extracting Data from.

The structure of your attachments should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" worksheet that demonstrates what you wish the output to be.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the "Add Files"... button to locate your file for uploading.
6. This will open a new window File Upload...Click "Select Files"
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the "Upload Files" button and wait until the file has uploaded.
10. Click the "Done" Button.

[AndrewSimpson87]

Hi,

Apologies for the delay.

Unfortunately, these are accounts files which I am unable to share here. I have, however, set up a sheet which might better explain what i am attempting to do. Hopefully you can better follow my logic.

If you really cannot help without the full files, i understand. And I appreciate your time thus far.

If you could think of an easier approach to this problem, i welcome your suggestions.