Challenging VBA Code

[Cheezee]

Hi guys, First of all I would like to thank you for visiting my thread and hoping that you could help me to solve this VBA Code

Pls see attachment Zipped for Excel file

As you can see in the attachment there is 3 files of excel namely Previous, Present and DesireOutput

Here's what we going to do we need to build/create a VBA code that can extract the difference of cell value in every sheet of workbook namely Previous and Present. The extracted value of the difference will create a new workbook which is DesireOutput.

In the attachment file it has 3 sheet, but this sheet varied depending on project load

 

[Gasman]

Why can't you just refer to the cells from within the DesireOutput workbook?

 

[Cheezee]

DesireOutput workbook is the result of the difference of Present and Previous workbook. In the attachment it is only few cell value and sheet within the workbook...
what if it is consisting of 200rows, with 500 columns and not only that it has 50sheet in every workbook.
How will you handle that in ease without having trouble of double tag mistake or we can call it human error....

 

[Gasman]

VBA code is not going to prevent human error in the input of data in the sheets.

I was just thinking of formula per sheet, referencing cells in the same position on each sheet.

So in the output sheet cell A1 would hold Present!A1 - Previous!A1 for the same sheet.

For this to work the structures would need to be the same, but I expect they would still have to be if you used VBA to walk through the cells.

If the formulas pointed to the correct cell on the correct sheet for the correct file, Excel would do all the work for you.

Challenging VBA code is an apt title.

 

[Cheezee]

The main goal is to extract the difference of the two workbook and this difference will open a New Workbook to clearly see the difference of the previous and present workbook.. I think by extracting the difference we can minimize the human error....

I guess you didn't see the whole picture of it. see image

If it is really apt title as you said can you produce the vba code,
A VBA code that produce the DesireOutput in my above attachment?
pls be reminded the no. of sheet, rows and columns is varied depending on the project load...

 

[Cheezee]

OOooppss!! I forgot to include in Column A of DesireOutput workbook it should be add/change/remove.. sorry for the inconvenience

 

[Gasman]

I don't think anyone here is going to write the code for you.

They will help you with your attempts with creating it though.

It really does sound like you do not know VBA at all, so it is going to prove very difficult.

The way I generally approach something like this is how I would do it manually, and then try and optimize it.

So you would need to investigate......

How you open excel files in VBA
How you determine the extent of the data to process.
How you move around those Excel files
How to obtain the values from each cell and store them in cells.
How to switch to the various files you have open and choose the relevant sheet.
How to save the files.

All of this is available on the net, though you would have to adapt it to suit.

Really, that is how I have learnt VBA and whilst I would try and help, it would take me quite a while to create the code you are looking for and that is when I know exactly what the data looks like, not getting it in dribs and drabs via a third party.

I was unable to open the macro files and got plenty of errors on the xlsx file.
I doubt most people will open a xlsm file anyway (who knows what it will do?)

So you have a daunting task ahead of you and I wish you well with it.

 

[Cheezee]

ok ok thank you,.

doing my research
well it will take time thanks though

 

[Gasman]

You can get a head start by recording a macro for a step you need and then looking at/amending the code to suit.

This also gives you tips on the syntax required.

 

[Gasman]

Here is something I created to use in work.

It goes through rows of an Excel sheet, collects data.
It then creates folders named by the data in the sheet, namely case number.
If creating the sheets is selected it will transfer data from the sheet cells being processed and populate the output sheet with that data and then save the sheet.

This process was done manually in work, but I decided to write it anyway.
We did use it for simply creating the folders a hundred or so at a time.

It is exported code from the sheet in question.

It should give you a bit of a head start?

Attribute VB_Name = "Module1"
'Option Explicit

