I am copying from one file to an other file, and I can see Excel switching between the source and the target file (like flickering). I want the macro to copy from the source and paste to target WITHOUT switching between the files (I DON'T want flickering).
So here I got my Excel VBA code. I set up a Button to run 2 macros. The first macro is an openfiledialog where I can choose a file to be opened. That is required because the needed file always got a different name and is in a different path.
After opening the file my second macro starts (CopyPasteValues), the one I posted below. At first I set my source and target Workbooks/Worksheets and create an array with 16 account numbers.
Then I use the find method to search for the account number in each file (source and target). The result of the find method is used to create an offset in the src file and copy it to an offset in the target file.
Sub CopyPasteValues()
Dim srcWb As Workbook 'source Wb
Dim srcWs As Worksheet 'source Ws
Dim trgWb As Workbook 'target Wb
Dim trgWs As Worksheet 'target Ws
Set trgWb = ActiveWorkbook
Set trgWs = trgWb.Sheets("Entry Sheet 20004100")
Set srcWb = Workbooks.Open(Filename:=openedFile, UpdateLinks:=False, ReadOnly:=True, Editable:=False)
Set srcWs = srcWb.Sheets("20004100")
Dim GLAccountField
'Array of 16 Account numbers
GLAccountField = Array(430000, 446030, 477030, 474210, 446075, 472700, 472710, 476000, 476100, 476610, 452200, 454700, 471300, 473110, 490000, 490710)
Dim srcFinder As Range, trgFinder As Range
Dim searchGL As Long
Dim srcRng As Range, trgRng As Range
Dim i As Integer
For i = LBound(GLAccountField) To UBound(GLAccountField)
'The range where GL Accounts will be searched
Set srcRng = srcWs.Range("A1:A100") 'source file
Set trgRng = trgWs.Range("B10:B900") 'target file
'search for the account number(i) in source and target sheets
searchGL = GLAccountField(i)
Set srcFinder = srcRng.Find(searchGL, Lookat:=xlWhole, LookIn:=xlValues, MatchCase:=True)
Set trgFinder = trgRng.Find(searchGL, Lookat:=xlWhole, LookIn:=xlValues, MatchCase:=True)
'If finder value equals searched Account Number, then paste to target
If srcFinder Is Nothing Then
MsgBox "GL Account: " & searchGL & " NOT found in 'Accounting Input' file"
Else
'copy from source
srcFinder.Offset(0, 15).Resize(1, 12).Copy
'paste to target from source
trgFinder.Offset(1, 4).Resize(1, 12).PasteSpecial xlPasteValues
End If
Next i
srcWb.Close
End Sub
If you're experiencing high CPU usage, Excel might crash due to insufficient resources. This usually happens if you've added animations to your spreadsheet. To fix it, you should disable hardware graphics acceleration. Besides keeping Excel from freezing or crashing, it will improve the overall performance.
(Answered in comments by Andy G):
Use Application.ScreenUpdating = False
at the start of your sub. Remember to set it back to True
at the end (also good practice to do this in an error handler so it gets reset even in the event of an error):
Sub foo()
On Error Goto errHandler
Application.ScreenUpdating = False
'Your code here
Application.ScreenUpdating = True
errHandler:
Application.ScreenUpdating = True
End Sub
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With