+ Reply to Thread
Results 1 to 8 of 8

Run - Time error 1004 => When macro run half way

Hybrid View

Orca_ng Run - Time error 1004 => When... 08-04-2013, 09:31 PM
wenqq3 Re: Run - Time error 1004 =>... 08-04-2013, 11:59 PM
xladept Re: Run - Time error 1004 =>... 08-05-2013, 12:16 AM
Orca_ng Re: Run - Time error 1004 =>... 08-05-2013, 12:27 AM
wenqq3 Re: Run - Time error 1004 =>... 08-05-2013, 12:31 AM
Orca_ng Re: Run - Time error 1004 =>... 08-05-2013, 12:42 AM
wenqq3 Re: Run - Time error 1004 =>... 08-05-2013, 02:03 AM
xladept Re: Run - Time error 1004 =>... 08-05-2013, 03:06 PM
  1. 08-04-2013, 09:31 PM #1
    Orca_ng is offline
    Registered User
    Join Date
    06-12-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    3

    Run - Time error 1004 => When macro run half way

    Hi,

    I'm new to this forum and need help urgently.
    Hope someone could help me.
    I'm not expert in Macro, and the file was created by another PIC.
    Need to resolve this issue asap as other employee is waiting to view their balance

    I had a macro to collect all data from 80 plus workbook and it will copy and paste into summary.

    It works for first 6 workbook. But Run time error pop up after 6th workbook.
    Have try to search for answer, but just could not find one to resolve for mine.

    Below is my code.

    Please help...

    Public Sub Collect_Books_From_G()

    'MsgBox "Collect"

    'EXCEL‚ðˆêŽž‰B‚·
    'Application.Visible = False
    'ƒGƒ‰[‚ð•\Ž¦‚µ‚È‚¢
    Application.DisplayAlerts = False
    '‰æ–Ê‚ðXV‚µ‚È‚¢
    Application.ScreenUpdating = False

    'Œ»Ý‚̃tƒ@ƒCƒ‹‚Ì–¼‘O‚ðŽæ“¾
    CurrentName = ThisWorkbook.Name

    'On Error GoTo HandleErr

    '’T‚µ‚½‚¢ƒtƒHƒ‹ƒ_‚̃pƒX
    'StrPath = "K:\21.Will\Test2\"
    'StrPath = "\\10.1.203.7\"
    'StrPath = "\\Fthqst04\dptfia\04 ŠÇ—‰ïŒvƒ`[ƒ€\05 ŒÂlƒtƒHƒ‹ƒ_\negi\DHxŒÂlWill\"
    StrPath = Workbooks(CurrentName).Sheets("Datas").Cells(4, 2).Value '2013.5.13 NE’ljÁ
    'Dir‚ŁuƒpƒXv“à‚ÌExcelƒtƒ@ƒCƒ‹‚ðˆêŒÂ‚¸‚Â’T‚µ‚āAstrFileName‚É‘ã“ü‚µ‚Ü‚·B
    'StrFileName = Dir(StrPath & "*.xls", vbNormal)

    'ŽÐˆõƒŠƒXƒg‚̐l”
    NameCnt = Sheets("WillEmpList").Range("B1").CurrentRegion.Rows.Count
    'ƒRƒs[Š®¬/“à—e‚È‚µ/’*•ë‚È‚µ‚̃JƒEƒ“ƒ^[
    CopyOKCnt = 3
    BookEmptyCnt = 3
    NoWillCnt = 3

    DateNum = Format(Now, "yyyy/mm/dd hh:mm")
    Sheets("Summary").Range("N2") = DateNum

    'Will’*•ë‚É•‚*ŒŽ•ÊF([2013-01])
    StrMonth = Workbooks(CurrentName).Sheets("Datas").Range("B1")

    'ŽÐˆõ‚ð‡”Ô‚ÉŠm”F‚·‚é
    For i = 2 To NameCnt

    'Gƒhƒ‰ƒCƒu‚Ì–¼Ì
    StrFileFolder = Workbooks(CurrentName).Sheets("WillEmpList").Range("F" & i)
    'Will’*•ë‚É•‚*–¼‘OF(Aerion)
    StrFileName = Workbooks(CurrentName).Sheets("WillEmpList").Range("H" & i)

    '######Will’*•ë‚̃AƒhƒŒƒX‚Æ–¼ÌI######
    'StrFileAll = StrPath & StrFileFolder & "\Will_Book" & StrFileName & "_Ver2.0.xls"
    'StrFileAll = StrPath & StrFileFolder & "\Will_Book" & StrFileName & "_[2013-01].xls"
    StrFileAll = StrPath & StrFileFolder & "\Will_Book" & StrFileName & "_" & StrMonth & ".xls"
    '######Will’*•ë‚Ì–¼‘OI################
    'StrFileNAMAE = "Will_Book" & StrFileName & "_Ver2.0.xls"
    'StrFileNAMAE = "Will_Book" & StrFileName & "_[2013-01].xls"
    StrFileNAMAE = "Will_Book" & StrFileName & "_" & StrMonth & ".xls"
    '######################################

    'ƒRƒs[Š®¬/“à—e‚È‚µ/’*•ë‚È‚µ‚ð‹L˜^‚·‚éˆ×(–¼‘O‚ƎЈõ”ԍ†)
    StrName = Workbooks(CurrentName).Sheets("WillEmpList").Range("B" & i)
    StrNum = Workbooks(CurrentName).Sheets("WillEmpList").Range("F" & i)

    'B—ñ‚̍Ōãs‚ð’T‚·(‰º‚©‚çŠm”F)
    'NextRow = Workbooks(CurrentName).Sheets("Summary").Range("B65536").End(xlUp).Row + 1
    NextRowB = Workbooks(CurrentName).Sheets("Summary").Range("B65536").End(xlUp).Row + 1
    NextRowG = Workbooks(CurrentName).Sheets("Summary").Range("G65536").End(xlUp).Row + 1
    'MsgBox NextRowB 'NE’ljÁ
    NextRow = NextRowB
    If NextRow < NextRowG Then NextRow = NextRowG

    'ŒÂl’*•ë‚ªGƒhƒ‰ƒCƒu‚É‘¶Ý‚©‚¢‚È‚¢‚©‚ðŠm”F
    If Dir(StrFileAll) <> "" Then
    'MsgBox StrFileAll 'NE’ljÁ
    'ƒCƒxƒ“ƒgˆ—‚𖳌ø‚É
    Application.EnableEvents = False

    'ŽÐˆõƒŠƒXƒg‚̃}ƒXƒ^[ƒtƒ@ƒCƒ‹‚ð[“Ç‚ÝŽæ‚èê—pƒ‚[ƒh]‚Æ[ŠO•”ŽQÆAƒŠƒ‚[ƒgŽQÆXV‚µ‚È‚¢]‚ÅŠJ‚*
    Workbooks.Open (StrFileAll), UpdateLinks:=0, ReadOnly:=True
    'Workbooks.Open (StrFileAll), ReadOnly:=True

    'ƒCƒxƒ“ƒgˆ—‚ð—LŒø‚É
    Application.EnableEvents = True

    'Workbooks.Open Filename:=File, ReadOnly:=True
    'f = Dir(File)

    'ÅŒã‚̕ҏWŽžŠÔ‚ðŽû“¾
    Set FSO = CreateObject("Scripting.FileSystemObject")
    StrLastTime = FSO.GetFile(StrFileAll).DateLastModified
    Set FSO = Nothing

    Windows(StrFileNAMAE).Activate
    'Windows("Will_Book" & StrFileName & ".xls").Activate

    Sheets("Income").Select

    '“à—e‚ð‹L“ü‚µ‚½‚Ì‚©
    If Sheets("Income").Range("A2") = "" And Sheets("Income").Range("B2") = "" And Sheets("Income").Range("C2") = "" And _
    Sheets("Income").Range("D2") = "" And Sheets("Income").Range("E2") = "" And Sheets("Income").Range("F2") = "" And _
    Sheets("Income").Range("G2") = "" And Sheets("Income").Range("H2") = "" And Sheets("Income").Range("I2") = "" And _
    Sheets("Income").Range("J2") = "" And Sheets("Income").Range("K2") = "" And Sheets("Income").Range("L2") = "" Then
    '“à—e‚È‚µ‚Ì—ñ‚É–¼‘O‚ƃtƒHƒ‹ƒ_–¼‚ð‹L“ü
    Workbooks(CurrentName).Sheets("Summary").Range("O" & BookEmptyCnt) = StrName & "-" & StrNum
    '“à—e‚È‚µ‚Ì—ñ‚ð‰º‚ÖˆÚ“®
    BookEmptyCnt = BookEmptyCnt + 1
    'G2‚É“à—e‚ð‹L“ü‚µ‚½‚̏ꍇ
    Else

    With Sheets("Income")
    '<<<<<<B—ñ(Žó•tl‚Ì•”–å)‚̍Ōãs‚ð’T‚µ‚Ä‹L˜^‚·‚é>>>>>>>
    '.Range("B1").End(xlDown).Select
    .Range("B65536").End(xlUp).Select
    RowB = Selection.Row
    '<<<<<<G—ñ(Memo)‚̍Ōãs‚ð’T‚µ‚Ä‹L˜^‚·‚é>>>>>>
    '.Range("F1").End(xlDown).Select
    .Range("H65536").End(xlUp).Select
    RowG = Selection.Row

    RowCnt = RowB
    If RowCnt < RowG Then RowCnt = RowG

    '‘æ2s‚©‚çÅŒãs‚܂ŃRƒs[
    .Range("A2:L" & RowCnt).Copy
    'WŒv•\‚É–ß‚·
    Workbooks(CurrentName).Sheets("Summary").Activate
    'Žg—pÏ‚ݔ͈͂̎Ÿs‚ÌAƒZƒ‹‚ð‘I‘ð‚·‚é
    Workbooks(CurrentName).Sheets("Summary").Range("A" & NextRow).Select
    '"’l"‚¾‚¯“\‚é
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    ActiveWorkbook.Save


    End With

    'ƒRƒs[Š®¬‚Ì—ñ‚É–¼‘O‚ƃtƒHƒ‹ƒ_–¼‚ð‹L“ü
    Workbooks(CurrentName).Sheets("Summary").Range("P" & CopyOKCnt) = StrName & "-" & StrNum
    'ÅŒã•ÒWŽžŠÔ‚ð‹L“ü
    Workbooks(CurrentName).Sheets("Summary").Range("O" & CopyOKCnt) = StrLastTime

    'ƒRƒs[Š®¬‚Ì—ñ‚ð‰º‚ÖˆÚ“®
    CopyOKCnt = CopyOKCnt + 1
    End If

    'ƒNƒŠƒbƒvƒ{[ƒh‚̃Gƒ‰[‘΍ôiƒtƒ@ƒCƒ‹‚ð•Â‚¶‚é‘O‚É’u‚*j
    Excel.Application.CutCopyMode = False
    'ƒZ[ƒu‚µ‚È‚¢‚܂܂ŕ‚¶‚é
    Workbooks(StrFileNAMAE).Close SaveChanges:=False
    'Workbooks("Will_Book" & StrFileName & ".xls").Close SaveChanges:=False
    'ŒÂl’*•ë‚ªGƒhƒ‰ƒCƒu‚É‚¢‚È‚¢‚̏ꍇ
    Else
    '’*•ë‚È‚µ‚Ì—ñ‚É–¼‘O‚ƃtƒHƒ‹ƒ_–¼‚ð‹L“ü
    Workbooks(CurrentName).Sheets("Summary").Range("R" & NoWillCnt) = StrName & "-" & StrNum
    '’*•ë‚È‚µ‚Ì—ñ‚ð‰º‚ÖˆÚ“®
    NoWillCnt = NoWillCnt + 1

    End If

    Next

    'ã‚É–ß‚é
    Range("A2").Select

    'ƒGƒ‰[‚ð•\Ž¦ó‘Ô‚ð–ß‚·
    Application.DisplayAlerts = True
    '‰æ–Ê‚ðXVó‘Ô‚ð–ß
    Application.ScreenUpdating = True
    'EXCEL‚ðÄ‚ÑŒ»‚·
    Application.Visible = True
    'ƒNƒŠƒbƒvƒ{[ƒh‚̃Gƒ‰[‘΍ô‚ð–ß‚·
    Excel.Application.CutCopyMode = True

    'HandleErr:
    ' Exit Sub

    End Sub
    Reply With QuoteRegister To Reply
  2. 08-04-2013, 11:59 PM #2
    wenqq3 is offline
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Run - Time error 1004 => When macro run half way

    Hi Orca_ng,
    When i saw your code, i was lazy to read it. Please Use code tags around code so it is readable.
    Those invalid character ('your own comment) are no readable as well. Can you remove it. Thanks
    -If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".

    -Always upload a workbook before start your question
    To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

    +++ If my answer(s) helped you, please add me reputation by click on * +++
    Reply With QuoteRegister To Reply
  3. 08-05-2013, 12:16 AM #3
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Run - Time error 1004 => When macro run half way

    I can't see why your code works at all - can you describe the situation more fully?
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
    Reply With QuoteRegister To Reply
  4. 08-05-2013, 12:27 AM #4
    Orca_ng is offline
    Registered User
    Join Date
    06-12-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Run - Time error 1004 => When macro run half way

    Hi Wengq3,

    So sorry, still trying to find out how to Use code tags around code. First time user.

    Hi xladept,

    The code did work during my trial run of 4 files. (Able to copy and paste four workbook data into a file name call summarylist.)
    But when I try to run for 80 plus files, the run-time error pop out when the 8th file open.
    And the macro can't work.

    I have 80 files, one file for each staff. Staff will update that workbook. All formats will be the same
    Then I will run a Master summary, this file consist of macros. It will run all macro and collect all data from all 80 plus file, and paste the data into a new file call summarylist.

    So, sorry If I can't explain it in a more understandable manner.

    Thank you
    Reply With QuoteRegister To Reply
  5. 08-05-2013, 12:31 AM #5
    wenqq3 is offline
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Run - Time error 1004 => When macro run half way

    NP Orca_ng.
    HTML Code: 
    rule number 3. Use code tags around code.
    Reply With QuoteRegister To Reply
  6. 08-05-2013, 12:42 AM #6
    Orca_ng is offline
    Registered User
    Join Date
    06-12-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Run - Time error 1004 => When macro run half way 

    Public Sub Collect_Books_From_G()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

CurrentName = ThisWorkbook.Name

StrPath = Workbooks(CurrentName).Sheets("Datas").Cells(4, 2).Value '2013.5.13 NE’ljÁ
NameCnt = Sheets("WillEmpList").Range("B1").CurrentRegion.Rows.Count
CopyOKCnt = 3
BookEmptyCnt = 3
NoWillCnt = 3

DateNum = Format(Now, "yyyy/mm/dd hh:mm")
Sheets("Summary").Range("N2") = DateNum

StrMonth = Workbooks(CurrentName).Sheets("Datas").Range("B1")

For i = 2 To NameCnt

StrFileFolder = Workbooks(CurrentName).Sheets("WillEmpList").Range("F" & i)
StrFileName = Workbooks(CurrentName).Sheets("WillEmpList").Range("H" & i)

StrFileAll = StrPath & StrFileFolder & "\Will_Book" & StrFileName & "_" & StrMonth & ".xls"
StrFileNAMAE = "Will_Book" & StrFileName & "_" & StrMonth & ".xls"
StrName = Workbooks(CurrentName).Sheets("WillEmpList").Range("B" & i)
StrNum = Workbooks(CurrentName).Sheets("WillEmpList").Range("F" & i)

NextRowB = Workbooks(CurrentName).Sheets("Summary").Range("B65536").End(xlUp).Row + 1
NextRowG = Workbooks(CurrentName).Sheets("Summary").Range("G65536").End(xlUp).Row + 1
NextRow = NextRowB
If NextRow < NextRowG Then NextRow = NextRowG

If Dir(StrFileAll) <> "" Then
Application.EnableEvents = False

Workbooks.Open (StrFileAll), UpdateLinks:=0, ReadOnly:=True

Application.EnableEvents = True

Set FSO = CreateObject("Scripting.FileSystemObject")
StrLastTime = FSO.GetFile(StrFileAll).DateLastModified
Set FSO = Nothing

Windows(StrFileNAMAE).Activate

Sheets("Income").Select
If Sheets("Income").Range("A2") = "" And Sheets("Income").Range("B2") = "" And Sheets("Income").Range("C2") = "" And _
Sheets("Income").Range("D2") = "" And Sheets("Income").Range("E2") = "" And Sheets("Income").Range("F2") = "" And _
Sheets("Income").Range("G2") = "" And Sheets("Income").Range("H2") = "" And Sheets("Income").Range("I2") = "" And _
Sheets("Income").Range("J2") = "" And Sheets("Income").Range("K2") = "" And Sheets("Income").Range("L2") = "" Then
Workbooks(CurrentName).Sheets("Summary").Range("O" & BookEmptyCnt) = StrName & "-" & StrNum
BookEmptyCnt = BookEmptyCnt + 1
Else

With Sheets("Income")
.Range("B65536").End(xlUp).Select
RowB = Selection.Row
.Range("H65536").End(xlUp).Select
RowG = Selection.Row

RowCnt = RowB
If RowCnt < RowG Then RowCnt = RowG

Workbooks(CurrentName).Sheets("Summary").Activate
Workbooks(CurrentName).Sheets("Summary").Range("A" & NextRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.Save


End With

Workbooks(CurrentName).Sheets("Summary").Range("P" & CopyOKCnt) = StrName & "-" & StrNum
Workbooks(CurrentName).Sheets("Summary").Range("O" & CopyOKCnt) = StrLastTime
CopyOKCnt = CopyOKCnt + 1
End If

Excel.Application.CutCopyMode = False
Workbooks(StrFileNAMAE).Close SaveChanges:=False
Else
Workbooks(CurrentName).Sheets("Summary").Range("R" & NoWillCnt) = StrName & "-" & StrNum
NoWillCnt = NoWillCnt + 1

End If

Next

Range("A2").Select

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Visible = True
Excel.Application.CutCopyMode = True

End Sub
    Reply With QuoteRegister To Reply
  7. 08-05-2013, 02:03 AM #7
    wenqq3 is offline
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Run - Time error 1004 => When macro run half way

    Hi, Orca_ng
    Can you upload the workbook with remove the sensitive data.
    So we can just test on it.
    Reply With QuoteRegister To Reply
  8. 08-05-2013, 03:06 PM #8
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Run - Time error 1004 => When macro run half way

    Just looking through the code, I see that you have a Paste-Special - but I see no Copy - do you run the routine with the Clipboard pre-loaded??

    If I could help you (and I'm not sure that I can), I would need a sample "ThisWorkbook"
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Macro Error - Run time error 1004 - Paste method of worksheet class failed
    By kvflynn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2012, 10:51 AM
  2. Macro Run-time error '1004':
    By Kody_Devl in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-24-2012, 03:09 PM
  3. Run-time error '1004' in macro
    By tvrm1963 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-24-2012, 10:07 PM
  4. Run-time error '1004': Macro error, only when shared
    By Pergo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-13-2011, 12:57 PM
  5. MACRO Run Time Error 1004
    By The Toasterman in forum Excel General
    Replies: 1
    Last Post: 06-30-2006, 11:25 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1