Excel - A macro to create new workbook and copy data
Solved/Closed
Shridharb2002
TrowaD
- Posts
- 12
- Registration date
- Saturday September 21, 2013
- Status
- Member
- Last seen
- April 9, 2014
TrowaD
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Related:
- Excel vba create new workbook and paste data
- Excel macro to create new workbook based on value in cells - Best answers
- Vba create new workbook and paste data - Best answers
- Macro to Create New Workbook and Copy Data at Each Change of X - Forum - Excel
- Macro to create new workbook and copy data ✓ - Forum - Excel
- VBA Copy sheet to new Excel File + automated manual data entry ✓ - Forum - Excel
- Excel - A macro to create new workbook and copy data - How-To - Excel
- Excel vba create new sheet with name from cell - Guide
13 replies
TrowaD
Updated on Nov 30, 2018 at 09:33 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Updated on Nov 30, 2018 at 09:33 AM
Hi Shridhar,
Please note that a sheet is not the same as a workbook as this will create confusion.
A workbook/excel file can contain multiple sheets/worksheets and not the other way around.
That being said here is your code:
Just watch the file extension within the code (look for green text after pasting.).
Best regards,
Trowa
Please note that a sheet is not the same as a workbook as this will create confusion.
A workbook/excel file can contain multiple sheets/worksheets and not the other way around.
That being said here is your code:
Sub RunMe() Dim lRow, lCol As Integer Sheets("Master").Select lRow = Range("A" & Rows.Count).End(xlUp).Row lCol = Cells(1, Columns.Count).End(xlToLeft).Column For Each cell In Range(Cells(1, "B"), Cells(1, lCol)) Union(Range("A1:A" & lRow), Range(Cells(1, cell.Column), Cells(lRow, cell.Column))).Copy Workbooks.Add Range("A1").PasteSpecial ActiveWorkbook.SaveAs Filename:= _ "C:\YourMap\" & cell.Value & ".xls" 'You might want to change the extension (.xls) according to your excel version ActiveWorkbook.Close Next cell Application.CutCopyMode = False End Sub
Just watch the file extension within the code (look for green text after pasting.).
Best regards,
Trowa
Dec 2, 2013 at 07:13 AM
The code is working perfect, can you also help me if I want the output without 1st row means without name as the workbook is named with the name.
and is it possible if we can get the output in a particular format like column width and height and font..
Please let me if it is possible..
Dec 3, 2013 at 11:38 AM
The following code doesn't copy the first row and has 4 extra lines to change Row Height, Column Width, Font and Font Size.
In the code I used column A as example. You could repeat the lines for other columns or apply the changes to multiple columns as in Columns("A:C") [or Rows("1:2") for Row Height]. The choice is yours :).
Here is the code:
Let me know if more changes are desired.
Best regards,
Trowa
Dec 5, 2013 at 05:39 AM
Code is working perfectly fine, thanks a lot :)
I'll get in touch with if some modification required.
Regards,
Shridhar
Jan 7, 2014 at 08:01 AM
Need your help for another macro,
I have a sheet which has, "A" column with City and B with Names and C, D, E.. with some other information. Now column A has duplicate value like India for more than 2 rows and all other column with the respective values.
I need an output which will create number of unique sheets with all the respective columns.
Like if column A2, A5, A7, A10 consist of value as India, then a sheet should be created as india and all the information from A2, A5, A7, A10 should be pasted there.
Please help me with this.
Thanks & Regards,
Shridhar
Jan 13, 2014 at 11:42 AM
Try this code and let me know how it works out:
Best regards,
Trowa