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

eaykan

New Member
Joined
Mar 20, 2013
Messages
15
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:

ABCD
1NameDateInput
2ABC30/11/20141
3CDE30/11/20142

<tbody>
</tbody>


ABC sheet:
ABCD% Difference
1NameDateInput
2ABC31/10/20140.5
3ABC30/11/2014XX

<tbody>
</tbody>

CDE sheet:
ABCD% Difference
1NameDateInput
2CDE31/10/20140.5
3CDE30/11/2014XX

<tbody>
</tbody>
 
Last edited:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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 ??
 
Upvote 0
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
 
Upvote 0
Ok, in that case you need to provide us with ALL the information, not just part of it !!!
 
Upvote 0
Try this in a copy of your workbook.
If it isn't what you want, please give more details.

Rich (BB code):
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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