Referencing original sheet to copy/paste into new sheet without using file name

Hello,

I am new to the forum and Macros. I recently got help with a pretty indepth Macro process. The person that helped with this used the TXT file based off steps I had and created it. It works perfect from start to finish but only on that file. The file name will change every week. I fixed the first part of the Macro to be universal for any file I run it on. However there is another part within the Macro that it references by file name. At this point I do not know where or what to put in the place so it will run with any file name. The issue is that I have one Sheet on the Workbook that has a file name, I then copy Columns B-F.....create a new tab (Sheet1) and Paste Special with Values into Columns A-E. So I need to know how to reference the original First sheet WITHOUT using the file name (because this will change every week) to copy and then paste into the new sheet that will ALWAYS be referred to as Sheet1 which is ok. Like I said....this whole Macro works flawless but I need to replace the part that has the file name with something generic to just reference the original first sheet I need to copy from. Here is my code...See this line in the code....here is the issue "Sheets("Insert WK 14 Batch # 4369").Columns("B:F").Copy 'I know, still hard-coded."

Please Login or Register  to view this content.

I don't know if I understand your problem.

Do you need to create a new worksheet by the name: "Insert WK 14 Batch # 4369"?

Or do you need to change the name: "Insert WK 14 Batch # 4369"? If so, what will you be changing it to?

Thank you for for replying to my post StevenM. The file name will always have the format of "Insert WK 14 Batch # 4369" but the WK number as well as the Batch number will change with the new file every week.... WK 15, WK 16, WK 17...as so on. I need the Macro to work on whatever file I want to run it on...as the file name changes. The issue here is that I am dealing with the file and it only has 1 sheet which is under the name "Insert WK 14 Batch # 4369" and at one point in the Macro it creates another sheet named Sheet1 by default. It copies Columns B-F on the first sheet (Insert WK 14 Batch # 4369) and pastes special with values this information into the newly created sheet (Sheet1) into Columns A-E. The macro works start to finish perfectly as long as I run it on this file "Insert WK 14 Batch # 4369". But if I try to run it on the fillowing weeks file "Insert WK 15 Batch # 4403" it will error becuase it does not recognize that file name. Is there a generic way I can reference the original first sheet to copy from and then paste special with values into Sheet1 that will never be given a tab name? The second tab (Sheet1) will always be that, there is no need to rename it.

You keep on saying "file name" although the line:

Please Login or Register  to view this content.

refers to a worksheet. But either way, the principle is the same.

The way I would handle the problem is that I would change:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

And change:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

Having done that, I would then create the following code (or something like it):

Please Login or Register  to view this content.

In this code I would do the basic housekeeping. If a workbook needs to be opened, or a new workbook created, I would do it here. Worksheets can be created, etc. Once everything is set in order, then you call: InsertsCreditCOMPLETEProcess passing it the newly named worksheet: wsWeekAndBatch

Now this is just an example, which should be tailored to your needs.