CODEWITHSUNDEEP
vbaexcel
I Forte Si Daja
I Forte Si Daja

Reputation: 45

VBA code to copy a cell from sheet_A to sheet_B

I have two sheets:

SheetA has a list of employee Nr.

Sheet B has a form that needs to be filled out AND printed with each employee numbers on it (then vlookup formulas fill out the rest)

Now I can copy paste each employee ID manually, but there are 330+ employees, that is a bit too much.

I would like to copy cell A2 in Sheet_A, paste it into cell A2 Sheet_B AND print the form, then go to cell A3 in Sheet_A copy it, paste it into A2 in Sheet_B and so on... I would like to repeat this process 337 times.

enter image description here

enter image description here

I created this macro, but I don't know how to make it always choose the next cell in Sheet_A AND repeat itself 337 times. (or depending on how many employees we have at a certain time)

Sub Copy_Cell()
' Copy_Cell Macro

    Sheets("Sheet A").Select
    Range("A2").Select
    Selection.Copy
    Sheets("Sheet B").Select
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub

Upvotes: 0

Views: 153

Answers (3)

YowE3K
YowE3K

Reputation: 23984

You just need to loop through each of your rows:

Sub Copy_Cell()
    Dim r As Long
    'Use a "With" block to save having to constantly type "Worksheets("Sheet A")"
    'inside the block
    With Worksheets("Sheet A")
        'Loop through all values in column A, thus saving the trouble of 
        'hard-coding the last row number to be used
        For r = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            'Just copy the value directly from one worksheet to another, thus
            'avoiding copy/paste
            Worksheets("Sheet B").Range("A2").Value = .Cells(r, "A").Value
            Worksheets("Sheet B").PrintOut Copies:=1, _
                                           Collate:=True, _
                                           IgnorePrintAreas:=False
        Next r
    End With
End Sub

Upvotes: 3

Navdeep Singh
Navdeep Singh

Reputation: 1

Sub Copy_Cell()
    Dim row as Integer
    Do While row <= 337
        Sheets("Sheet A").Activate
        Sheets("Sheet A").Cells(row + 1, 1).Copy
        Sheets("Sheet B").Activate 
        Range("A2").Select
        ActiveSheet.Paste 
        ActiveWindow.SelectedSheets.PrintOut Copies:=1
        row = row + 1
    Loop
End sub

Upvotes: 0

Mikisz
Mikisz

Reputation: 404

    Sub Copy_Cell() ' Copy_Cell Macro

Dim i as Integer
For i = 1 To 337
    Sheets("Sheet A").Activate
    ActiveSheet.Cells(i + 1, 1).Select
    Selection.Copy
    Sheets("Sheet B").Activate
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
Next i
    End Sub Image Image

Upvotes: 0

Related Questions