VBA Query - Can you point me in the right direction please

[eaykan]

Hi all,

I am trying to get my head around how to start coding the following spreadsheet presented below in excel 2013. If someone could point me in the right direction as to what to look at or breakdown a set of tasks for me to carry out to complete the VBA code I would be much obliged.

I have a spreadsheet consisting of the following sheets (below). The data (input column C and the date column B) needs to be taken from the Input sheet and moved to the ABC Sheet or the CDE sheet to the very last row and calculate the % difference between the new input and the previous dates input.

This sheet would be updated monthly and the only data that changes is in the input sheet (column B - date, Column C - Input)

Any ideas would be greatly appreciated.

Input sheet:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Input[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ABC[/TD]
[TD]30/11/2014[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]CDE[/TD]
[TD]30/11/2014[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


ABC sheet:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]% Difference[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Input[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ABC[/TD]
[TD]31/10/2014[/TD]
[TD]0.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ABC[/TD]
[TD]30/11/2014[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

CDE sheet:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]% Difference[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Input[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CDE[/TD]
[TD]31/10/2014[/TD]
[TD]0.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]CDE[/TD]
[TD]30/11/2014[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

You probably need to confirm which is the correct information
You state
The data (input column C and the date column B)
whereas the data on the table is the other way aorund !!
Also, is there only sheets ABC and CDE to be transferred to, or are there other combinations ??

 

[eaykan]

thanks for your reply Michael. you are correct = the data/format of the tables are the right way around. The post should have read:

"I have a spreadsheet consisting of the following sheets (below). The data (input column D and the date column C) needs to be taken from the Input sheet and moved to the ABC Sheet or the CDE sheet to the very last row and calculate the % difference between the new input and the previous dates input.
This sheet would be updated monthly and the only data that changes is in the input sheet (column C- date, Column D - Input)"

To answer the second question - there are other combinations (i.e. more tabs)

Thanks again

 

[Michael M]

Ok, in that case you need to provide us with ALL the information, not just part of it !!!

 

[Peter_SSs]

Try this in a copy of your workbook.
If it isn't what you want, please give more details.

Sub MoveData()
  Dim Cell As Range
  Dim lr As Long
  Dim ws As Worksheet
  
  Application.ScreenUpdating = False
  With Sheets("Input")
    For Each Cell In .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
      Set ws = Nothing
      On Error Resume Next
      Set ws = Sheets(Cell.Value)
      On Error GoTo 0
      If ws Is Nothing Then
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = Cell.Value
        Set ws = ActiveSheet
        ws.Range("A1:D1").Value = Array("Name", "Date", "Input", "% Diff")
        ws.Range("A2:C2").Value = Cell.Resize(, 3).Value
      Else
        lr = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1
        ws.Cells(lr, 1).Resize(, 3).Value = Cell.Resize(, 3).Value
        ws.Cells(lr, 4).Value = (ws.Cells(lr, 3).Value - ws.Cells(lr - 1, 3).Value) / ws.Cells(lr - 1, 3).Value
        ws.Cells(lr, 4).NumberFormat = "0.00%"
      End If
    Next Cell
  End With
  Application.ScreenUpdating = True
End Sub

 

[eaykan]

Thank you Peter, this works perfectly! Much appreciated. Have a good week.

 

[eaykan]

Michael, thanks for your input and replies. Peter solved my issue. Have a good week.

 

[Peter_SSs]

Thank you Peter, this works perfectly! Much appreciated. Have a good week.

Glad it worked for you. Thanks for letting us know.