help with code to repeat a copy & paste macro 2

[Excelerate2004]

How can I iterate or repeat the following copy & paste macro so that it goes thru the entire recordset and carries out the required task?

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 27/06/2005 by bam
'
' Keyboard Shortcut: Ctrl+p
'
Selection.Copy
Sheets("Sheet2").Select
Sheets("Sheet2").Range("A6").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B3:E4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select

Sheets("Sheet2").Range("A7").Select
ActiveSheet.Paste
Sheets("Sheet2").Range("A9").Select
Sheets("Sheet1").Select
Range("A5").Select
Application.CutCopyMode = False

Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet2").Range("A10").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Range("B6:E7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste

End sub

Thanks for any help I can get!

Cheers,

 

[PHV]

it goes thru the entire recordset and carries out the required task
Which recordset ?
Which task ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shetlandbob]

Use a "For" loop or a "While" Loop

Robert Cumming

http://www.consultantengineer.co.uk

 

[Excelerate2004]

This task here:

Range("A2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A6").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B3:E4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A7").Select
ActiveSheet.Paste

I'd like to be able to repeat this so that it goes thru my entire recordset on Sheet1 and reformats the data in my recordset and places it all on Sheet2.

The next iteration after this 1st task would look like the following:

Range("A5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B6:E7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A10").Select
ActiveSheet.Paste
Range("A16").Select

I'd like to repeat the initial task and repeat it for my entire dataset on sheet1 and reformat it all to go on to sheet2?

I hope this helps explain my question further??

Thanks in advance.

 

[shetlandbob]

I'm guessing you dont know any/much VBA?

Try recording the next loop of your reformat.

Then try playing around with the recorded code.

Also look at what the following code does:

i.e.

  dim variable as double
  variable = sheets(sheet1).cells(1,1)
  sheets(sheet2).cells(1,1) = variable

will copy your variable from one sheet to the next, combine this with

  dim i as integer
  for i = 0 to 100
    cells(i,1) = i
  next

this code wont do what you want, but it gives enough clues for you to combine with your own code to make it do what you want. Learning by yourself is much more valuable than being told.



Robert Cumming

http://www.consultantengineer.co.uk

 

[Excelerate2004]

I have some experience with VBA, but I am by no means a serious programmer. I liken myself to a project manager, I know what the larger picture looks like and how all the pieces fit, but as to the actual logistics of writing code...thats my weakpoint.

Anyhow, looking at what shetlandbob has written makes sense, however, my main issue would be how do I write code so that this little macro knows where the end of my dataset would be on sheet1? As well, how do I get it to Offset x # of rows after each repetition.

Because this is something that would be dynamic and may change as varying datasets are brought into the workbook.

Thanks for the help so far.

 

[shetlandbob]

There are a variety of ways to tell your macro when to stop.

If the data in your main sheet uses every row then check for a empty cell, and exit your loop, use:

Do
  if cells(row,1).value = "" then exit do
Loop

To add x rows after each loop use

Do
  variable = sheet1.cells(row,1).value
  if variable = "" then exit do
  sheet2.cells(row,1).value = variable
  row = row + 2
Loop

Robert Cumming

http://www.consultantengineer.co.uk

 

[PHV]

Have a look at the Offset property of the Range object.
Consult the FAQ area of this forum for at least 2 ways to find the last used cell.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Excelerate2004]

Thanks to all, I'll try and put something together that makes sense, then hopefully repost the solution back here.

Cheers!

 

[Excelerate2004]

I'm not having much luck, heres what Ive tried to combine from above:

Private Sub CommandButton3_Click()

Dim a As Double

Do
a = Sheet1.Cells(Row, 1).Value
If a = "" Then Exit Do
Sheet2.Cells(Row, 1).Value = a
Row = Row + 2
Loop
End Sub

I get an error that tells me the variable a = 0.

I'm not even quite sure this is what I want??

Looking at the recorded Macro and using pseudo code:

Do
'Task 1
Range("A2").Select
Selection.Copy
Range("A13").Select
ActiveSheet.Paste
Range("B3:E4").Select
Application.CutCopyMode = False
Selection.Copy
Range("A14").Select
ActiveSheet.Paste

'Check to see if the next row is empty
'If next row is empty then exit loop
'If not then do Task 2

