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

[jbesclapez]

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

 

[Logit]

.
To re-create a workbook in its entirety ... use the SaveAs command :

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]

.
To re-create a workbook in its entirety ... use the SaveAs command :

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]

The macro should save the workbook with all macros ... all formats ... all formulas, etc.

It does here.

 

[offthelip]

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.

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]

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.

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]

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]

@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]

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]

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...