VBA to copy specific cells to specific columns in different workbook

[MRichmond]

Looking for some help, as I cant quite find exactly what I'm looking for.

I have a sales quote form template that certain data is captured on, in certain cells. I need to transfer this data into another workbook, into certain columns, but onto two sheets, one being a summary where all quotes are captured, the second being month specific, so January quotes go onto Januarys tab, etc. etc. Obviously this data will need to go onto the next empty line on the relevant sheet.

I'm attaching samples of both workbooks. Quote Sheet will be a template and the source data while Quote Capture Register 2015 is the destination file. I have highlighted the cells to be copied and where they are to be copied to.

If someone could assist I would be very grateful.

thanks for you time.

[mancubus]

pointer:

Workbooks("Destination_Workbook.xlsm").Worksheets("Destination_Worksheet").Range(Destination_Cell).Value = Workbooks("Source_Workbook.xlsm").Worksheets("Source_Worksheet").Range(Source_Cell).Value


you can use variables to easily write and read the code:


Dim sWS As Worksheet, dWS As Worksheet

Set sWS = Workbooks("Source_Workbook.xlsm").Worksheets("Source_Worksheet")
Set dWS = Workbooks("Destination_Workbook.xlsm").Worksheets("Destination_Worksheet")

'ex1:
dWS.Range("A1").Value = sWS.Range("Z24").Value

'ex2:
dWS.Range("F46").Value = sWS.Range("A24").Value * sWS.Range("R17").Value

'...
'...
'...



you can run your 'final' code from both workbooks or from another workbook.

[MRichmond]

Thanks for your response mancubus, that's has helped me get started, and using your pointer and example I can get it to fill the summary sheet.

However, I'm not experienced enough to modify the code to select the next empty row on the summary next time a form is submitted so everytime I enter new data, it just goes into row 4 everytime instead of the next blank row.

Any chance of a pointer on how to do that bit?

[mancubus]

you are welcome.


first declare a variable which will hold the last non blank cell's row number

Dim LastRow as Long

you can find the last blank non cell in column A like this:


LastRow = dWS.Cells(Rows.Count, "A").End(xlUp).Row
or

LastRow = dWS.Cells(Rows.Count, 1).End(xlUp).Row
or

LastRow = dWS.Range("A" & Rows.Count).End(xlUp).Row

(Rows.Count gives total nuımber of rows in a worksheet. Range("A" & Rows.Count) = Range("A1048576"))

so first blank cell in colum A is LastRow + 1

ex1 will be

dWS.Range("A" & LastRow + 1).Value = sWS.Range("Z24").Value


or you can directly assign the row number of first non blank cell in column A

LastRow = dWS.Range("A" & Rows.Count).End(xlUp).Offset(1,0).Row
then

dWS.Range("A" & LastRow).Value = sWS.Range("Z24").Value

[MRichmond]

mancubus, you are the Man. Thanks for the help, that's doing exactly what I wanted for the first part. I'll just copy and paste for the second part cos even I can do that :cloud9:. :beerchug:

[mancubus]

welcome.

thanks for the feedback and marking the thread as solved.