'Task 2
'Range("A5").Select '+3
'Selection.Copy
'Range("A16").Select '+3
'ActiveSheet.Paste
'Range("B6:E7").Select '+3
'Application.CutCopyMode = False
'Selection.Copy
'Range("A17").Select '+3
'ActiveSheet.Paste
'Repeat until the cells are empty
...
'Task N
'Range("AN").Select '+3
'Selection.Copy
'Range("AN").Select '+3
'ActiveSheet.Paste
'Range("BN:EN").Select '+3
'Application.CutCopyMode = False
'Selection.Copy
'Range("AN").Select '+3
'ActiveSheet.Paste
'Repeat until the cells are empty

Loop


I know what it is I want to do I just dont know how to write it.

 

[shetlandbob]

a=0 because its a double.

Can you give an example of whats in col1, i.e. something that can detect the end of your data set?
i.e. is it

1
6
2
5
8
   [green]' no value in cell as end of data set[/green]

First you want to make sure one copy works, then we will worry about looping. The macro recorder can be good, but it makes a lot of unnecessary code

  Dim row As Integer
  row = 1
  Do
    If (IsEmpty(Sheet1.Cells(row + 1, 1).Value)) Then Exit Do
    Sheet3.Cells(row + 5, 1) = Sheet1.Cells(row, 1)
    Sheet3.Cells(row + 6, 1) = Sheet1.Cells(row + 2, 2)
    Sheet3.Cells(row + 6, 2) = Sheet1.Cells(row + 2, 3)
    Sheet3.Cells(row + 7, 1) = Sheet1.Cells(row + 3, 2)
    Sheet3.Cells(row + 7, 2) = Sheet1.Cells(row + 3, 3)
    row = row + 3
  Loop

Try the above code, step through it and try to understand waht it does. (right click to get Debug Toolbar), and click on the little arrow poiting into the text, it should have the text "Step Into"

Good luck!

Robert Cumming

http://www.consultantengineer.co.uk

 

[Excelerate2004]

Hey shetlandbob,

I tried your code sample and its just a bit off, I'll look at it more closely and try and match it up with what I'm trying to do.

Thanks!

 

[shetlandbob]

Really?

It does the same as what was posted at the top of this thread? (I tried to test it last night?)

If you cant get it to work, then give me an example and I'll get it working!

Cheers

Bob

Robert Cumming

http://www.consultantengineer.co.uk

 

[Excelerate2004]

Thanks Shetlandbob! I modified your idea to the following and it worked!!

Dim row As Integer
  row = 1
  Do
    If (IsEmpty(Sheet1.Cells(row + 1, 1).Value)) Then Exit Do
    
    Sheet2.Cells(row + 5, 1) = Sheet1.Cells(row + 1, 1)
    
    Sheet2.Cells(row + 6, 1) = Sheet1.Cells(row + 2, 2)
    Sheet2.Cells(row + 6, 2) = Sheet1.Cells(row + 2, 3)
    Sheet2.Cells(row + 6, 3) = Sheet1.Cells(row + 2, 4)
    Sheet2.Cells(row + 6, 4) = Sheet1.Cells(row + 2, 5)
    Sheet2.Cells(row + 6, 5) = Sheet1.Cells(row + 2, 6)
    Sheet2.Cells(row + 6, 6) = Sheet1.Cells(row + 2, 7)
    Sheet2.Cells(row + 6, 7) = Sheet1.Cells(row + 2, 8)
    Sheet2.Cells(row + 6, 8) = Sheet1.Cells(row + 2, 9)
    
    Sheet2.Cells(row + 7, 1) = Sheet1.Cells(row + 3, 2)
    Sheet2.Cells(row + 7, 2) = Sheet1.Cells(row + 3, 3)
    Sheet2.Cells(row + 7, 3) = Sheet1.Cells(row + 3, 4)
    Sheet2.Cells(row + 7, 4) = Sheet1.Cells(row + 3, 5)
    Sheet2.Cells(row + 7, 5) = Sheet1.Cells(row + 3, 6)
    Sheet2.Cells(row + 7, 6) = Sheet1.Cells(row + 3, 7)
    Sheet2.Cells(row + 7, 7) = Sheet1.Cells(row + 3, 8)
    Sheet2.Cells(row + 7, 8) = Sheet1.Cells(row + 3, 9)
    
    row = row + 3
  Loop

Much appreciated!!

 

[shetlandbob]

No worries glad to be of assistance, good luck and hope you learned a little??



Robert Cumming

http://www.consultantengineer.co.uk

 

[Excelerate2004]

I did learn a bit, most times I find with programming the simplest solution is the one I never choose. Ugghh lol

Thanks again