• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

recorded macro unsuccessful to remove&to delete &insert data cells

R

RAM72

Member
Hi All

See attached , I have a bunch of data ,my aim is to remove unnessecary data, to format it as an invoice and save in a particular directory on C:\\

I recorded a macro but unsuccesful indeed .

Those in yellow are the prime data required and two insert two columns at the right.
 

Attachments

  • INVOICE TEST.xlsm
    36.5 KB · Views: 11
Your file's sheet1 has rows highlighted in yellow that you want to keep, and rows with cells just filled with the word DATA that you want to lose. In real life, I don't for a minute believe that that is the situation; what is is about the rows you want to lose that can identify them (or perhaps: what is it about the rows you want to keep that can identify them)?

In the meantime your macro can be shortened and made a little more general (it will copy whichever sheet is the active sheet and make alterations and rename that copied sheet) but it can be further generalised when you answer the question above.


Code: 
Sub SHEET1MACINVOICE()
ActiveSheet.Copy Before:=Sheets(2)
With ActiveSheet
  .Range("A2:C29").ClearContents
  .Columns("A:B").Insert
  .Range("C30").Cut Destination:=.Range("D30")
  .Range("F9:F10").Copy .Range("A30")
  .Columns("A:A").EntireColumn.AutoFit
  .Range("C30").Value = "REFERENCE"
  .Range("F3").Copy .Range("B30")
  .Range("F5").Copy .Range("B31")
  .Rows("1:29").Delete
  .Rows("31:37").Delete
  .Range("A83:J89").Delete Shift:=xlUp
  .Range("A114:F144").ClearContents
  .Range("G114:G123").ClearContents
  .Range("A2:B2").Copy Range("A3:A112")
  .Name = Range("B112").Value
End With
ChDir "C:\INVOICE"
End Sub
 
Last edited:
p45cal said:
Your file's sheet1 has rows highlighted in yellow that you want to keep, and rows with cells just filled with the word DATA that you want to lose. In real life, I don't for a minute believe that that is the situation; what is is about the rows you want to lose that can identify them (or perhaps: what is it about the rows you want to keep that can identify them)?

In the meantime your macro can be shortened and made a little more general (it will copy whichever sheet is the active sheet and make alterations and rename that copied sheet) but it can be further generalised when you answer the question above.


Code: 
Sub SHEET1MACINVOICE()
ActiveSheet.Copy Before:=Sheets(2)
With ActiveSheet
  .Range("A2:C29").ClearContents
  .Columns("A:B").Insert
  .Range("C30").Cut Destination:=.Range("D30")
  .Range("F9:F10").Copy .Range("A30")
  .Columns("A:A").EntireColumn.AutoFit
  .Range("C30").Value = "REFERENCE"
  .Range("F3").Copy .Range("B30")
  .Range("F5").Copy .Range("B31")
  .Rows("1:29").Delete
  .Rows("31:37").Delete
  .Range("A83:J89").Delete Shift:=xlUp
  .Range("A114:F144").ClearContents
  .Range("G114:G123").ClearContents
  .Range("A2:B2").Copy Range("A3:A112")
  .Name = Range("B112").Value
End With
ChDir "C:\INVOICE"
End Sub
Click to expand...

Hi

Try code

ending error

.Name = Range("B112").Value in yellow.

However, upload variant models as per your request for ease of understanding



t
 

Attachments

  • inv variant models.xlsx
    48.4 KB · Views: 3
RAM72 said:
Hi

Try code

ending error

.Name = Range("B112").Value in yellow.

However, upload variant models as per your request for ease of understanding



t
Click to expand...
This line will typically throw an error if the name you're trying to give to the sheet is the same as an existing sheet name, or if the name you're trying to give it has illegal characters (for a sheet name) in it.
I'll have a look at your new file presently.
 
I'm looking in your file for ways to determine what entire rows I can delete; that is, rules for the code to follow.

Starting with the row below HS Code in column C all the way bown to the row above Detail Line Total in column D, is it safe to say that I can delete entire rows where the cells in column B are blank? The cellls in columns D and E are also blank for these rows in variant 1 and variant 2; would either of these columns be a more reliable indicator of which rows I can delete?

