VBA to copy range then paste values, formatting, column widths x times to right

[Malley87]

Hi All,
I have spent quite some time searching for a solution to this problem without success so thought I would post my first post.

I would like to make a macro that allows the user to input a cell value to determine the last cell in a range (A1:x). The range always starts at A1.

Then the user is asked how many times the range is to be copied (z).

The range is copied, and the cell values, formatting, and the column widths of the copied range are copied z times to the right.

I would appreciate any help!

Thanks

 

[Michael M]

You don't really need the user to specify the last row in a range. The macro can do that for you.
Also is the lasy cell also in Column "A", not a different column ?
How is the user deciding the number of columns...a value Input into a cell OR do you require a dialog box to popup for them to fill in ??

 

[Malley87]

The range will vary depending on what the user puts into it. I think ultimately it would be easier if the user input the bottom right cell in the range, maybe via a dialogue box. The last cell won't be in column A. Could be S23 for example, but it will vary each time.

The number of copies could also be input via a dialogue box.

 

[Michael M]

Maybe this....Untested.
This assumes you are copying the sheets used range, so no input for last cell is required

Sub MM1()
Dim cr As Integer, lc As Integer, c As Integer, ur As Range
Set ur = ActiveSheet.UsedRange
cr = Application.InputBox("How many copies are required?")
c = 1
Do
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    ur.Copy Cells(1, lc + 1)
c = c + 1
Loop Until c > cr
End Sub

 

[Malley87]

Thanks Michael M.

There are some problems with the code. Some cells in the range are merged, and there is an error with the following line in the code: ur.Copy Cells(1, lc + 1)
Resulting in a run time error: "We can't do that to a merged cell".

To go into more detail about the range being copied. The last cell in the range may not actually have anything in it. The last row may not have any contents, but the spacing is necessary for the form to fit on an A4 page in landscape and look aesthetic. The range may contain merged cells, company logo, formulas with named ranges, lookup formulas etc. Copying the range enables multiple copies of the same form to be created, but then formulas in the copied forms can be edited to suit.

 

[Michael M]

Hmmm, working with merged cells in this case will cause a lot of issues, especially with VBA.
I'd suggest unmerging those cells and instead using, "centre across selection" method>> Highlight the cells >>format>>Alignment>>Horizontal>> and select from the dropdown.

 

[Malley87]

I have had some success with this code, but the only problem is that it doesn't adjust the column widths when it copies over...

Sub CopyHorizontally()
Dim mR As Range
On Error Resume Next
Set mR = Application.InputBox("Select your Range", , , , , , , 8)
    If mR Is Nothing Then MsgBox "Nothing Selected!", vbExclamation: Exit Sub
On Error GoTo 0
Dim HowManyTimes As Integer: HowManyTimes = InputBox("How Many Times", , 1)
    If HowManyTimes < 1 Then MsgBox "Value Entered is not valid!", vbExclamation: Exit Sub
mR.Copy mR.Offset(, mR.Columns.Count).Resize(, mR.Columns.Count * HowManyTimes)
End Sub

 

[Michael M]

If you have merged cells it will fail when trying to autofit or column adjustment !