Hello,
I would like to copy cell values from Datawrksht to other worksheets by
clicking one button.
Like this:
Select A1 from Datawrksht and copy the value (not the formating) to cell C9
on Worksheet1, then select B1 from Datawrksht and copy the value to cell c10
in Worksheet2, Now, I will have to repeat this over and over later to select
many other cells that have to be copied to different sheets.
Here is the complication, once it is done copying and pasting, I would like
it to Delete Datawrksht and to open up the SAVE AS dialog box so that I can
save this as a new file.

Regards

Memphis

Hi Memphis,

When you "repeat this over and over later to select
many other cells that have to be copied to different sheets. . ."

1. Are you always using A1 from Datawrksht and C9 on Worksheet1
and B1 from Datawrksht and c10 in Worksheet2?

2. How do you know to repeat this several times?

3. What is the difference between 1 and 2 and
. . .many other cells that have to be copied to different
sheets.. .?


Dan

Ok, here it is.
I merged data from ACCES and dropped it into Dataworksheet, each row goes
from A1 trough CB1
So, I need to select cells within this row and paste them onto different
worksheets that need to capture information such as name, bank account,
address, payment amount, etc.. So each worksheet is for a different
information category which needs to be extracted from row1 in Dataworksheet.
Basically, this is to show the current information we have about the case.

Each row in Dataworksheet is info for a different case.

Then I need to create a file after I paste the info from Dataworksheet.

Yes the receiving cells in worksheet1 are scattered, they are not like
a1:c1, name goes in A1, SSN goes in B2, etc...

NOw, to save code, I would like to delete row A1 from Dataworksheet, thus
bumping row A2 and next time I run the code, this will refer to row A1 again,
but this row will obviously have info about another case.

A bit winded, but I hope you get the idea as to what I need to do.

Thank you




"dan dungan" wrote:

Hi Memphis,

When you "repeat this over and over later to select
many other cells that have to be copied to different sheets. . ."

1. Are you always using A1 from Datawrksht and C9 on Worksheet1
and B1 from Datawrksht and c10 in Worksheet2?

2. How do you know to repeat this several times?

3. What is the difference between 1 and 2 and
. . .many other cells that have to be copied to different
sheets.. .?


Dan

Hi Memphis,

What stops you from using queries and reports in access?

Dan

Maybe this will get you started

Option Base 1


Sub CopyData()

Dim Arr(5, 2)
Dim i As Long

'Source Target
Arr(1, 1) = "A1": Arr(1, 2) = "C1"
Arr(2, 1) = "B1": Arr(2, 2) = "D1"
Arr(3, 1) = "C1": Arr(3, 2) = "E1"
Arr(4, 1) = "D1": Arr(4, 2) = "F1"
Arr(5, 1) = "E1": Arr(5, 2) = "G1"


For i = 1 To 5
Sheets(1).Range(Arr(i, 1)).Copy Sheets(2).Range(Arr(i, 2))
Next

Sheets(2).Copy
ActiveWorkbook.SaveAs ("C:\AAA\Test.xls")
ActiveWorkbook.Close

End Sub


--
mdmackillop
------------------------------------------------------------------------
mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=66608

Thank you mdmckillop for the code, it allowed me to see that what I want to
do is possible.
I have come to realize that when I run this code, the cells I copy from
Sheet2 are then pasted over to sheets 3 and 4 as intended, but when this
happens they get rid of the cells borders that exist in Sheets 3 and 4. Also
some of the cells I am pasting the copied cells into are merged cells, so I
get an error " RUN TIME ERROR '1004':
Cannot Change Part of Merged Cell"
I would like to keep my merged cells and the existing borders in sheets 3
and 4.

Here is the code I have, i have four sheets 1 through 4 sheet one houses the
cmd button only, sheet 2 houses the case information, sheets 3 and 4 receive
the information found in sheet 2.
_____________________________________________
Private Sub CopyData_Click()
Dim Ax(3, 2)
Dim Bx(5, 4)

Dim i As Long
Dim Xi As Long

'Source Target
Ax(1, 1) = "A2": Ax(1, 2) = "B3"
Ax(2, 1) = "B2": Ax(2, 2) = "B5"
Ax(3, 1) = "C2": Ax(3, 2) = "B8"
'Source Target
Bx(4, 3) = "F2": Bx(4, 4) = "B4"
Bx(5, 3) = "G2": Bx(5, 4) = "B6"

For i = 1 To 3
For Xi = 4 To 5

Sheets("Sheet2").Range(Ax(i, 1)).Copy Sheets("Sheet3").Range(Ax(i, 2))
Sheets("Sheet2").Range(Bx(Xi, 3)).Copy Sheets("Sheet4").Range(Bx(Xi, 4))
Next
Sheets("Sheet3").Select
Next
Sheets("Sheet4").Select

Sheets(Array("Sheet3", "Sheet4")).Copy

'ActiveWorkbook.SaveAs ("C:\AAA\BogusFileName.xls")
'ActiveWorkbook.Close
End Sub
__________________________________________________ ____

Thank you for your help in advance.

Memphis