VBA Coding

CRUTHERFORD

Board Regular
Joined
Jul 10, 2014
Messages
107
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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?
 
Upvote 0

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
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?)

Code:
[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]
 
Upvote 0

par60056

Well-known Member
Joined
Jul 26, 2012
Messages
1,581
Office Version
  1. 2011
  2. 2010
Platform
  1. Windows
  2. MacOS
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:

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

CRUTHERFORD

Board Regular
Joined
Jul 10, 2014
Messages
107
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.
 
Upvote 0

Forum statistics

Threads
1,190,801
Messages
5,982,992
Members
439,811
Latest member
DB38

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