VBA Coding

[CRUTHERFORD]

Hi,

So i have two questions - one is really easy to answer im sure but the other may not be.

1st - I'm looking to learn more about VBA and how to use it; where is the best place to learn and understand or is it a skill learnt from experience and trial and error?

2nd - I have 33 different spreadsheets that i would like to pull the data from and put onto one. My problem is that currently it's such a manual process and i have to copy then paste the data under the right matching header for each one. I know its a long shot but is there any way i can have some VBA that will rearrange the headings from each of the 33 spreadhsheets and pull the info into my main one? My thoughts are that if i could do a long piece of VBA that will go through each individual spreadsheet one by one, and reorder the data to what it is in the main spreadsheet it could save me alot of time even though it might take ages to process?!

Please help...

Thanks

 

[NeonRedSharpie]

1) Just write stuff. Use the macro recorder, study what it says, and learn the syntax. There aren't a lot of books that will help you because there are 430 ways to do it. You'll see that even in answers. I'll have a way, and there will be three other macros posted that do the same thing.

2) It actually won't take as long as you think to process. Just need a little more detail. Are all the columns the same column, just in different order?

 

[mrmmickle1]

1. EdNerd Gave me this list a while back. It was helpful :

I managed to put together a list of the Excel web sites I've collected over a few years. Actually, some of them may be dead links - but lots are still very alive

www.YumaMagic.com/ExcelFaves.htm

Enjoy!!
Ed

2. Try using this macro Rick Rothstein put together to rearrange columns (Is this what you meant by headings?)

[COLOR=#0000ff]Sub[/COLOR] RearrangeColumns()
 [COLOR=#0000ff] Dim [/COLOR]X [COLOR=#0000ff]As Long[/COLOR], Lastrow[COLOR=#0000ff] As Long[/COLOR], Letters [COLOR=#0000ff]As Variant[/COLOR], NewLetters [COLOR=#0000ff]As Variant[/COLOR]
  [COLOR=#0000ff]Const[/COLOR] NewOrder [COLOR=#0000ff]As String[/COLOR] = "C,B,E,F,H,AC,K,AF,T,M,S,G,X,I,Z,AA,L,AG,AE,AH,AD,AI,A,D,J,N,O,P,Q,R,U,V,W,Y,AB,AJ" 'Put new column/headings order here
  Lastrow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  Letters = Split(NewOrder, ",")
[COLOR=#0000ff]  ReDim [/COLOR]NewLetters(1 [COLOR=#0000ff]To UBound[/COLOR](Letters) + 1)
[COLOR=#0000ff]  For[/COLOR] X = 0 [COLOR=#0000ff]To UBound[/COLOR](Letters)
    NewLetters(X + 1) = Columns(Letters(X)).Column
[COLOR=#0000ff]  Next[/COLOR]
  Range("A1").Resize(Lastrow, [COLOR=#0000ff]UBound[/COLOR](Letters) + 1) = Application.Index(Cells, Evaluate("ROW(1:" & Lastrow & ")"), NewLetters)
[COLOR=#0000ff]End Sub[/COLOR]

 

[par60056]

Welcome to the forum. You will find lots of good answers and help here.

1) I find that the best way to learn is to use the macro recorder and record doing something manually. Then edit a lot. The macro recorded includes every movement and scroll. It also does everything by selecting something and then working on the selection. Most macros can be written without changing the selection. Changing selection for everything also slows the macro.

Read the forum. There are always good code examples being posted.

Ask questions. There are some real geniuses on here and so almost no question can't be answered. The better the description of what you want to do and the better the examples you provide the faster you will get solutions that help. Post any code that you have questions about. Make sure to use the code /code tags. People will help find issues if you have made a first attempt.

2) this sounds like a fine process to automate. If you need to do the same thing to all 33 sheets, then it is easy to write a loop that looks at all the sheets in a workbook and if it isn't the 1 sheet you don't want to change then do the processing.

You would have code that looks something like:

Sub processSheets()


Dim ws As Variant


For Each ws In Sheets
    If ws.Name <> "Master" Then
        'do the stuff to the sheet
        ws.Cells(3, 2) = "pick me"
    End If
Next


End Sub

 

[CRUTHERFORD]

Hi,

1) Thanks to all of you for the above comments and advice! It's a great help and i will definately take a closer look at the recording macros.

2) Thanks again to all of you for your posts but it seems that mrmmickle1 has the code for me so thank you.

 

[mrmmickle1]

Glad I could help. Thanks for the feedback.