View Full Version : [SOLVED:] VBA to copy specific cells to specific columns in different workbook
MRichmond
02-03-2015, 02:06 AM
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
02-03-2015, 05:55 AM
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
02-03-2015, 06:40 AM
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
02-03-2015, 07:39 AM
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
02-03-2015, 07:53 AM
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
02-03-2015, 08:47 AM
welcome.
thanks for the feedback and marking the thread as solved.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.