In variant 3 and variant 4 your yellow cells-to-be-deleted are much more scattered around, but I see that you want to lose data from column B - does this apply to variant 1 and variant 2 too?

Regarding the top 30 or so rows of each sheet, is it safe to say I can clear the contents of all cells in just the columns A to C, from row 1 down to the row above Buyers Code: in column B?
Can I also make the rule that the entire rows can be deleted from the row with Buyers Code: in column B, down to the row above HS Code in column C?

Looking at the bottom 30 rows or so on each sheet, can I safely always delete everything below and including the row having Detail Line Total in column D, EXCEPT for the data in column G on the same row as Detail Line Total in column D? (Actually, in variant 3 that data seems to be in column H - is it common for data to be in different columns like this?)

How would you like the new sheet to be named?

Answer each of the questions above as meticulously as you can, without missing any, and I could have some generalised code for you quickly.
 
p45cal said:
I'm looking in your file for ways to determine what entire rows I can delete; that is, rules for the code to follow.

Starting with the row below HS Code in column C all the way bown to the row above Detail Line Total in column D, is it safe to say that I can delete entire rows where the cells in column B are blank? The cellls in columns D and E are also blank for these rows in variant 1 and variant 2; would either of these columns be a more reliable indicator of which rows I can delete?

In variant 3 and variant 4 your yellow cells-to-be-deleted are much more scattered around, but I see that you want to lose data from column B - does this apply to variant 1 and variant 2 too?

Regarding the top 30 or so rows of each sheet, is it safe to say I can clear the contents of all cells in just the columns A to C, from row 1 down to the row above Buyers Code: in column B?
Can I also make the rule that the entire rows can be deleted from the row with Buyers Code: in column B, down to the row above HS Code in column C?

Looking at the bottom 30 rows or so on each sheet, can I safely always delete everything below and including the row having Detail Line Total in column D, EXCEPT for the data in column G on the same row as Detail Line Total in column D? (Actually, in variant 3 that data seems to be in column H - is it common for data to be in different columns like this?)

How would you like the new sheet to be named?

Answer each of the questions above as meticulously as you can, without missing any, and I could have some generalised code for you quickly.
Click to expand...

Hi

I apologize for data submitted as it leads to confusion.

I reworked the variant sheets where are possible combination invoice sheets that can and will occur.

So what I am looking is the Buyer reference sheet & invoice number headers and their respective data where two columns should be inserted at column A 1 and B1 copied to last data row based on the reference headers of columns A 32.

the header variant also varies at may start on row 29 or row 28 particularly if the data is small.

The only drawback is the blank rows between the data varies from 6 to 8 rows as per variant but not more than eight.

However variant 3 shows a blank column C which can be deleted .

I have highlighted the data to be on expected results which should be more clear now.

The whole at top can be deleted provided the code copies the buyers reference and invoice number with its relative data to insert in the first two columns as per expected sheet results .

the sheet tab should be named invoice number data ie 00060679.

See attached sheet
 

Attachments

  • amended inv variant models with expected results.xlsx
    61.3 KB · Views: 5
Test the following; it uses the active sheet as the source sheet, creates a new workbook, saves and closes the new Workbook with the invoice number as its name to the C:\INVOICE folder.
Code: 
Sub SHEET1MACINVOICE()
Dim CopyRows As Range
Set SceSht = ActiveSheet
Worksheets.Add
ActiveSheet.Move
Set NewSht = ActiveSheet
NewSht.Range("A1:I1") = Array("Buyer's Reference", "Invoice Number", "REFERENCE", "Description of Goods", "HS Code", "Origin", "Quantity", "@", "Amount")
With SceSht
  Set HSC = .Cells.Find(what:="HS Code", LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False)
  Set DLT = .Cells.Find(what:="Detail Line Total", LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False)
  'Determine rows to be copied (uses
  Set CopyRows = .Range(.Cells(HSC.Row + 1, HSC.Column + 2), .Cells(DLT.Row - 1, HSC.Column + 2))
  On Error Resume Next
  Set CopyRows = .Range(.Cells(HSC.Row + 1, HSC.Column + 2), .Cells(DLT.Row - 1, HSC.Column + 2)).SpecialCells(xlCellTypeConstants, 23)
  On Error GoTo 0
  'Column C reference gets its data from Description of Goods column A:
  Intersect(CopyRows.EntireRow, .Columns(1)).Copy NewSht.Range("C2")
  'Column D data comes from column B:
  Intersect(CopyRows.EntireRow, .Columns(2)).Copy NewSht.Range("D2")
  'Copy the next 5 columns:
  Intersect(CopyRows.EntireRow, .Columns(HSC.Column).Resize(, 5)).Copy NewSht.Range("E2")
  'Buyer's Ref and Invoice no. repeat in columns A and B respectively.
  Set TheLastCell = NewSht.Range("A1").SpecialCells(xlCellTypeLastCell)
  Set InvNo = .Cells.Find(what:="Invoice Number", LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False).Offset(2)
  InvNo.Copy Range(NewSht.Range("B2"), NewSht.Cells(TheLastCell.Row, "B"))
  Set BuyRef = .Cells.Find(what:="Buyer's Reference", LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False).Offset(1)
  BuyRef.Copy Range(NewSht.Range("A2"), NewSht.Cells(TheLastCell.Row, "A"))
  'put the total in:
  .Cells(DLT.Row, HSC.Column + 4).Copy TheLastCell.Offset(1)
