VBA code check, and help

[tcfreer]

Hello all

I have written the following code to append new data into a sheet from another sheet.

The code is as follows

Sub AppendEPOS()
'
' AppendEPOS Macro
' Copy Data from the Converter into the EPOS
'
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Sheet As Worksheet
Dim PasteStart As Range
Set wb1 = ActiveWorkbook
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse")
If FileToOpen = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub
Else
    Set wb2 = Workbooks.Open(Filename:=FileToOpen)
    wb2.Sheets("EPOS Sales Copy sheet").Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    wb2.Close
    
    Sheets("EPOS Sales").Select
Dim lastRow1 As Long
    lastRow1 = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & lastRow1 + 1).Select
    Selection.Paste
    Application.CutCopyMode = False
End If
Dim lastRow2 As Long
    lastRow2 = Range("C" & Rows.Count).End(xlUp).Row
    Range("D2:Q2").Select
    Selection.AutoFill Destination:=Range("D2:Q" & lastRow2)
    Calculate
    
Sheets("Control").Select
    Range("E11").Select
    ActiveCell.FormulaR1C1 = "=NOW()"
    Range("E11").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
End Sub

I have a couple of questions

1 - Is this code acceptable or is there any improvements that could be made?

2 - I also need to tweak this code, as it now seems that some of the data that is to be brought in will not be available at the same time. Is there a simple check / count that can be used to validate each line and the number in the 3rd column, if its greater on the data that's being copied in then it gets applied and that the date matches as well

Not sure if that makes sense, I am also researching the later part so I can add more detail as I go

Cheers for any help

TC

 

[JackDanIce]

#1 With suggested improvements, try:

Sub AppendEPOS_v1()

    Dim wkb     As Workbook
    Dim arr()   As Variant
    Dim LR      As Long
    Dim LC      As Long
    Dim str     As String
    
    str = Application.GetOpenFilename(Title:="Please choose a Report to Parse")
    
    If str = False Then
        MsgBox "No File Specified.", vbExclamation, "ERROR"
        Exit Sub
    End If
    
    Set wkb = Workbooks.Open(str, ReadOnly:=True)
    ThisWorkbook.Activate
    
    Application.ScreenUpdating = False
    
    With wkb
        With .Sheets("")
            LR = .Cells(.Rows.Count, 1).End(xlUp).row
            LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
            arr = .Cells(1, 1).Resize(LR, LC).Value
        End With
        .Close False
    End With
    
    With Sheets("EPOS Sales")
        LR = .Cells(.Rows.Count, 1).End(xlUp).row + 1
        .Cells(LR, 1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
        LR = .Cells(.Rows.Count, 3).End(xlUp).row - 1
        .Cells(2, 4).Resize(, 17).AutoFill Destination:=.Cells(2, 4).Resize(LR, 17)
    End With
    
    With Sheets("Control")
        .Cells(11, 5).Value = Format(Now, "DDDD DD / MMM /YYYY")
        If ActiveSheet.Name <> .Name Then .Select
    End With
        
    Application.ScreenUpdating = True
    
    Erase arr
    Set wkb = Nothing
    
End Sub

#2 Unclear what you're asking, can you provide specific examples?

 

[tcfreer]

Hi Jack

Nice, change makes more sense then my original

Ok so Part 2

What basically happens is that we have a file that has 3 rows of data, which update based on another sheet within the workbook that it is held with in.

There is always 3 columns

The number of rows, may change - but I think I handle this already.

However the data that feeds this file is not always in on the same time.

So for some of the rows, the 2nd column will have a either a 0 or last weeks number.

This is fine for the sheet that we get the data from

However, when we pull that data across and append it to the bottom of the data in the workbook that holds this macro, instead of just simply appending the data, it needs to check if that week already exists, if not just run as normal, if it does to over right all the rows with the corresponding week on

Input is as follows

[TABLE="width: 242"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]Sales[/TD]
[TD]Week Ref[/TD]
[/TR]
[TR]
[TD]5833603[/TD]
[TD]3,413 [/TD]
[TD]02/05/2016[/TD]
[/TR]
</tbody>[/TABLE]

That format is identical to the sheet it lands in as well.

Does that make sense?

 

[JackDanIce]

I'm afraid not, need more precise examples for both date match and date does not match.

You say for some of the rows the 2nd column with have either a 0 or last week's number but your input example suggests week refs are column 3.

I have no idea what data is in the Combined sheet, what format it is in and where the comparison of date values is made.

I'm guessing this is due to not seeing your screen so what maybe obvious to you is guesswork to anyone reading?

 

[tcfreer]

Yes, apologies.

Let me try and break it down

I have 3 columns of data, as you see above

Column 1 has the product ref number in it - this is unique to each product
Column 2 has the volume in it - this is a variable number dependent upon performance of the week
Column 3 has the Week ref with in it - this is always the Monday of the previous week.

The Product column has differing formats but the individual item ref within them, as mentioned above is unique to each row and checkable

The Week Ref for each row, changes each week and is unique for that weeks data set.

The Sales column is a variable, that changes week on week dependent upon product performance.

The code appends these 3 columns from a pre set tab within the first workbook, into this workbook, on to the tab mentioned in the code

The columns - A.B and C - are identical in both tabs

The append does a copy and paste values to break links.

What I need to evolve on the vba, is a way for the append to do a check against the Week and then go one of 2 ways

Way 1 if the week ref doesn't exist, complete the simple append, copy and paste vales

Way 2 if the week ref exists, overwrite them with the details being pasted in.

The reason behind this, is that we receive 5 files in - and they come in at differing points during the day/week sometimes.

I want to be able to allow the data to be moved forward, without holding up due to no data being available.

Is this a better explanation? Apologies for making a hash of it before

 

[JackDanIce]

Sorry still not clear. Firstly though, I'm assuming the suggested code works - changes may make more sense than the original, but that's not the same!

Let's try this:

Manually, you would copy (e.g.) Range A1:C10 from the first sheet of the input file into Combined Sheet and when it's the first time after sheet is created, you copy A1:C10 to B3:D12 on sheet Combined.

Next, are you saying you want to loop through C3:C12 on Combined and compare that date value to what? You say week but without clarifying what, are you comparing the week number to week number? The actual date to actual date?

Still unable to see your screen, if you can post examples of:

"This is what it looks like after I paste into Combined sheet"
"If the date in C3 = today's date then I want to do this"
"If the date in C3 <> today's date, then I want to do this"
"This needs to repeat for all values in column C, starting C3"

You don't need to explain why it's doing what it's doing or it's purpose or why, just be precise about the steps you want coded. It may help bring clarity...