Spending some time with my code I realized I need your help. The problem is following. I have a file with VBA code in it (ThisWorkbook) and i'm trying to open another excel book to copy data from three different sheets there and paste into my excel in thee different sheets either. Copying the data from first two sheets goes flawlessly but there is problem with the third one. The interest thing is that when I try do my job in debug mode, everything goes right, but when i run macro the problem exists.
Here is my code:
Option Explicit
Const NORMAT_START_POINT = 3 'data starts from this row after header
Const PA_MAN_ADJ_START_POINT = 9
Sub copy_paste_data_to_checklist()
Dim FileToOpen As Variant
Dim WorkingFile As Workbook
Dim countCompanies As Long
Dim period As Variant 'month and year for copy paste the table header
Dim no_sent_reports_count As Long
countCompanies = licenses.Range("A1").End(xlDown).Row
period = fzp_nzp_pa.Range("Q1").Value
'On Error GoTo handle
no_sent_reports_count = VBA.InputBox("How many companies didn't provide reports for
previous quarter?", "Enter data")
'On Error GoTo 0
Call EntryPoint
FileToOpen = Application.GetOpenFilename(FileFilter:="Excel files(*.xlsb), *.xlsb", Title:="Select a file to import data from")
'clear data before pasting new values
fzp_nzp_pa.Range("A3", "M" & countCompanies + NORMAT_START_POINT).ClearContents
nro_nja_normat.Range("A3", "L" & countCompanies + NORMAT_START_POINT).ClearContents
'open file with nornativ
'''''''''''''''''''''''''''''''''''''''''''''copy paste fzp_nzp_pa'''''''''''''''''''''''''''''''''''''''
If FileToOpen <> False Then
Set WorkingFile = Workbooks.Open(FileToOpen)
WorkingFile.Application.Calculation = xlCalculationManual
If isWorkbookOpen(WorkingFile.Name) = True Then
'Application.Wait (Now + TimeValue("0:00:30"))
WorkingFile.Worksheets("Íîðìàòèâû").Range("A3", "B" & countCompanies + NORMAT_START_POINT).Copy
fzp_nzp_pa.Range("A3").PasteSpecial xlPasteValues
'fzp_nzp
WorkingFile.Worksheets("Íîðìàòèâû").Range("F3", "G" & countCompanies + NORMAT_START_POINT).Copy
fzp_nzp_pa.Range("C3").PasteSpecial xlPasteValues
WorkingFile.Worksheets("Íîðìàòèâû").Range("K3", "K" & countCompanies + NORMAT_START_POINT).Copy
fzp_nzp_pa.Range("E3").PasteSpecial xlPasteValues
WorkingFile.Worksheets("Íîðìàòèâû").Range("Q3", "Q" & countCompanies + NORMAT_START_POINT).Copy
fzp_nzp_pa.Range("F3").PasteSpecial xlPasteValues
WorkingFile.Worksheets("Íîðìàòèâû").Range("V3", "V" & countCompanies + NORMAT_START_POINT).Copy
fzp_nzp_pa.Range("G3").PasteSpecial xlPasteValues
WorkingFile.Worksheets("Íîðìàòèâû").Range("M3", "N" & countCompanies + NORMAT_START_POINT).Copy
fzp_nzp_pa.Range("H3").PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("Z9", "Z" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
fzp_nzp_pa.Range("J3").PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AO9", "AO" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
fzp_nzp_pa.Range("K3").PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AV9", "AV" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
fzp_nzp_pa.Range("L3").PasteSpecial xlPasteValues
WorkingFile.Worksheets("Íîðìàòèâû").Range("S3", "S" & countCompanies + NORMAT_START_POINT).Copy
fzp_nzp_pa.Range("M3").PasteSpecial xlPasteValues
'''''''''''''''''''''''''''''''''''''''''copy paste nro_nja_normat'''''''''''''''''''''''''''''''''''''''
WorkingFile.Worksheets("Íîðìàòèâû").Range("A3", "B" & countCompanies + NORMAT_START_POINT).Copy
nro_nja_normat.Range("A3").PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AA9", "AA" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_normat.Range("C3").PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AB9", "AB" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_normat.Range("D3").PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AC9", "AC" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_normat.Range("E3").PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AF9", "AF" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_normat.Range("F3").PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AF9", "AF" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_normat.Range("F3").PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AG9", "AG" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_normat.Range("G3").PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AL9", "AL" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_normat.Range("H3").PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AP9", "AP" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_normat.Range("I3").PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AS9", "AS" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_normat.Range("J3").PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AT9", "AT" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_normat.Range("K3").PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AU9", "AU" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_normat.Range("L3").PasteSpecial xlPasteValues
''''''''''''''''''''''''''''''''''''''''copy paste to nro_nja_archive''''''''''''''''''''''''''''''''''''
'copy the header
nro_nja_archive.Range("A2:S2").Copy
'paste it 5 rows below the last found non-empty cell
nro_nja_archive.Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 5 + no_sent_reports_count).PasteSpecial xlPasteAllUsingSourceTheme
'insert period date above the header in column A
fzp_nzp_pa.Range("Q1").Copy
nro_nja_archive.Range("A" & Range("A" & Rows.Count).End(xlUp).Row - 1).PasteSpecial xlPasteValues
'nro_nja_archive.Range("A" & Range("A" & Rows.Count).End(xlUp).Row - 1).Value = period
nro_nja_archive.Range("A" & Range("A" & Rows.Count).End(xlUp).Row - 1).Interior.Color = VBA.RGB(158, 28, 176)
nro_nja_archive.Range("A" & Range("A" & Rows.Count).End(xlUp).Row - 1).Font.Color = vbWhite
'copy paste data to nro_nja_archive
WorkingFile.Worksheets("Íîðìàòèâû").Range("A3", "B" & countCompanies + NORMAT_START_POINT).Copy
nro_nja_archive.Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AO9", "AO" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_archive.Range("C" & Range("C" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
WorkingFile.Worksheets("Íîðìàòèâû").Range("Q3", "Q" & countCompanies + NORMAT_START_POINT).Copy
nro_nja_archive.Range("D" & Range("D" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AB9", "AB" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_archive.Range("E" & Range("E" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AF9", "AF" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_archive.Range("F" & Range("F" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AA9", "AA" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_archive.Range("G" & Range("G" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AL9", "AL" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_archive.Range("H" & Range("H" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AG9", "AG" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_archive.Range("I" & Range("I" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AC9", "AC" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_archive.Range("J" & Range("J" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AV9", "AV" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_archive.Range("K" & Range("K" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
WorkingFile.Worksheets("Íîðìàòèâû").Range("V3", "V" & countCompanies + NORMAT_START_POINT).Copy
nro_nja_archive.Range("L" & Range("L" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AP9", "AP" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_archive.Range("M" & Range("M" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AS9", "AS" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_archive.Range("N" & Range("N" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AU9", "AU" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_archive.Range("O" & Range("O" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
WorkingFile.Worksheets("ÏÀ_manual_adj").Range("AT9", "AT" & countCompanies + PA_MAN_ADJ_START_POINT).Copy
nro_nja_archive.Range("P" & Range("P" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
WorkingFile.Worksheets("Íîðìàòèâû").Range("W3", "Y" & countCompanies + NORMAT_START_POINT).Copy
nro_nja_archive.Range("Q" & Range("Q" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
WorkingFile.Close False
MsgBox "Done!", vbInformation, "Task completed"
End If
End If
Call ExitPoint
' Exit Sub
'handle:
End Sub
Sub EntryPoint()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
.StatusBar = "Winter is coming"
End With
End Sub
Sub ExitPoint()
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
.CutCopyMode = False
.StatusBar = ""
End With
End Sub
Function isWorkbookOpen(WorkbookName As String) As Boolean
Dim wb As Workbook
On Error Resume Next
Set wb = Application.Workbooks(WorkbookName)
On Error GoTo 0
If wb Is Nothing Then
isWorkbookOpen = False
Else
isWorkbookOpen = True
End If
End Function
The problem starts from this block :
''''''''''''''''''''''''''''''''''''''''copy paste to nro_nja_archive''''''''''''''''''''''''''''''''''''
It should take data from opened file and insert it below the last rows with data. It seems like file isn't loaded fully when the code executes, since data are being pasted anywhere but not below the last row. Again, when in debug mode everything is working just fine... I need somehow make the vba code wait until another excel file is fully loaded before performing copy paste.
Any help will be much appreciated!