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?

 

[texasalynn]

Welcome to MrExcel board....

sounds a little daunting and would probably be a little convaluted. Would suggest that you create individual macros for the different layouts, then when that one file comes in you run that macro. You could setup a set with all macros in it and then put different command buttons for the different macros. Good luck

 

[howdy11354]

Thanks.

 

[texasalynn]

I must be sleeping still, cuz that didn't say what I was thinking

Welcome to MrExcel board....
You could setup a set with all macros in it and then put different command buttons for the different macros.

s/b
You could setup a file......

 

[howdy11354]

I created a macro for one of the vendor's spreadsheets. It works fine. On this spreadsheet, we have 4 products and each product has 5 SKUs. Next time if their spreadsheet only shows 4 products and each product has 3 SKUs. When I run this macro, it will not work because each product is missing 2 SKUs(they won't be listed on the same line). What should I do? I am new to macro so hopefully someone can help.

 

[texasalynn]

post the code you have

 

[howdy11354]

I was using the "macro recording" to record the macro. Would that be different?

 

[texasalynn]

that's not a problem using record, you just have to modify the macro afterwards to fix it for repeated use. Go to the macro and select edit and select Ctrl + A (to select all) then Ctrl + C (to copy), then paste it back here in a post.

 

[howdy11354]

Range("G5").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-6],"" "",R[1]C[-4])"
Range("A5").Select
Selection.AutoFill Destination:=Range("A5:A11"), Type:=xlFillDefault
Range("A5:A11").Select
ActiveWindow.SmallScroll Down:=6
Range("A13").Select
Selection.AutoFill Destination:=Range("A13:A17"), Type:=xlFillDefault
Range("A13:A17").Select
Range("A20").Select
Selection.AutoFill Destination:=Range("A20:A25"), Type:=xlFillDefault
Range("A20:A25").Select
ActiveWindow.SmallScroll Down:=6
Range("A27").Select
Selection.AutoFill Destination:=Range("A27:A32"), Type:=xlFillDefault
Range("A27:A32").Select
ActiveWindow.SmallScroll Down:=6
Range("A34").Select
Selection.AutoFill Destination:=Range("A34:A38"), Type:=xlFillDefault
Range("A34:A38").Select
ActiveWindow.SmallScroll Down:=6
Range("A41").Select
Selection.AutoFill Destination:=Range("A41:A44"), Type:=xlFillDefault
Range("A41:A44").Select
ActiveWindow.SmallScroll Down:=6
Range("A46").Select
Selection.AutoFill Destination:=Range("A46:A51"), Type:=xlFillDefault
Range("A46:A51").Select
ActiveWindow.SmallScroll Down:=6
Rows("54:62").Select
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-54
Range("G5").Select
Columns("G:G").EntireColumn.AutoFit
Selection.AutoFill Destination:=Range("G5:G90"), Type:=xlFillDefault
Range("G5:G90").Select
Range("G89").Select
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("G5").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C[-6],"" "",R[1]C[-4])"
Range("G5").Select
Selection.AutoFill Destination:=Range("G5:G34"), Type:=xlFillDefault
Range("G5:G34").Select
Selection.AutoFill Destination:=Range("G5:G90"), Type:=xlFillDefault
Range("G5:G90").Select
Range("G88").Select
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("G5:G89").Select
Range("G5:G89").Cut Destination:=Range("G6:G90")
Range("G6:G90").Select
ActiveWindow.SmallScroll Down:=-93
End Sub

 

[texasalynn]

Ok so would really need to see your sheet or a sample of your data in order to help on making this work. So you could post a small sample of your data and the before and after, or else use one of the free file storage web sites. like this one:
http://www.box.net/

I did clean up your code a little to remove the select statements and the scrolls

Sub howdy()

Range("G5").FormulaR1C1 = "=CONCATENATE(RC[-6],"" "",R[1]C[-4])"
Range("A5").AutoFill Destination:=Range("A5:A11"), Type:=xlFillDefault
Range("A13").AutoFill Destination:=Range("A13:A17"), Type:=xlFillDefault
Range("A20").AutoFill Destination:=Range("A20:A25"), Type:=xlFillDefault
Range("A27").AutoFill Destination:=Range("A27:A32"), Type:=xlFillDefault
Range("A34").AutoFill Destination:=Range("A34:A38"), Type:=xlFillDefault
Range("A41").AutoFill Destination:=Range("A41:A44"), Type:=xlFillDefault
Range("A46").AutoFill Destination:=Range("A46:A51"), Type:=xlFillDefault
Rows("54:62").Delete Shift:=xlUp
Columns("G:G").EntireColumn.AutoFit
Range("G5").AutoFill Destination:=Range("G5:G90"), Type:=xlFillDefault
Range("G5").FormulaR1C1 = "=CONCATENATE(R[1]C[-6],"" "",R[1]C[-4])"
Range("G5").AutoFill Destination:=Range("G5:G34"), Type:=xlFillDefault
Range("G5").AutoFill Destination:=Range("G5:G90"), Type:=xlFillDefault
Range("G5:G89").Cut Destination:=Range("G6:G90")

End Sub