Hello Excel Gurus,

I am currently preparing one database sheets and would like to perform
following action on the hit of couple of buttons,

I have two sheets name "entry" and "database" in a workbook.

I have two buttons on entry sheet called "transfer" and "call"

entry sheet has following cells/Ranges required to filled first time for
every new entry (all cells contain;Name or currency or number, etc)

("entry" sheet) Range "A1" and Range "A3:C7" (total 25 value in 2D array)

(Value in Range "A1" is important value and used to call all the value in
Range "A3:C7" from "database" sheet, lets say for an example Range"A1"=JIM)


when i fill above cells/Ranges in entry sheet and hit "transfer" button i
would like to to transfer all 26 value( Range A1 and 25 value from 2D array,
row by row) from "entry" sheet into "database" sheet in 1D Array format, such
as shown in below rage and clear all the cell value from "entry" sheet

"database" sheet
Range "A1:Z26"
(during the trasfer range A1 in entry sheet will be transfered in A1 in
database sheet, while entry sheet range "A3:C7" should transfer in database
sheet row by row, first row first and then second, so on)

Now if i type 'JIM' in Range "A1"(entry sheet) and hit "Call" button, i want
to bring all the same value in Range "A3:C7" (entry sheet) what i typed in
before and was stored in "database" sheet (Range"A1:Z26")

But if i type different value ( for an example "VIC" ) in Range "A1" (entry
sheet) and filled Range "A3:C7" (entry sheet) and hit "transfer" button, it
should transfer all 26 value in "database" sheet just above the existing
Range"A1:Z26" (So that alway recent data entered remains on the top row of
the "database" sheet)

As mentioned above, if i type "VIC" in cell A1 in entry sheet and hit "call"
button, it should fill all remaining value in Range "A3:C7" from "database"
sheet.

Please disregard worry of Uppercase or lowercase letter issues, I will be
restricting Range "A1" (entry sheet) by data validation.

Any Help, will be highly appriciated.

Many Thanks in advance,

Regards,
Learner

this is a bit simplistic but gives you the idea:

Option Explicit

Sub Transfer()
Dim wsEntry As Worksheet
Set wsEntry = Worksheets("entry")
With Worksheets("database")
.Range("A1") = wsEntry.Range("A1")
.Range("B1:D1").Value = wsEntry.Range("A3:C3").Value
.Range("E1:F1").Value = wsEntry.Range("A4:C4").Value
.Range("G1:I1").Value = wsEntry.Range("A5:C5").Value
End With
End Sub



"Learner" wrote:

Hello Excel Gurus,

I am currently preparing one database sheets and would like to perform
following action on the hit of couple of buttons,

I have two sheets name "entry" and "database" in a workbook.

I have two buttons on entry sheet called "transfer" and "call"

entry sheet has following cells/Ranges required to filled first time for
every new entry (all cells contain;Name or currency or number, etc)

("entry" sheet) Range "A1" and Range "A3:C7" (total 25 value in 2D array)

(Value in Range "A1" is important value and used to call all the value in
Range "A3:C7" from "database" sheet, lets say for an example Range"A1"=JIM)


when i fill above cells/Ranges in entry sheet and hit "transfer" button i
would like to to transfer all 26 value( Range A1 and 25 value from 2D array,
row by row) from "entry" sheet into "database" sheet in 1D Array format, such
as shown in below rage and clear all the cell value from "entry" sheet

"database" sheet
Range "A1:Z26"
(during the trasfer range A1 in entry sheet will be transfered in A1 in
database sheet, while entry sheet range "A3:C7" should transfer in database
sheet row by row, first row first and then second, so on)

Now if i type 'JIM' in Range "A1"(entry sheet) and hit "Call" button, i want
to bring all the same value in Range "A3:C7" (entry sheet) what i typed in
before and was stored in "database" sheet (Range"A1:Z26")

But if i type different value ( for an example "VIC" ) in Range "A1" (entry
sheet) and filled Range "A3:C7" (entry sheet) and hit "transfer" button, it
should transfer all 26 value in "database" sheet just above the existing
Range"A1:Z26" (So that alway recent data entered remains on the top row of
the "database" sheet)

As mentioned above, if i type "VIC" in cell A1 in entry sheet and hit "call"
button, it should fill all remaining value in Range "A3:C7" from "database"
sheet.

Please disregard worry of Uppercase or lowercase letter issues, I will be
restricting Range "A1" (entry sheet) by data validation.

Any Help, will be highly appriciated.

Many Thanks in advance,

Regards,
Learner

Patrick,

The reason i have asked with simple 2D array (three column and three row
only-9 cells) to understand the array lood and transfer macros.

Actually i have got 2D array of 14 rows and 14 columns (196 value), using
example you have given me would make things very tough. is there any other
solution we can implement using variable for assigning value,etc

let me know what do you think

cheers
Learner

"Patrick Molloy" wrote:

this is a bit simplistic but gives you the idea:

Option Explicit

Sub Transfer()
Dim wsEntry As Worksheet
Set wsEntry = Worksheets("entry")
With Worksheets("database")
.Range("A1") = wsEntry.Range("A1")
.Range("B1:D1").Value = wsEntry.Range("A3:C3").Value
.Range("E1:F1").Value = wsEntry.Range("A4:C4").Value
.Range("G1:I1").Value = wsEntry.Range("A5:C5").Value
End With
End Sub



"Learner" wrote:

Hello Excel Gurus,

I am currently preparing one database sheets and would like to perform
following action on the hit of couple of buttons,

I have two sheets name "entry" and "database" in a workbook.

I have two buttons on entry sheet called "transfer" and "call"

entry sheet has following cells/Ranges required to filled first time for
every new entry (all cells contain;Name or currency or number, etc)

("entry" sheet) Range "A1" and Range "A3:C7" (total 25 value in 2D array)

(Value in Range "A1" is important value and used to call all the value in
Range "A3:C7" from "database" sheet, lets say for an example Range"A1"=JIM)


when i fill above cells/Ranges in entry sheet and hit "transfer" button i
would like to to transfer all 26 value( Range A1 and 25 value from 2D array,
row by row) from "entry" sheet into "database" sheet in 1D Array format, such
as shown in below rage and clear all the cell value from "entry" sheet

"database" sheet
Range "A1:Z26"
(during the trasfer range A1 in entry sheet will be transfered in A1 in
database sheet, while entry sheet range "A3:C7" should transfer in database
sheet row by row, first row first and then second, so on)

Now if i type 'JIM' in Range "A1"(entry sheet) and hit "Call" button, i want
to bring all the same value in Range "A3:C7" (entry sheet) what i typed in
before and was stored in "database" sheet (Range"A1:Z26")

But if i type different value ( for an example "VIC" ) in Range "A1" (entry
sheet) and filled Range "A3:C7" (entry sheet) and hit "transfer" button, it
should transfer all 26 value in "database" sheet just above the existing
Range"A1:Z26" (So that alway recent data entered remains on the top row of
the "database" sheet)

As mentioned above, if i type "VIC" in cell A1 in entry sheet and hit "call"
button, it should fill all remaining value in Range "A3:C7" from "database"
sheet.

Please disregard worry of Uppercase or lowercase letter issues, I will be
restricting Range "A1" (entry sheet) by data validation.

Any Help, will be highly appriciated.

Many Thanks in advance,

Regards,
Learner