VBA for Finance structure to Database structure (Copy & Paste)

[Dn Dilf]

Hi everybody,

I use this forum as much as I can to look for better ways to use Excel, but I'mn ot that good with VBA and I need ome help for restructuring some finance data into a Database tab.

I have 4 tabs, identical formatting except the amounts per Month are different. The 4 tabs represent Actuals, Forecast, Outlook and Business Plan (hence same structure for comparison but different numbers). In cells C5:J50 there are descriptions to group all info and in cells K5:V50 is the finance info per Month (January = K5:K50, feb = L5:L50…etc).

What I need: Copy/paste cells C5:J50 from tab Actuals with descriptions into tab Database, start on cell A2. In tab Database add in column J2:J50 1 for the period Janury. Then copy/paste from tab Actuals cells K5:K50 (January numbers) into tab Database again in cells K2:K50. And then repeat for February so you all info is underneath each other. Of course, the period should be filled in 2 for Feb etc.
After 12 times and all actuals are copied, move to tab Forecast and repeat all of the above until all 4 tabs are converted.

Hopefully this makes sense when you guys read it, always more difficult to explain it in words.

Thanks for the help

 

[Dn Dilf]

Sorry, need your help with one thing I just realized (and tried but failed).
I want to copy paste it as a value in the Database tab, and when done make the complete Database tab range an Table (for reporting purposes).

Hope you can help!

 

[Fluff]

How about

Sub Dn_Dilf()
   Dim Ws As Worksheet, Sht As Worksheet
   Dim i As Long, NxtRw As Long, j As Long
   Dim Ary As Variant
   
   Set Ws = Sheets("Database")
   Ary = Array(5, 46, 57, 47, 110, 23)
   NxtRw = 2
   For Each Sht In Sheets(Array("Actuals", "Forecast", "Outlook", "Business Plan"))
      For j = 0 To UBound(Ary) Step 2
         Sht.Range("C" & Ary(j)).Resize(Ary(j + 1), 8).Copy
         Ws.Range("A" & NxtRw).Resize(Ary(j + 1) * 12, 8).PasteSpecial xlPasteValues
         For i = 11 To 22
            Ws.Range("J" & NxtRw).Resize(Ary(j + 1)).Value = i - 10
            Sht.Cells(Ary(j), i).Resize(Ary(j + 1)).Copy
            Ws.Cells(NxtRw, 11).PasteSpecial xlPasteValues
            NxtRw = NxtRw + Ary(j + 1)
         Next i
      Next j
   Next Sht
   Application.CutCopyMode = False
   Ws.ListObjects.Add(xlSrcRange, Ws.UsedRange, , xlYes).Name = "Table1"
End Sub

 

[Dn Dilf]

You are officially my hero! Thanks!

 

[Fluff]

You're welcome & thanks for the feedback