Please help with Excel VB automation question
Please help with Excel VB automation question
Please help with Excel VB automation question
Please help with Excel VB automation question
Please help with Excel VB automation question
Please help with Excel VB automation question Please help with Excel VB automation question Please help with Excel VB automation question Please help with Excel VB automation question Please help with Excel VB automation question Please help with Excel VB automation question Please help with Excel VB automation question Please help with Excel VB automation question
Please help with Excel VB automation question Please help with Excel VB automation question
Please help with Excel VB automation question
Go Back  Xtreme Visual Basic Talk > > > Please help with Excel VB automation question


Reply
 
Thread Tools Display Modes
  #1  
Old 01-05-2012, 12:18 AM
mikeforrest23 mikeforrest23 is offline
Newcomer
 
Join Date: Jan 2012
Posts: 2
Question Please help with Excel VB automation question


Hi,

Please let me clarify that I have no previous knowledge of visual basic but I'm trying to record a macro in Excel and then modify it so that it cleans up my entire database (20,000+ rows) of data. The problem is this data was entered in rows instead of columns so I can't really use it at the moment. I am trying to transpose it but I can't just do copy -> paste special (transpose) because each entry needs to be on its own row. Hence, I need to do that copy -> paste special (transpose) for each entry. Please see below code from Macro recorder. I would like someone's help to insert a simple loop into the code which changes the cell ranges on the different sheets automatically. So for example: Range("A1:C14").Select should be Range ("A15:C28) on the 2nd loop, A29:C42 on the 3rd and so on (A and C both increase by 14 on each loop). The other range which needs to change is in Sheet2, A4 -> A7 -> A10 (+3 each loop) and Range("B7:N7") to B10:N10, B13:N13 etc.

Sorry if it's a bit confusing, I think if anyone with a bit of coding experience can take a look at the code from the macro below, it will be a lot easier to understand. I am basically trying to have a loop for the process below rather than having to do it manually. Thank you very much if you're able to add in the loop code.

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 05/01/2012 by Mike Forrest
'

'
Range("A1:C14").Select
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Bangkok travel agents.").Select
Range("A15:C28").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B4:N4").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A7").Select
Sheets("Bangkok travel agents.").Select
ActiveWindow.SmallScroll Down:=12
Range("A29:C42").Select
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B7:N7").Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Bangkok travel agents.").Select
Range("A43:C56").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A10").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B10:N10").Select
Application.CutCopyMode = False
Selection.ClearContents
End Sub
Reply With Quote
Reply

Tags
excel macro automation


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Please help with Excel VB automation question
Please help with Excel VB automation question
Please help with Excel VB automation question Please help with Excel VB automation question
Please help with Excel VB automation question
Please help with Excel VB automation question
Please help with Excel VB automation question Please help with Excel VB automation question Please help with Excel VB automation question Please help with Excel VB automation question Please help with Excel VB automation question Please help with Excel VB automation question Please help with Excel VB automation question
Please help with Excel VB automation question
Please help with Excel VB automation question
 
Please help with Excel VB automation question
Please help with Excel VB automation question
 
-->