VBA - copy/paste (same) range several times next to eachother in different sheets incl. formatting

[CaptnAbraham]

Greetings everyone!

I'm relatively new to this world and I've tried to solve my problem with macro recordings, tinkering with those and hitting up google but I did not yet get the hang of it. While being able to create single, individual steps when lucky, interconnecting several and of those never worked...

Situation:
I have a file with 12 worksheets (General, DataEntry1, Task1, Task2, Task3, Task4, Task5, Task6, Task7, Task8, Task9, DataEntry2) and I would like to be able to execute a copy/paste code in eleven of them (the exception being the first, "General" sheet) at the same time.

In each of the eleven worksheets there is a set range filled with information, it is the same in each of them, A40:G68.
1) I wish to copy this range an "x" amount of times next to eachother (A40:G68 -> H40:N68 -> O40:U68 and so forth), where "x" is a set number in cell B1 in sheet "General". (Or is it easier to grab the columns per se and not bother with a specific range?)
2) Once done, copy rows 40:68 an "y" amount of times beneath one another, where "y" is a set number in cell B2 in sheet "General".

Hope my crude description makes some sense to the vba initiated. I'm sure there are more elegant ways to put it and therefore to code it as well..

I would appreciate every bit of help I can get. Thanks!

 

[Jamie McMillan]

Hello,

Part One

Sub CopyPasteYourRange()
Dim i As Integer
    i = 1
    Application.ScreenUpdating = False
    For i = 1 To Range("B1").Value * 7 'Sets the range to copy to
    Range("A40:G68").Copy 'Copies your range
    Cells(40, i + 7).Select 'Selects the next cell to paste to
    ActiveSheet.Paste 'Pastes Data
    i = i + 6
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Part Two

Sub CopyPasteBelow()
Dim i As Integer
    i = 1
    Application.ScreenUpdating = False
    For i = 40 To Sheets("General").Range("B2").Value * 30 'Sets the rows to copy
    Range("A40:A68").EntireRow.Copy
    Cells(i + 29, 1).Select 'Selects the next cell to paste to
    ActiveSheet.Paste 'Pastes Data
    i = i + 28
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Then make a macro button

Sub DoAll()
Call CopyPasteYourRange 'Runs the first macro
Call CopyPasteBelow 'And then the second
End Sub

Jamie

 

[CaptnAbraham]

Thank you, that does indeed work very nicely, however...

Right now this only copies the data within the active worksheet "General" where I set the numbers and insert the macro button. Or rather, it does the work in whichever worksheet I copy it into and execute a macro button in.

Is there maybe a way to select the other sheets to do the copying in in the script, instead of just the one I have the macro button set in? I have tried to alter the code with "Sheets(Array(..." and also creating an Array first where I name the eleven sheets, but none of my attempts worked out. Maybe somehow un-activate the "General" worksheet and then activate others one after the other while executing the code above inbetween the activation/deactivation?

As a bonus question... can one also copy the column widths? It seems something along the lines of ".PasteSpecial Paste:=xlPasteColumnWidths" can do that but it doesn't seem to be as straightforward as to just Paste function in the above code...

 

[Jamie McMillan]

Hello,

Sub AllSheets()
Application.ScreenUpdating = False
Sheets("DataEntry1").Select
Call DoAll
Sheets("Task1").Select
Call DoAll
Sheets("Task2").Select
Call DoAll
'
'Do for all your Sheets
'
Application.ScreenUpdating = True
End Sub

NB On the first two macros remove - Application.ScreenUpdating = False & Application.ScreenUpdating = True (4 in total): This will be the macro that is assigned to the button, it will turn off
ScreenUpdating at the start and turn it back on at the end. (Otherwise it will be on/off/on/off/on/off/on/off and it will run slower. )

Jamie

 

[CaptnAbraham]

Yes, works like a charm, almost... For some reason part one of the code doesn't seem to be executed anymore.

While I was running it on only one worksheet before, the the two parts worked perfectly (and they still do... when I linked that part of the code to the macro button, it did copy the range horizontally, as wished).
But now, using the code across the named worksheets, it copies the range below but not to the right. Strangely, there is no error code (1004 or otherwise) displayed or any other noticable hiccup.

Dividing the code into two separate macro buttons with the same structure didn't help either. One works, the other one doesn't. Odd.

 

[CaptnAbraham]

Solved.
Once I had a good nights' sleep I realized I didn't have the reference to the "General" sheet in the first part.

Thanks a lot, Jamie!