End With
NewSht.Columns("A:I").EntireColumn.AutoFit
'Save and close new Workbook with invoice number an name to the C:\INVOICE folder:
NewSht.Parent.Close True, Filename:="C:\INVOICE\" & InvNo.Value & ".xlsx"
End Sub
 
I tested it working flawlessly.:awesome::awesome: but just a little tuning, could you adjust so that it also add the invoice number on the sheet tab.:)

PM for some issues

Thank you for your kind assistance
 
Code: 
Sub SHEET1MACINVOICE()
Dim CopyRows As Range
Set SceSht = ActiveSheet
Worksheets.Add
ActiveSheet.Move
Set NewSht = ActiveSheet
With NewSht
  .Range("A1:I1") = Array("Buyer's Reference", "Invoice Number", "REFERENCE", "Description of Goods", "HS Code", "Origin", "Quantity", "@", "Amount")
  With SceSht
    Set HSC = .Cells.Find(what:="HS Code", LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False)
    Set DLT = .Cells.Find(what:="Detail Line Total", LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False)
    'Determine rows to be copied (uses
    Set CopyRows = .Range(.Cells(HSC.Row + 1, HSC.Column + 2), .Cells(DLT.Row - 1, HSC.Column + 2))
    On Error Resume Next
    Set CopyRows = .Range(.Cells(HSC.Row + 1, HSC.Column + 2), .Cells(DLT.Row - 1, HSC.Column + 2)).SpecialCells(xlCellTypeConstants, 23)
    On Error GoTo 0
    'Column C reference gets its data from Description of Goods column A:
    Intersect(CopyRows.EntireRow, .Columns(1)).Copy NewSht.Range("C2")
    'Column D data comes from column B:
    Intersect(CopyRows.EntireRow, .Columns(2)).Copy NewSht.Range("D2")
    'Copy the next 5 columns:
    Intersect(CopyRows.EntireRow, .Columns(HSC.Column).Resize(, 5)).Copy NewSht.Range("E2")
    'Buyer's Ref and Invoice no. repeat in columns A and B respectively.
    Set TheLastCell = NewSht.Range("A1").SpecialCells(xlCellTypeLastCell)
    Set InvNo = .Cells.Find(what:="Invoice Number", LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False).Offset(2)
    InvNo.Copy Range(NewSht.Range("B2"), NewSht.Cells(TheLastCell.Row, "B"))
    Set BuyRef = .Cells.Find(what:="Buyer's Reference", LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False).Offset(1)
    BuyRef.Copy Range(NewSht.Range("A2"), NewSht.Cells(TheLastCell.Row, "A"))
    'put the total in:
    .Cells(DLT.Row, HSC.Column + 4).Copy TheLastCell.Offset(1)
  End With  'SceSht
  .Columns("A:I").EntireColumn.AutoFit
  .Name = InvNo.Value
  'Save and close new Workbook with invoice number an name to the C:\INVOICE folder:
  .Parent.Close True, Filename:="C:\INVOICE\" & InvNo.Value & ".xlsx"
End With  'NewSht
End Sub
 
You must log in or register to reply here.
Back
Top