Hello,
It has been a while since I have asked a question, but I have a need for the bottom code to be streamlined. This code takes a really long time to run when there are hundreds of rows that I am updating.
I have a workbook with a Sheet Named Working Master and a Sheet Named Temp. The Working Master is the sheet that needs to be updated from the Temp Sheet. Basically, what I am doing is taking data from a separate workbook, pasting it into the Temp Sheet and running the code to look up each value in column A on the Temp sheet to the Working Master Sheet and cop/paste the data from the Temp sheet to the Working Master sheet. Where this code gets really clunky and needs some serious help is that I have multiple offset cells that I copy/paste one by one to the Working Master. I have used this code on multiple other projects over the years and the current project that I am modifying the code for, I need to copy/paste Range(E:DX) for each unique value in Column A on the Temp sheet to the Working Master sheet. I have to use the lookup function on the Working Master as the rows are not in the same order between both sheets.
Here is the code that I have been using:
I am open to updating this or a completely new way of doing this.
I thank everyone who will take a look at this and give me input. I do truly appreciate it.
It has been a while since I have asked a question, but I have a need for the bottom code to be streamlined. This code takes a really long time to run when there are hundreds of rows that I am updating.
I have a workbook with a Sheet Named Working Master and a Sheet Named Temp. The Working Master is the sheet that needs to be updated from the Temp Sheet. Basically, what I am doing is taking data from a separate workbook, pasting it into the Temp Sheet and running the code to look up each value in column A on the Temp sheet to the Working Master Sheet and cop/paste the data from the Temp sheet to the Working Master sheet. Where this code gets really clunky and needs some serious help is that I have multiple offset cells that I copy/paste one by one to the Working Master. I have used this code on multiple other projects over the years and the current project that I am modifying the code for, I need to copy/paste Range(E:DX) for each unique value in Column A on the Temp sheet to the Working Master sheet. I have to use the lookup function on the Working Master as the rows are not in the same order between both sheets.
Here is the code that I have been using:
VBA Code:
Sub FindKey()
'/////////////////////////////////////////////////////////////////////////
'Sheets used
'ImportTemp (Sheet where the temp data is housed to copy over values from
'Working Master (WorkingMaster) Main Sheet where data is being copied to
'/////////////////////////////////////////////////////////////////////////
Application.ScreenUpdating = False
Dim lookVal As String
Temp.Select
Range("A2").Select 'Temp Sheet for copying values over to Missing AC Sheet
Do Until IsEmpty(ActiveCell.Value)
lookVal = ActiveCell.Value 'Key# from Column A
WorkingMaster.Select 'Main Sheet that is being Updated
Cells.Find(What:=lookVal, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range("A" & (ActiveCell.Row)).Select
Temp.Select
ActiveCell.Offset(0, 4).Copy
WorkingMaster.Select 'Main Sheet that is being Updated
ActiveCell.Offset(0, 4).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
:=False, Transpose:=False
Temp.Select
ActiveCell.Offset(0, 5).Copy
WorkingMaster.Select 'Main Sheet that is being Updated
ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
:=False, Transpose:=False
Temp.Select
ActiveCell.Offset(0, 6).Copy
WorkingMaster.Select 'Main Sheet that is being Updated
ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
:=False, Transpose:=False
Temp.Select
ActiveCell.Offset(0, 7).Copy
WorkingMaster.Select 'Main Sheet that is being Updated
ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
:=False, Transpose:=False
Temp.Select
ActiveCell.Offset(0, 8).Copy
WorkingMaster.Select 'Main Sheet that is being Updated
ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
:=False, Transpose:=False
Temp.Select
ActiveCell.Offset(0, 9).Copy
WorkingMaster.Select 'Main Sheet that is being Updated
ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
:=False, Transpose:=False
Temp.Select
ActiveCell.Offset(0, 10).Copy
WorkingMaster.Select 'Main Sheet that is being Updated
ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
:=False, Transpose:=False
Temp.Select
ActiveCell.Offset(0, 11).Copy
WorkingMaster.Select 'Main Sheet that is being Updated
ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
:=False, Transpose:=False
Temp.Select
ActiveCell.Offset(0, 12).Copy
WorkingMaster.Select 'Main Sheet that is being Updated
ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
:=False, Transpose:=False
Temp.Select
ActiveCell.Offset(0, 13).Copy
WorkingMaster.Select 'Main Sheet that is being Updated
ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
:=False, Transpose:=False
Temp.Select
ActiveCell.Offset(0, 14).Copy
WorkingMaster.Select 'Main Sheet that is being Updated
ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
:=False, Transpose:=False
Temp.Select
ActiveCell.Offset(0, 15).Copy
WorkingMaster.Select 'Main Sheet that is being Updated
ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
:=False, Transpose:=False
Temp.Select
ActiveCell.Offset(0, 16).Copy
WorkingMaster.Select 'Main Sheet that is being Updated
ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
:=False, Transpose:=False
Temp.Select
ActiveCell.Offset(0, 17).Copy
WorkingMaster.Select 'Main Sheet that is being Updated
ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
:=False, Transpose:=False
Temp.Select
ActiveCell.Offset(0, 18).Copy
WorkingMaster.Select 'Main Sheet that is being Updated
ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
:=False, Transpose:=False
Temp.Select
ActiveCell.Offset(0, 19).Copy
WorkingMaster.Select 'Main Sheet that is being Updated
ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks _
:=False, Transpose:=False
Temp.Select
ActiveCell.Offset(1, 0).Select
Loop
WorkingMaster.Select
Application.ScreenUpdating = True
End Sub
I am open to updating this or a completely new way of doing this.
I thank everyone who will take a look at this and give me input. I do truly appreciate it.