Copy Data from One sheet to another Workbook with same formating

Darshan Shah

New Member
Joined
Jul 4, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hello everyone..

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


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!


VBA Code:
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Oh yes.. It's Coming.. But image is not being copied by using that code..

We are near at 99% of the output :)
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top