Move data from one cell to another cell in a different sheet when printing

[DavyJones90]

Hi,
I am trying to move data from on cell into a different sheet in a different celll when printing. the complication is. in one sheet I only have data

and in the other sheet I have a form

Each line in the first sheet will fill the entire form, but I don't actually need the form to be filled unless it is printed, at which point it should fill this form as many times as there are lines in the first table. so if I have 5 projects filled out in the first sheet, then 5 forms with that exact data should be printed.

I have been searching for hours, but no idea how to go about this.

Thank you for your help

 

[HaHoBe]

Hi DavyJones,

the part you showed in the first sheet is only to fill Range("C21:C29") - are yu only talking about this as the information for the rest needs to come from a different source. And what about the data after it has been printed - where will be noted that it already has been printed?

2 more columns for the first sheet like

MrE_1229210_1702518_move data from one_230206.xlsm
	K	L
1	Ready to Print	Printed
2	9
Quelle

Cell Formulas
Range	Formula
K2	K2	=COUNTA(B2:J2)

This will only be part of the solution to your problem as you need to either get information by using VLookup for any customer or use VBA to collect this as well. A start:

Public Sub MrE_1229210_1702518()
' https://www.mrexcel.com/board/threads/move-data-from-one-cell-to-another-cell-in-a-different-sheet-when-printing.1229210/

'only solution to a part of filling the form

Dim wsSrc As Worksheet
Dim wsTarg As Worksheet
Dim lngCounter As Long

Set wsSrc = ThisWorkbook.Worksheets("Quelle")
Set wsTarg = ThisWorkbook.Worksheets("Ziel")

With wsSrc
  For lngCounter = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
    If .Cells(lngCounter, "K").Value = 9 Then
      .Cells(lngCounter, "B").Resize(1, 9).Copy
      wsTarg.Range("C21").PasteSpecial Paste:=xlPasteAll, Transpose:=True
      Application.CutCopyMode = False
      wsTarg.PrintPreview
'      'change commands if you want the part printed out
'      wsTarg.PrintOut
      .Cells(lngCounter, "L").Value = Now
    End If
  Next lngCounter
End With

Set wsTarg = Nothing
Set wsSrc = Nothing
End Sub

With my sample the first sheet after a print may look like

MrE_1229210_1702518_move data from one_230206.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	H	H	H	H	H	H	H	H	H	Ready to Print	Printed
2	a1	Data	Data	Data	Data	Data	Data	Data	Data	Data	9	06.02.2023 20:29
3	a2	Zwei	Zwei		Zwei	Zwei	Zwei	Zwei	Zwei	Zwei	8
4	a3	Drei	Drei	Drei	Drei	Drei	Drei	Drei	Drei	Drei	9	06.02.2023 20:30
5	a4										0
Quelle

Cell Formulas
Range	Formula
K2:K5	K2	=COUNTA(B2:J2)

Ciao,
Holger