VBA Excel macro to create a small databasesheet using multiple sheets(new to macros, and interested to learn)

Question

I have started using macros since couple of weeks and am struggling with a question.

I am trying to create a data entry mask as part of a project and my aim is to store the data from the mask in sheet 1 to sheet 9 in a sequencial order, i managed to achieve this and clear the initial mask so that a new data could be entered.

The code ive managed to achieve the above function is:

Sub Macro1

Transfer the Data from Table 1 to Table 9

Sheets("Tabelle9").Select
Range("A2").Select      
ActiveCell.FormulaR1C1 = "=Tabelle1!R1C2"
Selection.AutoFill Destination:=Range("A2:A28"), Type:=xlFillDefault
Range("A2:A28").Select
Range("B2").Select      
ActiveCell.FormulaR1C1 = "=Tabelle1!R[2]C2"
Selection.AutoFill Destination:=Range("B2:B28"), Type:=xlFillDefault
Range("B2:B28").Select
Range("C2").Select      
ActiveCell.FormulaR1C1 = "=Tabelle1!R[2]C3"
Selection.AutoFill Destination:=Range("C2:C28"), Type:=xlFillDefault
Range("C2:C28").Select
Range("D2").Select      
ActiveCell.FormulaR1C1 = "=Tabelle1!R[2]C6"
Selection.AutoFill Destination:=Range("D2:D28"), Type:=xlFillDefault
Range("D2:D28").Select
Range("E2").Select      
ActiveCell.FormulaR1C1 = "=Tabelle1!R[2]C9"
Selection.AutoFill Destination:=Range("E2:E28"), Type:=xlFillDefault
Range("E2:E28").Select
Range("F2").Select      
ActiveCell.FormulaR1C1 = "=Tabelle1!R1C3"
Selection.AutoFill Destination:=Range("F2:F28"), Type:=xlFillDefault
Range("F2:F28").Select
Range("G2").Select      
ActiveCell.FormulaR1C1 = "=Tabelle1!R[2]C14"
Selection.AutoFill Destination:=Range("G2:G28"), Type:=xlFillDefault
Range("G2:G28").Select
Range("H2").Select      
ActiveCell.FormulaR1C1 = "=Tabelle1!R[2]C11"
Selection.AutoFill Destination:=Range("H2:H28"), Type:=xlFillDefault
Range("H2:H28").Select
Range("I2").Select      
ActiveCell.FormulaR1C1 = "=Tabelle1!R[2]C5"
Selection.AutoFill Destination:=Range("I2:I28"), Type:=xlFillDefault
Range("I2:I28").Select
Range("J2").Select      
ActiveCell.FormulaR1C1 = "=Tabelle1!R[2]C13"
Selection.AutoFill Destination:=Range("J2:J28"), Type:=xlFillDefault
Range("J2:J28").Select
Range("K2").Select      
ActiveCell.FormulaR1C1 = "=Tabelle1!R[2]C12"
Selection.AutoFill Destination:=Range("K2:K28"), Type:=xlFillDefault
Range("K2:K28").Select

To keep Table9 unaffected from Live Edit Table1

Sheets("Tabelle9").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

To clear all the fields in Table 1

Sheets("Tabelle1").Select
Range("A4:A30").Select
Selection.ClearContents
Range("C4:C30").Select
Selection.ClearContents
Range("E4:F30").Select
Selection.ClearContents
Range("H4:H30").Select
Selection.ClearContents
Range("J4:N30").Select
Selection.ClearContents

End Sub

Issues i face are that the data in Table 9 is being replaced everytime i run the macro using a command button. With lot of internet searches i tried to edit the code as follows:

Sub Trail

Sheets("Tabelle1").Select
Range("B1").Select      
Selection.Copy
Sheets("Tabelle9").Select
Range("A2").Select
Selection.End(xlDown).Select
Count = ActiveCell.Cells.Row + 1
Range("A" & Count).Select
Selection.PasteSpecial Paste:=xlPasteAll, 
Operation:=xlNone,SkipBlanks:= _True, Transpose:=True

End Sub

Here comes the tricky part that as a newbie i couldnt figure it out, would be very thankful if someone could guide me through this.

How can i paste the data in a new table by using reference cells and not absolute address of cell. (Ex- I would like to paste data from Table 1 to Table 9 in a sequence so that from each run of macro code exact 28 cells of data is saved and so that when i run the macro for second time the data is saved from cell 29 to 56)

Is it possible to copy and paste one field from Table 1 and paste it in 28 succesive cells in table 9(A1 of Table 1 be copied in all the A1-A28 in Table 9), if yes kindly enlight me with the method.

Thankful for any suggestions. Danke Schön!

Balasubramaniyan Ramadoss Balasubramaniyan Ramadoss · Accepted Answer · 2016-08-26T11:24:48

Please try this and change the Sheet name to original:

Sub Trail()
   Static Count As Integer
   Count = Count + 1
   Sheets("Sheet1").Select
   Range("B1").Select
   Selection.Copy
   Sheets("Sheet2").Select
   Sheet2.Range("A" & Count & ":A28").Select
   Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=True, Transpose:=True
   Selection.End(xlDown).Select
   Count = ActiveCell.Cells.Row
End Sub

VBA Excel macro to create a small databasesheet using multiple sheets(new to macros, and interested to learn)

1 Answers