Macro doesn’t work after a table is created.

[Kimston]

For somereasons, the macro in the TimeandAttendance files does not seem to copy theProductionDate, Supervisor, Department and Shift after creating a table. Icreated this table to so I don’t have to be updating the table rangeconstantly. Is there any other way I can accomplish this task? Can this excelfiles be converted to MS Access tables? Any help would be greatly appreciate. This is my first time posting a thread in this forum. I don't know if it's possible to upload more than one file a a time. Here is the code that I am using in the TimeandAttendance files
Option Explicit
Dim MFile As String
Dim lastrow As Long
Dim frow As Long
Dim lrow As Long
Dim i As Integer










Sub update_master()
Application.DisplayAlerts = False
'Change the MasterFile Path
MFile = "C:\MasterFile.xls"






For i = Workbooks.Count To 1 Step -1
If Workbooks(i).Name = "MasterFile.xls" Then
MFile = "MasterFile.xls": GoTo Opened: End If: Next i

Workbooks.Open MFile
MFile = ActiveWorkbook.Name

Opened:






lastrow = ThisWorkbook.Worksheets("TimeAndAttendance").Range("A" & Rows.Count).End(xlUp).Row
ThisWorkbook.Worksheets("TimeAndAttendance").Range("A9:H" & lastrow).Copy _
Workbooks(MFile).Worksheets("Master").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)






frow = Workbooks(MFile).Worksheets("Master").Range("K" & Rows.Count).End(xlUp).Row
lrow = Workbooks(MFile).Worksheets("Master").Range("B" & Rows.Count).End(xlUp).Row






Workbooks(MFile).Worksheets("Master").Range("J" & frow + 1 & ":J" & lrow).Value = ThisWorkbook.Worksheets("TimeAndAttendance").Range("B3").Value
Workbooks(MFile).Worksheets("Master").Range("K" & frow + 1 & ":K" & lrow).Value = ThisWorkbook.Worksheets("TimeAndAttendance").Range("B4").Value
Workbooks(MFile).Worksheets("Master").Range("L" & frow + 1 & ":L" & lrow).Value = ThisWorkbook.Worksheets("TimeAndAttendance").Range("B5").Value
Workbooks(MFile).Worksheets("Master").Range("A" & frow + 1 & ":A" & lrow).Value = ThisWorkbook.Worksheets("TimeandAttendance").Range("B6").Value
Workbooks("MasterFile.xls").Close SaveChanges:=True
'Application.Quit
End Sub

[GTO]

Greetings Kimston,

Welcome to vbaexpress :-)

I am headed out to work at the moment, so no real help, but regarding your one question... If you have a couple of files interacting and want to post examples of each - you can zip them and attach the zip.

Mark

[Kimston]

Greetings Kimston,

Welcome to vbaexpress :-)

I am headed out to work at the moment, so no real help, but regarding your one question... If you have a couple of files interacting and want to post examples of each - you can zip them and attach the zip.

Mark

Thanks for the quick response, Mark. I have included the MasterFile and the TimeandAttendance file so anyone can see them. Again, thanks for your tip on how to upload more than one file at a time.

[GTO]

Greetings Kimston,

I may be (actually, I am sure I am) too tired to be responding, but hopefully, you are forgiving of a response that could be taken negatively. That is not my intent at all, and I hope that you will accept that this is meant to save you some headaches.

I am off to bed, so I failed to really try and understand your code, vs. what you have included in questioning. That said, I would ask that you articulate in plain English, what it is taht we are trying to do.

As to your present code, this is what I wish to communicate in a positive manner. In reading through it, I can tell that you are making sincere efforts. But, and you will just have to decide to trust me on this: you are picking up some bad techniques. Please believe me in relaying that the first bits of code I wrote were FAR worse. For me, it was in PerfectScript (my memory is shady, but that is what I recall WordPerfect using), and I wouldn't have had a clue as to typing a variable. EVERYTHING was a variant when I got done with it :-( And I wouldn't have known a loop if it bit me in the ass. Nor did I realize that there were great sites like this, where folks would help. So, you are miles ahead of where I was, so please take the following in the spirit intended:


Application.DisplayAlerts = False
'Change the MasterFile Path

