VBA Macros - Automizing process

Silvia_GB

New Member
Joined
Oct 10, 2022
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
Hello!

I have 1892 rows which I have to copy and paste in a certain way onto another sheet. So I started doing copy and paste manually, but I don't have much time, so I tried to do it with a macro. The problem is that I have to change the range, etc all the time, so I would like to know if there is any way to do it like +8 rows and so on. I paste here the code for you to understand what I am trying to do:

VBA Code:
Sheets("Sales data").Select
    Range("F258:N258").Select
    Selection.Copy
    Sheets("Sales per month").Select
    Range("G2306").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    ActiveWindow.SmallScroll Down:=6
    Sheets("Sales data").Select
    Range("A258:E258").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sales per month").Select
    Range("A2306:E2314").Select
    ActiveSheet.Paste
    Range("C2297").Select
   
    Sheets("Sales data").Select
    Range("F259:N259").Select
    Selection.Copy
    Sheets("Sales per month").Select
    Range("G2315").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    ActiveWindow.SmallScroll Down:=6
    Sheets("Sales data").Select
    Range("A259:E259").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sales per month").Select
    Range("A2315:E2323").Select
    ActiveSheet.Paste
    Range("C2297").Select

So the code to execute would be this one, one of the two paragraphs, but now I would like to automatize in order to not repeat each time where I need to change the range. So, the first range would always be plus +1 until row 1,892. The second would be +9, for example: G2315, G2324, G2333, etc. For the third range, it would be +1, as for the first one (it would be the same) and for the last range, it would be the second range for the letter A + 8 for the letter E.

Please please if someone could help, it would mean a lot, I really need to get this right!! Thank youuuu
 
Last edited by a moderator:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time. 😊

Give this a try with a copy of your workbook.

VBA Code:
Sub Move_Data()
  Dim wsSd As Worksheet, wsSpm As Worksheet
  Dim i As Long, nr As Long
  
  Set wsSd = Sheets("Sales Data")
  Set wsSpm = Sheets("Sales per month")
  nr = 2306
  Application.ScreenUpdating = False
  For i = 258 To wsSd.Range("A" & Rows.Count).End(xlUp).Row
    wsSd.Range("F" & i).Resize(, 9).Copy
    wsSpm.Range("G" & nr).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    wsSd.Range("A" & i).Resize(, 5).Copy Destination:=wsSpm.Range("A" & nr).Resize(9)
    nr = nr + 9
  Next i
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
Not sure what the data look likes, just try to reflect what your macro is doing:
VBA Code:
Option Explicit
Sub test()
Dim lr&, i&, j&, k&, t&, rng, arr(1 To 10000, 1 To 6)
With Sheets("Sales data")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    rng = .Range("A258:N" & lr).Value
End With
For i = 1 To UBound(rng)
    For t = 1 To 9
        k = k + 1
        For j = 1 To UBound(rng, 2)
            If j < 6 Then
                arr(k, j) = rng(i, j)
            Else
                arr(k, 6) = rng(i, 5 + t)
            End If
        Next
    Next
Next
With Sheets("Sales per month")
    .Range("A2306:G100000").ClearContents
    .Range("A2306").Resize(k, 6).Value = arr
End With
End Sub
Book1
ABCDEFGHIJKLMN
25812345T1T2T3T4T5T6T7T8T9
259678910T10T11T12T13T14T15T16T17T18
Sales data


Book1
ABCDEF
230612345T1
230712345T2
230812345T3
230912345T4
231012345T5
231112345T6
231212345T7
231312345T8
231412345T9
2315678910T10
2316678910T11
2317678910T12
2318678910T13
2319678910T14
2320678910T15
2321678910T16
2322678910T17
2323678910T18
Sales per month
 
Upvote 0
try to reflect what your macro is doing:
Not quite by the look of it.
From your sample data in 'Sales data', the OP's code would produce the colour you have shown in the 'Sales per month' sheet, but it looks like you must have just done that manually.
Of course if the OP has no special formatting (or formulas) then it wouldn't matter, but that's why I chose to replicate the copy/paste to preserve any formatting (or formulas).
 
Upvote 0
Thank you so much guys for your answers! @Peter_SSs your code worked perfectly, @bebo021999 your code worked as well but just for some rows, not for all the 1,892 rows, after running it, the program said that there was an error.

Serioulsy, thank you so muuuch for your quick answer, you're the best! :)

 
Upvote 0
You're welcome. Glad we could help. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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