Copy Data from One sheet to another Workbook with same formating

[Darshan Shah]

Hello everyone..

I am stuck in code.. Here is the explanation with example.. Hope it is not confusing

I have made one model for order booking. I run it for each order to get optimum solutions. Now for the next step i want your help..

As example, I run this model for say "X" customer. I wanted to save that output in different workbook (Say Master File). Now i wanted the msg box which will rename that sheet name in "Master File". (by default the sheet name will be "Sheet1" but i want to make it as user input name) (For example i will rename that sheet as "X-order")

Now, for case 2, say for "Y" customer i ran the same model and output should be save in "Master File" in new sheet. Where i will rename with that msg box as "Y-Order".

So, finally in Master sheet there will be 2 sheets. 1st is "X-order" & the second is "Y-order". And so on..

Data should be save in Master file with the same format of "Model" i.e width of column & height of Raw. It consist "Image" too. That should also be copied too. and it should be values only. Not formula.

I have tried this below code, but output is new workbook.. I don't want that.. Please Help!

Sub Exportluck()

Dim strFileName As String

strFileName = InputBox("Type a name for the new workbook", "File Name")
If Trim(strFileName) = vbNullString Then Exit Sub

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Range("A4:O47").Copy
With ActiveWorkbook.ActiveSheet
.UsedRange.Value = .UsedRange.Value
End With
Sheets.Add.Range("A1").PasteSpecial xlPasteAll
Application.CutCopyMode = False
ActiveSheet.UsedRange.EntireColumn.AutoFit
ActiveSheet.Move
ActiveWorkbook.SaveAs "C:\Users\Desktop\" & strFileName & ".xlsx"
ActiveWorkbook.Close False

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[jolivanes]

How far did you get?

 

[Darshan Shah]

How far did you get?

Output sheet is blank

 

[Darshan Shah]

Oh yes.. It's Coming.. But image is not being copied by using that code..

We are near at 99% of the output