Do I need a, do loop?

[adkchum]

Morning all,

I have done a Macro in Excel 2003 to copy and paste infomation from one sheet to my form I have setup in another sheet.

Can someone add to my macro so it continues copying the the data down the rows until it reaches a blank row, please

Many thanks.

'
' Macro4444 Macro
' Macro recorded 12/05/2009
'
' Keyboard Shortcut: Ctrl+d
'
    Sheets("Sheet 3").Select
    Range("F2").Select
    Selection.Copy
    Sheets("Side One").Select
    ActiveSheet.Paste
    Range("G42").Select
    Sheets("Sheet 3").Select
    Range("E2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Side One").Select
    ActiveSheet.Paste
    Range("D48").Select
    Sheets("Sheet 3").Select
    Range("A2:D2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Side One").Select
    ActiveSheet.Paste
    Range("I48").Select
    Sheets("Sheet 3").Select
    Range("G2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Side One").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("Sheet 3").Select
    Range("A3").Select
End Sub

[mewingkitty]

For a do until row is blank, you'd use something like this:

dim x as integer
x = 1

do while cells(x, 1) <> vbnullstring
    ' your code here
    x = x + 1
loop

Does that help?

It's sorta hard to follow why your code is doing what it's doing without somewhat of a description, but that might give you something to work off of?

lemme know.

mew!

[rylo]

Hi

Can you please attach an example file showing your source and output data and what goes where. There are a couple of bits missing in the code (eg the first output cell) and are you pasting just the values or are they formulas?

rylo

[adkchum]

Hi

Can you please attach an example file showing your source and output data and what goes where. There are a couple of bits missing in the code (eg the first output cell) and are you pasting just the values or are they formulas?

rylo

I am trying to basicly copy just values from 'Sheet3' over to my Form then print it out.

But i need the macro to continue down the rows until it gets to a empty row.
i have attached my spreadsheet.

thank You.

[Chance2]

You do not need to "SELECT" everything in Excel.
That being said, I've shortened your code and added a loop to get everything (you may want to confirm my TO and FROM ranges)...

Sub Macro4444()
'
' Macro4444 Macro
' Macro recorded 13/05/2009 by D
'
' Keyboard Shortcut: Ctrl+d
'
    
   Dim iRow As Long
    iRow = 2
    Sheets("Sheet3").Select
    Do Until IsEmpty(Sheets("Sheet3").Range("A" & iRow)) = True
        Sheets(" Print Forms").Range("F42:I42, I49, F40, F42").Value = _
            Sheets("Sheet3").Range("A" & iRow & ":D" & iRow & ", G" & iRow & ", F" & iRow & ", E" & iRow).Value
        Sheets(" Print Forms").PrintOut Copies:=1, Collate:=True
        iRow = iRow + 1
        Range("A" & iRow).Select
    Loop
End Sub

[adkchum]

You do not need to "SELECT" everything in Excel.
That being said, I've shortened your code and added a loop to get everything (you may want to confirm my TO and FROM ranges)...

Sub Macro4444()
'
' Macro4444 Macro
' Macro recorded 13/05/2009 by D
'
' Keyboard Shortcut: Ctrl+d
'
    
   Dim iRow As Long
    iRow = 2
    Sheets("Sheet3").Select
    Do Until IsEmpty(Sheets("Sheet3").Range("A" & iRow)) = True
        Sheets(" Print Forms").Range("F42:I42, I49, F40, F42").Value = _
            Sheets("Sheet3").Range("A" & iRow & ":D" & iRow & ", G" & iRow & ", F" & iRow & ", E" & iRow).Value
        Sheets(" Print Forms").PrintOut Copies:=1, Collate:=True
        iRow = iRow + 1
        Range("A" & iRow).Select
    Loop
End Sub

Thank very much for you time and help
I changed a range (F42:I42) to (D49-G49) , but for some reason i cannot work out how to alter:

I need
'Sheet3' column G (Missing or Free Text) copied into sheet 'Print Forms' cell I49
'Sheet3' column E (DATE) copied in to 'Print Forms' cell F42
'Sheet3' column F (0000) copies in to 'Print Forms' cell F40

I hope this makes sense, and again i do thank you for your time and help.

[Chance2]

The lines after the DO statement are your TO and FROM:

"TO"
Sheets(" Print Forms").Range("F42:I42, I49, F40, F42").Value

"FROM"
Sheets("Sheet3").Range("A" & iRow & ":D" & iRow & ", G" & iRow & ", F" & iRow & ", E" & iRow).Value

I changed a range (F42:I42) to (D49-G49) , but for some reason i cannot work out how to alter:

I need
'Sheet3' column G (Missing or Free Text) copied into sheet 'Print Forms' cell I49
'Sheet3' column E (DATE) copied in to 'Print Forms' cell F42
'Sheet3' column F (0000) copies in to 'Print Forms' cell F40

Sheets(" Print Forms").Range("D49:G49, I49, F40, F42").Value = _
Sheets("Sheet3").Range("A" & iRow & ":D" & iRow & ", G" & iRow & ", F" & iRow & ", E" & iRow).Value