0

I am in need of some help and ideas.

I have a folder, let's say this one C:\Users\ Me \Desktop\Test Dir\Files\ In this folder I have several workbooks and my Master Workbook named Test.xlsm

In my master Workbook I have several sheets with different names AB11, AB12, BC13 etc

I want to build a code that will copy and move the sheet AB1 to a excel file in the folder whose name ends with AB1 (or maybe contains this word).

And then the loop will continue. If the next Excel file ends with AB2, then copy and move the sheet AB2 from my Master Workbook.

Any ideas? What I have so far is:

However, my file cannot be found , the one that ends with the name "AB11"


Dim myPath As String
Dim myFileAB11 As Workbook
Dim wb As Workbook

myPath = "C:\Users\Me\Desktop\Test\Split "

filenamefilters = Array("AB11.xlsx", "AB12.xlsx", "BC13.xlsx", "BC14.xlsx")
myFileAB11 = Dir(myPath & "*AB115*.xls", vbNormal)
myFileAB12 = Dir(myPath & "*AB12*.xls", vbNormal)
myFileBC13 = Dir(myPath & "*BC13*.xls", vbNormal)
myFileBC14 = Dir(myPath & "*BC14*.xls", vbNormal)

Workbooks.Open (Dir(myPath & "*GB55.xlsx", vbNormal))

Workbooks(myFileAB11).Activate
Sheets.Add
ActiveSheet.Name = "AB11"

Workbooks.Open Filename:="C:\Users\Me\Desktop\Test\Test.xlsm"
Sheets(AB11).Copy Before = Workbooks(myFileAB11).Sheets(Sheets.Count)
ActiveSheet.Name = "AB11"



End Sub
Redds
  • 33
  • 3
  • 3
    My idea is: Split your project into smaller parts. Do some research on every part. Try something and come back with your attempt (your code) and ask a question to it, incl. telling what went wrong or where you got stuck: See [No attempt was made](http://idownvotedbecau.se/noattempt/) and [ask]. – Pᴇʜ Nov 11 '20 at 15:42
  • Try searching for how to use Dir and how to transfer data from one workbook to another, there are many dupe targets for both of those. – Warcupine Nov 11 '20 at 15:45

1 Answers1

-2

I don't like that people are more likely to respond with a "try harder" message than some actual advice from people who have actually solved these problems. Here is some useful code for moving sheets between two workbooks. I explained what each line does.

Dim SourceWorkbook As Workbook, CurrentWorkbook As Workbook 'sets up objects references for the workbook files.

Set CurrentWorkbook = ThisWorkbook 'ThisWorkbook is a keyword that references the book this code is running on
Set SourceWorkbook = Workbooks.Open("D:\work files\Warehouse inventory system\Input file.xlsx") 'this will open the file and assign the reference to it to SourceWorkbook

SourceWorkbook.Sheets("Sheet1").Copy After:=CurrentWorkbook.Sheets("Main") 'this code section opens the file, imports the sheet
SourceWorkbook.Close 'Closes the second workbook, recommended to free up resources

Sheets("Sheet1").Activate 'this makes the newly pasted sheet the active sheet
Sheets("Sheet1").Cells.Select 'this will select all cells on that sheet, it is not nessacary to activate the sheet to select the cells
Selection.Copy 'copys the selection to the clipboard
Sheets("Main").Select 'selects the sheet named Main
Cells(1, 1).Select 'selects the first cell in the sheet
ActiveSheet.Paste 'pastes in the data from the other sheet

Application.DisplayAlerts = False 'turns off alert messages before deleting to remove the "are you sure" message
Sheets("Sheet1").Delete 'deletes the sheet named "Sheet1"
Application.DisplayAlerts = True 'turns on the alert messages after the delete
Sheets.Add(After:=Sheets("Main")).name = "Barcode" 'adds a new sheet and names it Barcode
Sheets("Main").Select 'selects the Main sheet

you will of course have to figure out how to modify this code to make it work for your situation. but it should be more then enough to get you started.

Scott Cannon
  • 47
  • 6
  • 2
    Don't use `.Select`. You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). And we didn't ask to "try harder" we just asked "to try *at all*" otherwise it is just asking other people to do the work. – Pᴇʜ Nov 11 '20 at 16:03
  • 1
    @Scott Cannon: The reason is mainly because SO was meant to be a resource to find *answers* to questions and not to ask the same question again and again. See https://meta.stackoverflow.com/a/261593/7599798. But nowadays SO is more like every other forum, someone ask for a code, someone else answers more or less good and noone will use the given answer for a similar problem. – FunThomas Nov 11 '20 at 16:43
  • And please get rid of all the `Activate` and `Select`-statements. And qualify the objects like `Sheets` and `Cells`. And never rely on `Activesheet` – FunThomas Nov 11 '20 at 16:48
  • 1
    @ScottCannon The problem is this is not, nor was it ever intended to be a tutorial site. If you need to learn programming there are plenty of resources. If you want general help reddit is probably your best bet. If you need something complete, quick, and to your exact specifications then there are plenty of people willing to be paid to do that out there. SO is for specific programming related issues. It comes across as harsh but isn't meant to be, some stuff just isn't a good fit for SO. (After the OP edit it's definitely in better shape.) – Warcupine Nov 11 '20 at 18:07
  • The difference between tutorial and code debugging is semantic. you are still explaining how to accomplish a task, your problem is that you don't like the person is starting from in your opinion too low of a pool of knowledge and asking too broad of a question. which is at it's very core is a completely subjective argument. there is nothing wrong with someone asking how others would solve the problem, Sometimes the code you are stuck on is figuring out the limits of the tools available, sometimes it's figuring out what tools are available. Google can't provide insight only reference. – Scott Cannon Nov 11 '20 at 18:48
  • @ScottCannon No, the problem is not starting from a low knowledge. The problem was clearly not showing any effort at all for an issue where you can easily find a ton of information. This has nothing to do with where a person starts I'm sure everyone will get help here if he can explain his issue and shows that he at least tried *something* even if he completely messed up. • But what SO is definitely not for is: Someone posts his requirements and waits til someone posts the code. – Pᴇʜ Nov 11 '20 at 19:51