Need help on Marco

howdy11354

New Member
Joined
Apr 17, 2009
Messages
19
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?
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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...... :biggrin:
 

howdy11354

New Member
Joined
Apr 17, 2009
Messages
19

ADVERTISEMENT

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.
 

howdy11354

New Member
Joined
Apr 17, 2009
Messages
19

ADVERTISEMENT

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

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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

New Member
Joined
Apr 17, 2009
Messages
19
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

Well-known Member
Joined
May 19, 2002
Messages
8,458
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,091
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top