Excel Macro Problem Copy Data from sheet1 to 2 but into different cells

[GreenOnes]

Hi all,

I'm a complete noobie at Excel VBA,,,

My problem is...

I have a spreadsheet and on page 1, named "MAIN"..

I want to be able to select a row, and have the cells copied to another
sheet

Copy the data from cells C4, D4, E4, F4, K4, L4 and P4..(from sheet
MAIN)

into sheet Billing Statement..

B9, C9, B10, B14, A17, D17, F10 Respectively...and the Cell numbers
increment by one as each row goes down...


The sheet for the data to be copied to is called Billing Statement...

Any help on this would be fantastic,,,been trying to figure it out for
ages..

I have about 100 lines, and would like to make one button that affects
which ever row is selected and copys the data from the above mentioned
cells to the ones mentioned afterwards..

Obvisously I'd rather not manually record over 100 different buttons,

Feel free to post here or mail me directly if you need more
information.

Thanks!

 

[Nigel]

For the selected cell(s) in a row you can extract the row number using
ActiveCell.Row
so your source rows can be dynamically changed as each row is selected.
You need to ensure the activesheet is MAIN and the row range is within the
limits; option in following code......

Sub CreateBillDoc()
Dim wsD As Worksheet, wsB As Worksheet, xARow As Long
Dim minRow As Long, maxRow As Long
Set wsD = Worksheets("MAIN")
Set wsB = Worksheets("Billing Statement")
' set upper and lower limits
minRow = 10
maxRow = 100

If ActiveSheet.Name = wsD.Name Then
xARow = ActiveCell.Row
If xARow >= minRow And xARow <= maxRow Then
wsB.Range("B9") = wsD.Cells(xARow, "C")
wsB.Range("C9") = wsD.Cells(xARow, "D")
wsB.Range("B10") = wsD.Cells(xARow, "E")
wsB.Range("B14") = wsD.Cells(xARow, "F")
wsB.Range("A17") = wsD.Cells(xARow, "K")
wsB.Range("D17") = wsD.Cells(xARow, "L")
wsB.Range("F10") = wsD.Cells(xARow, "P")
Else
MsgBox "Selected a row within the range: " & minRow & " to " & maxRow
End If
Else
MsgBox "Please select the MAIN sheet before transferring data"
End If
End Sub

 

[pgchop]

Sheets("Sheet2").Range("B9").Formula = Sheets("Sheet1").Range("C4")
Sheets("Sheet2").Range("C9").Formula = Sheets("Sheet1").Range("D4")
Sheets("Sheet2").Range("B10").Formula = Sheets("Sheet1").Range("E4")
Sheets("Sheet2").Range("B14").Formula = Sheets("Sheet1").Range("F4")

 

[pgchop]

.... and if you dont want to specify the address using "A1" notation,
use
cells(row,column) for the range...

 

[Dave Peterson]

Is this so you can print separate billing statements?

If yes, you may want too look at Debra Dalgleish's site:
http://www.contextures.com/xlForm03.html

If you're trying to build a data entry form:
http://www.contextures.com/xlForm02.html

 

[GreenOnes]

Wow, you guys are quality!

Many, many many many thanks! Been racking my brains (what little there
is..) for ages over this one! lol...

Cheers! The code worked a treat, and I changed it slightly and made it
do something else useful to me too!!

You star!

Thanks again
Sam