Macro to re-design the same worksheet than the one open.

jbesclapez

Board Regular
Joined
Feb 6, 2010
Messages
235
Hello,

I have kind of a silly question.

Imagine you have a workbook.
I would like to have a VB that would recreate the exact same workbook.
Note that it is not a simple copy paste. The VB will write all cells, add buttons if they are some, add the VB if tehre is any... a bit like a human would do manually..

Thanks
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,949
.
To re-create a workbook in its entirety ... use the SaveAs command :

VBA Code:
Option Explicit

Sub SaveAs()
Dim strFileSaveName As Variant
Dim fileFilter As String
On Error Resume Next

    strFileSaveName = Application.GetSaveAsFilename(fileFilter:="Microsoft Excel Workbook (*.xlsm), *.xlsm")
    
    ActiveWorkbook.SaveAs Filename:=strFileSaveName, CreateBackup:=False

End Sub
 

jbesclapez

Board Regular
Joined
Feb 6, 2010
Messages
235
.
To re-create a workbook in its entirety ... use the SaveAs command :

VBA Code:
Option Explicit

Sub SaveAs()
Dim strFileSaveName As Variant
Dim fileFilter As String
On Error Resume Next

    strFileSaveName = Application.GetSaveAsFilename(fileFilter:="Microsoft Excel Workbook (*.xlsm), *.xlsm")
   
    ActiveWorkbook.SaveAs Filename:=strFileSaveName, CreateBackup:=False

End Sub
Hi Logit,

I need to collect the VB that recreates the file. By saving it, i do not get the VB...
I mean if there is a value of 10 in A1
then I would like in my macro
Range("A1").Value = 10

But like this for all cells, and format too...


Thanks
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,949
The macro should save the workbook with all macros ... all formats ... all formulas, etc.

It does here.
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,721
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Try this code it writes the VBA to create the values and the formula and puts it into an extra worksheet. Doing the formatting is not quite so straight forward because you would need to test for each possible format. If you know what formatting is used then you can use the same technique to write the code.
To use it run this macor with the sheet you want to regenerate, then copy and paste the text in the new sheet to a vba module and then run that.
VBA Code:
Sub test()
Dim outarr()
inarr = Worksheets("Sheet1").UsedRange.Formula
ReDim outarr(1 To UBound(inarr, 1) * UBound(inarr, 2), 1 To 1)
indi = 1
For i = 1 To UBound(inarr, 1)
 For j = 1 To UBound(inarr, 2)
   If inarr(i, j) <> "" Then
      cellad = "cells(" & i & "," & j & ")"
    If Left(inarr(i, j), 1) = "=" Then
      outarr(indi, 1) = "Range(" & cellad & " ," & cellad & ").formula=" & Chr(34) & inarr(i, j) & Chr(34)
    Else
    outarr(indi, 1) = "Range(" & cellad & " ," & cellad & ")=" & Chr(34) & inarr(i, j) & Chr(34)
    End If
    indi = indi + 1
   End If
  Next j
Next i
Worksheets.Add
Range(Cells(1, 1), Cells(indi, 1)) = outarr


End Sub
 

jbesclapez

Board Regular
Joined
Feb 6, 2010
Messages
235
Try this code it writes the VBA to create the values and the formula and puts it into an extra worksheet. Doing the formatting is not quite so straight forward because you would need to test for each possible format. If you know what formatting is used then you can use the same technique to write the code.
To use it run this macor with the sheet you want to regenerate, then copy and paste the text in the new sheet to a vba module and then run that.
VBA Code:
Sub test()
Dim outarr()
inarr = Worksheets("Sheet1").UsedRange.Formula
ReDim outarr(1 To UBound(inarr, 1) * UBound(inarr, 2), 1 To 1)
indi = 1
For i = 1 To UBound(inarr, 1)
For j = 1 To UBound(inarr, 2)
   If inarr(i, j) <> "" Then
      cellad = "cells(" & i & "," & j & ")"
    If Left(inarr(i, j), 1) = "=" Then
      outarr(indi, 1) = "Range(" & cellad & " ," & cellad & ").formula=" & Chr(34) & inarr(i, j) & Chr(34)
    Else
    outarr(indi, 1) = "Range(" & cellad & " ," & cellad & ")=" & Chr(34) & inarr(i, j) & Chr(34)
    End If
    indi = indi + 1
   End If
  Next j
Next i
Worksheets.Add
Range(Cells(1, 1), Cells(indi, 1)) = outarr


End Sub

Hi OffTheLip,

This is great code what you did. Thanks.
It is the spirit of what I would like to achieve.
As you mentioned, I miss the format too.

The idea behind that would be to have a macro that could recopy a wksht in order to insert it later in its own VB. At least people wont move things around and it will always look the same...

Adding the formatting will be to much of a challenge for me... :( I hope someone can catch up on this one!

Thanks again,
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,721
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

If this is what you want, the easiest way to sort the formatting out would be to use my macro to record what the values and equations are , and then run the code generated,. Having got the new sheet, start recording a macro and then format the generated sheet as you want it formatted. You can then append this recorded code to the worksheet generating code.
 

jbesclapez

Board Regular
Joined
Feb 6, 2010
Messages
235
@offthelip I hear you, but then it is not really achieving the same purpose. I will think about how to do the same than your macro but with Formats... Thanks again!
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,721
Office Version
  1. 2010
Platform
  1. Windows
the problem is there are so many different types of format that you would need to test for, if you really wanted to cover every possibility. If you know that the data was only of certain types or even if you knew that certain column always contained certain types of data it would be easier. Just think of the number of different ways you can format a date!! Best of luck
 

jbesclapez

Board Regular
Joined
Feb 6, 2010
Messages
235
And cant we do something liek :
1 - Check if a cell as a format different thant the "default"
2- If not then Copy format of the cell
3- Paste the same format on the same cell.
Go to next cell...
 

Watch MrExcel Video

Forum statistics

Threads
1,126,986
Messages
5,621,995
Members
415,873
Latest member
fuulhouse

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
Top