Copying a worksheet from a workbook and save it as a new worksheet based on a list. VBA

Hi guys

I am working on a workbook (excel 2010) with 2 sheets. First sheet (List) contains the list of names in column B and C. The second sheet (CIS & FMS data) is the template I want to copy.

The entries in the list will be up to 1000 entries.

My request is to create a macro that copies the sheet (CIS & FMS data) x number of entries in the list. I do not want to copy the sheet 'List'. I also DO NOT want to copy the macro in to the newly created sheets.

Helpful if I can save each newly created worksheets with the names from Column B and C. For example the naming of each sheet is the combined name from column B&C. So the first sheet will be named 'AH15/100 Name -A' and so on.

Below is the first few lines of the list
Column B Column C
AH15/100 Name - A
AH15/101 Name - B
AH15/102 Name - C
AH15/103 Name - D



I also would like to automatically save the sheets in to a destination folder.


Thanks guys


Below is the macro I made from copying and amending existing macros from other sources but not working at all

Sub CreateWBs()
Dim lRow, x As Integer
Dim wbName As String
Dim MyRange As Range
Dim Sheet As Range


Set MyRange = Sheet("List").Range("B5")
lRow = Range("B" & Rows.Count).End(xlUp).Row
x = 1
Do
x = x + 1
wbName = Range("B" & x).Value & "_" & Range("C" & x).Value
ThisWorkbook.ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=" H:\Desktop\to upload TRIM\" & wbName & ".xls"
Loop Until x = lRow
ActiveWorkbook.Close


End Sub

Thanks jolivanes

I have tried the code but it is coming up with a message box saying " Object doesn't support this property or method".

But the code to change '/' to '_' is working perfectly.

Can you please help further

Thank you

malitec

Can you attach a small example of your actual workbook.
Change all data that should not be seen by other people, in other words, sanitize before attaching.
It needs to be exactly like your original as far as sheet names etc is concerned.

Thanks jolivanes

Please see attached zip file.

Thank you

malitec

My bad. Forgot the Sheet name at the 2nd half of this line.
I have not checked your attachment yet because I think that this is the problem.
Could you change it and let me know if it works please.
Sorry about this.
Change this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

Thanks jolivanes .its working. Appreciating your effort. You are a gem

Thanks

malitec

Nice to hear that it's working to your liking.
Good luck