Option Compare Text
Sub Create_Folders()
Attribute Create_Folders.VB_ProcData.VB_Invoke_Func = " \n14"
'
' Macro to create CG folders and populate CG workbooks
'
Dim CGStart, CGNum, CGReason, strInfile, strOutfile, strPath, strOldFile, strBaseName, strRegion, strRemote As String
Dim CGValue As String, CurrName As String
Dim rng As Range
Dim CGLogged As Date
Dim iCreate As Integer

' Set defaults for macro
strOldFile = "INCIDENT LOG SHEET.xlsx"
strPath = "C:\test\"
strRemote = "\\RemotePath\"
strBaseName = strOldFile
CGSheet = "Sheet1"
CurrName = ThisWorkbook.Name

'Get required start ref
    CGStart = Application.InputBox(Prompt:="Enter CG Start Ref", Type:=2)
    ' CGSheet = Application.InputBox(Prompt:="Sheet Name?", Type:=2)
    iCreate = MsgBox("Create Excel Sheets?", vbYesNo)
    strRegion = Application.InputBox(Prompt:="(L)ocal or (R)emote path to create", Type:=2)
' Choose which path to use
    If Left(strRegion, 1) = "R" Then
        strPath = strRemote
    End If
    
    
    Range("A1").Select
        Cells.Find(what:=CGStart, after:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    If ActiveCell.Value <> CGStart Then
        MsgBox "Value not found", vbExclamation
        Exit Sub
    End If
    ' Switch off screen movement
    Application.ScreenUpdating = False
    ' Now open the template file if we are creating sheets
    If iCreate = vbYes Then
        Workbooks.Open Filename:=strPath & strOldFile
        'Now go back to Master Extract workbook
        Workbooks(CurrName).Sheets(CGSheet).Activate
    End If
        
    
    Do Until ActiveCell.Value = "" Or ActiveCell.Value = 0
        CGNum = ActiveCell.Value
        ActiveCell.Offset(0, 1).Activate
        CGValue = ActiveCell.Value
        ActiveCell.Offset(0, 2).Activate
        CGReason = ActiveCell.Value
        ActiveCell.Offset(0, 1).Activate
        CGLogged = ActiveCell.Value
    '   MsgBox (CGNum & " " & CGValue & " " & CGReason & " " & CGLogged)
    ' Now create the folder
    If Not FileFolderExists(strPath & CGNum) Then
        MkDir (strPath & CGNum)
    End If
    ' Now go to opened template file and relevant sheet if we are creating the files
    If iCreate = vbYes Then
        ' Sometime selecting the sheet works, other times it does not, so remove for now and have only one sheet
        Workbooks(strOldFile).Activate
        ' Now set the values
        Range("B1").Value = CGNum
        Range("B2").Value = CGReason
        Range("E3").Value = CGValue
        Range("H1").Value = CGLogged
        Range("A6").Value = CGLogged
        ' Now create the name to save as
            strOutfile = strPath & CGNum & "\" & CGNum & " " & strBaseName
        ' And save the file
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs Filename:=strOutfile, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        Application.DisplayAlerts = True
        strOldFile = CGNum & " " & strBaseName
    End If
        'Now go back to Master Extract workbook
        Workbooks(CurrName).Sheets(CGSheet).Activate
        ' Get back to column A and down a row
        ActiveCell.Offset(1, -4).Activate
    
Loop

    If iCreate = vbYes Then
        Workbooks(strOldFile).Close
    End If
    'Switch Screen updating back on
    Application.ScreenUpdating = True
    
    If Left(strRegion, 1) <> "R" Then
        MsgBox "Macro completed, please now move the folders to required location", vbExclamation
      Else
         MsgBox "Macro completed, Folders created at remote location", vbExclamation
    End If

' Clear variables
    End
     
End Sub

Public Function FileFolderExists(strFullPath As String) As Boolean
' Check if file or folder exists
    On Error GoTo EarlyExit
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then
        FileFolderExists = True
    End If
EarlyExit:
    On Error GoTo 0
End Function

 

[arnelgp]

this is made in ms access, please try if this will help you.