Macro / Formula to update master file

[Injinia]

Hi,


I have a file with data under different regions but similar categories, as follows

Column B: Region name (50 regions), and more to be added sometime.

Column C: Category name (36 identical categories per region)


The data runs from Jan 2011 - Dec ("xxxx"), right now I have data up until April 2012. The above columns and rows have data(in numerical form).


The monthly data(IE May 2012, June 2012 etc) to be added to this master data is in a separate excel sheet (named "Update").


I would therefore like the following: A macro which checks the region and category name, matches the update and master file, by copying the data from the update and pasting it in the "empty month" column in the master file.


Appreciate ya'll helping hands in advance.


-Injinia

 

[SirJB7]

Hi, Injinia!

Considered uploading a sample file?

Regards!

 

[Injinia]

Hi,


Here is the sample file.


https://www.dropbox.com/sh/18lfwijgkdmsco4/KPjUwdU3Ts


I did not include numbers / figures in the sample file.


The idea is that the data on the update file would be copied to the Master file for the corresponding month by running a macro.


Regards,

Injinia

 

[SirJB7]

Hi, Injinia!


Place this code in any module or 'Master File' sheet code pane or 'ThisWorkbook' code pane, then run macro FromUpdateToMaster.


-----

[pre]

Option Explicit

Sub FromUpdateToMaster()
' constants
Const ksMaster = "Master File"
Const ksMasterRange = "C:N"
Const ksUpdate = "Update"
Const ksUpdateRange = "C:C"
' declarations
Dim rngM As Range, rngU As Range
Dim I As Integer, J As Integer
' start
Set rngM = Worksheets(ksMaster).Range(ksMasterRange)
Set rngU = Worksheets(ksUpdate).Range(ksUpdateRange)
' process
J = 0
For I = 1 To 12
If rngM.Cells(1, I).Value = rngU.Cells(1, 1).Value Then
J = I
Exit For
End If
Next I
If J = 0 Then
MsgBox "No entry found in master range first row for '" & _
rngU.Cells(1, 1).Value & "'. Cancelled.", _
vbApplicationModal + vbCritical + vbOKOnly, "Warning"
Else
rngU.Copy rngM.Columns(2 + J)
End If
' end
Set rngM = Nothing
Set rngU = Nothing
End Sub

[/pre]
-----


Or download a sample file from this link and proceed similarly.

https://dl.dropbox.com/u/60558749/Macro%20_%20Formula%20to%20update%20master%20file%20-%20Master_File%20%28for%20Injinia%20at%20chandoo.org%29.xlsm


Regards!

 

[Injinia]

Hi SirJB7,


Thanks for the code, however it copies the new data always to column J in the master file. Ideally, it should check for the next empty column and paste the data there since I will update the master file every month.


A little more help with that.


Cheers,

Injinia

 

[SirJB7]

Hi, Injinia!

It doesn't do such a thing, it simply takes the value in cell C1 of sheet Update, looks for that value into first row from C:N columns of sheet 'Master File', and then either copies the values from Update or displays a message.

Within the original requirement there's nothing regarding search for first available column, it clearly states that should update the empty column that matches the date.

In fact, in the file provided it doesn't update column J, but G instead.

Regards!