Yuck. You have that at the start, and never change it back. 'On Error Resume Next' and shutting off Alerts are bad ideas, excepting under tightly controlled bits of code. Whereas ignoring an error and checking to see if one occurred, or, shutting off Alerts while deleting a sheet may be effective and a good idea, masking errors or killing the built-in warnings for the duration are not. As far as I can see (admittedly, quickly read), you never turn Alerts back on. If that is correct, the user may do things that they did not intend.


For i = Workbooks.Count To 1 Step -1
If Workbooks(i).Name = "MasterFile.xls" Then
MFile = "MasterFile.xls": GoTo Opened: End If: Next i

There are quicker ways to see if a workbook is open, but for the moment, let's tackle what is going to frustrate you a few months down the road, when you need to edit/update what you have written. Ditch the ":"'s and use indentation. It is code; you are not printing it and saving trees by running lines together. Rather, it just becomes much harder to decipher later.

'GoTo': Well... I personally am not "against" GoTo's, but only if no other way seems sensible. 'Exit For' would be great, except for this:


Workbooks.Open MFile

Please read the Help topic on 'Set'.

Warning: "Air Code"

Not tested at all, but I think the first part might be better if something like:

Sub UpdateMaster_02()
Dim WB As Workbook

'// Shut off error handling only long enough to see if setting a reference happened. //
'// If it failed, the wb does not exist in this instance's workbook collection. //
On Error Resume Next
Set WB = Workbooks("MasterFile.xls")
On Error GoTo 0

'// If 'WB' IS Nothing, then it doesn't exist yet, so Open it. //
If WB Is Nothing Then
Set WB = Workbooks.Open("C:\MasterFile.xls")
End If

MsgBox WB.FullName

End Sub

Hope that is a start :-)

Mark

[snb]

or simply:


sub snb()
with Getobject("C:\MasterFile.xls")
- - - - - - - -
end with
end sub


@GTO

It's no use resetting displayalerts, because in Excel <2007 ending the macro defaults to True.

[Kimston]

Good morning Mr. GTO,
I finally was able to respond back to your post. Unfortunately,I have been very busy at work. First of all, I’d like to thank you for sharingyour knowledge and trying to understand me. I’d not take your comments negativelyor personal because if I do, then, I won’t grow or learn more about VBA. I amhere seeking help from the experts who can share their knowledge with peoplelike me who have barely any understanding of how VBA works. However, I am eagerto learn and be humble enough to take any comments. My original intention withthese two files was to be able to copy data from TimeandAttendane file(s) intoMasterFile. Once data was collected into the MasterFile, I could analyzetrends. If you run the file TimeandAttendance, you will notice that informationgets copy from (A9:H50) as well as data from (B3:B6). The problem that Istarted facing was that if the MasterFile was opened or used by another user at the time another user wantedto send and save data, his/her data would be lost. In addition to that, theonly reason I used the Application.DisplayAlerts = False at the beginning ofthe procedure was because there are items from a data validation list from(H9:H50). I wanted to avoid confusing the users when they see the error: "A formula or sheet you want to move orcopy contains the name 'AttendanceCodes', which already exists on thedestination worksheet.
In addition to that, I added this code For i = Workbooks.Count To 1 Step -1
If Workbooks(i).Name = "MasterFile.xls" Then
MFile = "MasterFile.xls": GoTo Opened: End If: Next ito make sure data was
saved in the MasterFile. However, I got confused to where I should use your code. Sub UpdateMaster_02()
Dim WB As Workbook

'// Shut off error handling only long enough to see if setting a reference happened. //
'// If it failed, the wb does not exist in this instance's workbook collection. //
On Error Resume Next
Set WB = Workbooks("MasterFile.xls")
On Error Goto 0

'// If 'WB' IS Nothing, then it doesn't exist yet, so Open it. //
If WB Is Nothing Then
Set WB = Workbooks.Open("C:\MasterFile.xls")
End If

MsgBox WB.FullName

End Sub
I tried to use it, but it does not copy any data.


I have modified and added another PDF file to explain what Iam trying to accomplish with all of these. Please take a look at the PDF file for better understanding.
Again thank you very much for being patient with me and above all understandable.