VBA: ActiveSheet started spitting out 1004 out of nowhere

[mewashoo]

Hi All, First, nice to meet you all! I have a fairly large database of transactions which i am trying to show in a form of project tracker. I download logs from accounting software and import it into a sheet within workbook and then import records matching several conditions. I have had it finished, but i fancied a revamp and simplified code a bit, and i can't really spot anything different in parts of code that is throwing 1004 error right now. I know i shouldn't be using select or activesheet, but i have to as each project is different name and sheets are names after the project name based on value in "B5" of current sheet. Second issue i am facing, it really needs massive amounts of RAM to work. Is there any other approach that i could take that i am missing at the moment that would be more optimised? It has to be dynamic as new projects are starting all the time and some others are being finished. Pivot tables may be an option, but i do not know how to create them dynamically where i have 4 tables to worry about within each sheet (labour, supplies, subcons and incomes) and i don't want to go very wide, i would rather stick to utilising rows instead of columns (it's being converted to PDF and printed for internal meetings). I hope somebody see something i am missing, and by the way, i took most of the code from various sites so i do not take any credit for it and feel free to use this code if you find it useful. (code in red is causing issues)

[mewashoo]

If Worksheets("Data").Cells(e, 17) = ActiveSheet.Range("B5") Then 'check if same project
If Not IsError(Application.Match(Worksheets("Data").Cells(e, 12), Worksheets("References").Range("D:D"), 0)) Then 'check if name exists in range
If Not InStr(Worksheets("Data").Cells(e, 11), "PORP") = 1 Then 'skip PORP ones
If IsError(Application.Match(Worksheets("Data").Cells(e, 2), ActiveSheet.Range("LabourIDCol"), 0)) Then 'check if invoice number already exists
ActiveSheet.Range("LabourStart").End(xlDown).Offset(1, 0) = Split(Worksheets("Data").Cells(e, 11), " ")(0) 'import Inv No
ActiveSheet.Range("LabourStart").End(xlDown).Offset(0, 1) = Worksheets("Data").Cells(e, 9) 'import Inv Date
ActiveSheet.Range("LabourStart").End(xlDown).Offset(0, 2) = Worksheets("Data").Cells(e, 12) 'import Name
ActiveSheet.Range("LabourStart").End(xlDown).Offset(0, 3) = "=IFERROR(VLOOKUP(RC[-1],References!C:C[1],2,0),"""")"
ActiveSheet.Range("LabourStart").End(xlDown).Offset(0, 4) = Worksheets("Data").Cells(e, 13) 'import Inv Value
If Not IsError(Application.Match(ActiveSheet.Range("LabourStart").Offset(0, 1).End(xlDown), Worksheets("References").Range("A:A"), 0)) Then
ActiveSheet.Range("LabourStart").End(xlDown).Offset(0, 5) = ActiveSheet.Range("LabourStart").End(xlDown).Offset(0, 4) + ActiveSheet.Range("LabourStart").End(xlDown).Offset(0, 4).Value * ActiveSheet.Range("N19")
ElseIf IsError(Application.Match(ActiveSheet.Range("LabourStart").Offset(0, 1).End(xlDown), Worksheets("References").Range("A:A"), 0)) Then
ActiveSheet.Range("LabourStart").End(xlDown).Offset(0, 5) = ActiveSheet.Range("LabourStart").End(xlDown).Offset(0, 4) + ActiveSheet.Range("LabourStart").End(xlDown).Offset(0, 4).Value * ActiveSheet.Range("N18")
End If
ActiveSheet.Range("LabourStart").End(xlDown).Offset(0, 5).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Range("LabourStart").End(xlDown).Offset(0, 7) = Worksheets("Data").Cells(e, 2) 'import ID
ActiveSheet.Range("LabourStart").End(xlDown).Offset(2).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Application.CutCopyMode = False
End If
End If
End If
End If

[Jan Karel Pieterse]

Your code assumes a range name named LabourIDCol is present in the active sheet. If that name is absent, the code causes error 1004.

[mewashoo]

Your code assumes a range name named LabourIDCol is present in the active sheet. If that name is absent, the code causes error 1004.

Hi, range is there, referring to correct tables. Funnily, nothing changes when activesheet is changed to actual sheet name either. . Mind boggling

[mewashoo]

Your code assumes a range name named LabourIDCol is present in the active sheet. If that name is absent, the code causes error 1004.


Hmm, turns out it is an issue with range.... I suppose i need to start workbook from scratch as it's not code issue, it's workbook issue? Have microsoft not managed to give solution to those yet?

[Jan Karel Pieterse]

It is both a code issue and a workbook issue, both are intertwined.

[SamT]

I'd like to see the entire code. I see some advantageous changes possible in that little snippet... Can you attach the workbook (Use the Go Advanced button)

[mewashoo]

I'd like to see the entire code. I see some advantageous changes possible in that little snippet... Can you attach the workbook (Use the Go Advanced button)

I have removed all named ranges and re done them from scratch and it's fine. i think the issue was that 1 template sheet is being copied over to new sheet with all named ranges etc dragged over. I was missing ranges applicable to entire workbook, only had those referring to each sheet.
Anyways, code is pretty heavy on RAM, so if you do have some ideas, please let me know. i had to remove some parts, but 90% of its content is attached.

[SamT]

I thought that Sheet Blank was the template copied to a new sheet. I had already written much code for Blank to eliminate the issue with copying it, Then I got to Sub NewSheet() and saw you copying many of Blank's Cells to the new copy.

Can you tell us what is supposed to be going on?

[mewashoo]

I thought that Sheet Blank was the template copied to a new sheet. I had already written much code for Blank to eliminate the issue with copying it, Then I got to Sub NewSheet() and saw you copying many of Blank's Cells to the new copy.

Can you tell us what is supposed to be going on?


Sure, i import 3 files from my accounting software into "Data" sheet with "importer" macro. New Sheet creates new blank sheet named as per project name so then any transactions within this particular project are imported into its respective tab. Projects come and go hence "New Sheet" macro.
Bear in mind i have done this over 3 years ago and now i'm just rewriting it so you'll definitely find irrelevant macros and missing references.

[SamT]

New Sheet creates new blank sheet named as per project name so then any transactions within this particular project are imported into its respective tab [Sheet].

That is confusing... New Sheet (Project,) and Respective sheet.
By "blank sheet" do you mean a copy of Sheet ("Blank")


import 3 files from my accounting software into "Data" sheet That should result in three new sheets. Correct?
Parsing Sub Importer, it appears that you must make some manual edits to sheet References before rinning Sub Importer. Is that correct?


you'll definitely find irrelevant macros and missing references.
I can ignore missing Reference, but How can I tell which Procedures (Macros) are no longer used. Easier to ask which Modules are still in use.

Can you attach a sample "Data" sheet with an example of Imported values and with Column Headers?

Thanks, st

[mewashoo]

That is confusing... New Sheet (Project,) and Respective sheet.
By "blank sheet" do you mean a copy of Sheet ("Blank")

That should result in three new sheets. Correct?
Parsing Sub Importer, it appears that you must make some manual edits to sheet References before rinning Sub Importer. Is that correct?


I can ignore missing Reference, but How can I tell which Procedures (Macros) are no longer used. Easier to ask which Modules are still in use.

Can you attach a sample "Data" sheet with an example of Imported values and with Column Headers?

Thanks, st

Right,

NewSheet (module 2)is still being used - it copies entire "Blank" sheet over to new one that is being named through input window, some cells are being referred to blank sheet to allow easy renaming if necessary
MergerNew (module 8) is being used to import data from CSV files
Importer (module 10) is being used to import data from "Data" sheet into its respective Worksheet

Other macros are irrelevant really, either testing or minor use.

I bet you'll find plenty ways of optimisation in MergerNew macro.

[SamT]

I have attached a copy of the data sheet. I have added Field Names (Headers) as I could discover them. Please fill in all other Field Names, even if they won't be used. Also, can you add a sample import to it. You can replace all Proprietary Names with "XXX" and all monetary values with 99.99

In that Sheet's Code page, I pasted the Code for Sub NewSheet(). What does the formula = "=Blank!RC" do?

[SamT]

You do realize that the data sheet has 4 columns ("Index" x 4) that have 5000 formulas. That is 20,000 unnecessary calculations that must be done every time. Plus they make the used Range over 500K cells in size