Need help on Marco

[howdy11354]

I have all these spreadsheets from different vendors and all of these spreadsheets are in totally different formats. Is there a way I can create one macro and apply this one macro to all spreadsheets to get me a standard format?

 

[howdy11354]

http://www.box.net/shared/6hjxuethtz

 

[texasalynn]

ok so are you trying to copy/repeat the data in column A to each item till the blank line then repeat for the next? Your macro showed it starting in A5, but the file has the data starting in A1. Need a little more info

 

[howdy11354]

I deleted some information on the file that I sent you. What I really need to do is to have a macro copying from A1 through A7 for product TKSA, A9 through A13 for product TKDEV, and so on...Then I need to concatenate Column A and column C to make a full description. Thx.

 

[texasalynn]

ok so this is how I would work the macro

Sub format()
Dim LR As Long
LR = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    
    Cells.Select
    Cells.AutoFilter Field:=3, Criteria1:="<>"
    Cells.AutoFilter Field:=1, Criteria1:="="
    Range("A2").FormulaR1C1 = "=+R[-1]C"
    Range("G2").FormulaR1C1 = "=RC[-6]&"" ""&RC[-4]"
    Range("A2").Copy
    Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Cells.AutoFilter Field:=3
    Cells.AutoFilter Field:=1, Criteria1:="<>"
    Range("G2").Copy
    Range("G2:G" & LR).SpecialCells(xlCellTypeVisible).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").AutoFilter
    Range("A1").Select
    Columns("A").Copy
    Columns("A").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    Columns("G").Copy
    Columns("G").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("G:G").EntireColumn.AutoFit
    Range("A1").Select

End Sub

Let me know how it goes. HTH

 

[howdy11354]

Awesome! it works. Is it hard to edit the codes? I have about 50 vendors and each vendor has its own format. I wonder if I have the ability to create one macro for each vendor.

 

[texasalynn]

doing is learning :wink:

but to create another copy of the macro goto Tools - Macro - Macros - click (once) on the "format" macro, then click edit. Now do a CTRL+A (selects all), then do a CTRL+C (copy), now press CTRL + END, then press CTRL + V (paste). You will have a copy of the macro, now immediately go to the second "SUB Format" line and change that to something else "Sub Format_Vendor2" (suggestion). Then you can change the parts that are different. If you need help, post back with the differences for the 2nd layout.

edit: Also I would probably not keep copying/paste into the same Module, but perhaps insert a new module to paste the code into.