Solved Move items from Price sheet to Quote

May 11, 2016 at 05:26:52
Specs: Windows 7
I have a warranty calculator that spits out pricing for monthly, annual, quarterly pricing depending on the age, type, and price of each item selected. (uses VLookup from another sheet).

I need the document to automatically copy the TEXT (not formulas) from cells B5:E50 on My "Calculator" sheet to cells B14:E60 on my "Quote" sheet. I need it to only copy over the cells in those range which are not empty. The Quote sheet should not have any blank rows in above listed cell array.

I keep seeing solved Macros solutions for this, but I can't understand the macros enough to edit the formula for my own use.


See More: Move items from Price sheet to Quote

Report •


✔ Best Answer
May 11, 2016 at 11:07:16
The following code assumes that Row 14 of the Quote sheet already has your Column headings in the cells and that you don't want the data pasted below Row 60. If you paste in data that extends past Row 60, it will loop back up and start the paste in Row 15, overwriting anything that is already there. All of that is easy to modify if that is an issue. I just need the details.

Sub CopyData()
'Loop through B5:B50
 For Each itm In Sheets("Calculator").Range("B5:B50")
'Copy B:E if cells not empty
   If itm <> "" Then
    nxtRw = Sheets("Quote").Range("B60").End(xlUp).Row + 1
     Range("B" & itm.Row & ":E" & itm.Row).Copy
     Sheets("Quote").Range("B" & nxtRw).PasteSpecial Paste:=xlValues
   End If
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
May 11, 2016 at 07:03:15
I'm a little confused by your request.

To start with, the source range (B5:E50) is smaller than the destination range (B14:E60) by 1 Row, which leads to some confusion as to what gets copied to where.

Is it a simple, consistent, one-to-one copy such as this, or is it more complicated?

Calculator!B5 --> Quote!B14
Calculator!C5 --> Quote!C14
etc.

re: "I need it to only copy over the cells in those range which are not empty. "

I'm not sure if that means that the code should only look at the Calculator sheet and copy any and all cells that contains data or if it means that is also needs to look at the Quote sheet and not copy to a Quote sheet cell that already contains data.

Please clarify your requirements and we'll see what we can offer.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 11, 2016 at 07:55:31
Sorry for the confusion-

The destination range can be one cell larger, so that they match. (there is a variant number of rows for each time we use this quote document, so sometimes it might just be five rows and sometimes maybe 10)

Yes, it is a simple, consistent, one-to-one copy like you showed above.

The code should look at the calculator sheet range and copy any and all cells that contain data. The code should then put the data into the next available blank row on the Quote sheet range.


Report •

#3
May 11, 2016 at 08:50:11
You have said 2 very different things:

re: "Yes, it is a simple, consistent, one-to-one copy like you showed above."

I showed an example of specific cells being copied to specific cells.

Calculator!B5 --> Quote!B14
Calculator!C5 --> Quote!C14
etc.

re: "The code should then put the data into the next available blank row on the Quote sheet range."

That is not the same thing as specific cells being copied to specific cells. Let's try an different example:

Let's say there is data in Calculator!B5 and the "next available blank row" on the Quote sheet is Row 20. It sounds like Calculator!B5 would be copied to Quote!B20. Now Row 20 is no longer blank, so where would Calculator!C5 be copied to? Quote!B21? Do you see my confusion?

It is still not clear, at least not to me, what is being copied to where. Keep in mind that we cannot see your workbook from where we are sitting, so you need to be very specific with your requirements.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
May 11, 2016 at 10:44:46
wow I'm so sorry. I'm confusing myself talking about this lol. Thanks for your patience with me.

Here is a sample of the Calculator Page, Cells A4:F13

Price	Item Type	Qty	Location	Installed	Total
600.00	16 CH DVR	1.00	Back Office 	5/4/2013	 $852.00 
800.00	Analog PTZ	2.00	Roof	        5/4/2014         $1,648.00 
300.00	Analog Cam	8.00	Indoor 	        5/4/2014	 $2,418.00 
300.00	Analog Cam	2.00	Outdoor 	5/5/2015	 $609.00 
					  
					  
800.00	Analog PTZ	4.00	Back House 	5/5/2016	 $3,200.00 
300.00	8 CH DVR	1.00	Back House 	5/5/2016	 $300.00 
					  

Here is a sample of what I'd like the result to be on the Quote Page, cells : B14:E28

Item Type	Qty	Location	Notes
16 CH DVR	1.00	Back Office 	
Analog PTZ	2.00	Roof	
Analog Cam	8.00	Indoor 	
Analog Cam	2.00	Outdoor 	
Analog PTZ	4.00	Back House 	
8 CH DVR	1.00	Back House 	
			
			
			
			
			
			
			
			
			

So, you can see i'm just trying to neatly pull the information from the calculator page over to the Quote page. (Hopefully).


Report •

#5
May 11, 2016 at 11:07:16
✔ Best Answer
The following code assumes that Row 14 of the Quote sheet already has your Column headings in the cells and that you don't want the data pasted below Row 60. If you paste in data that extends past Row 60, it will loop back up and start the paste in Row 15, overwriting anything that is already there. All of that is easy to modify if that is an issue. I just need the details.

Sub CopyData()
'Loop through B5:B50
 For Each itm In Sheets("Calculator").Range("B5:B50")
'Copy B:E if cells not empty
   If itm <> "" Then
    nxtRw = Sheets("Quote").Range("B60").End(xlUp).Row + 1
     Range("B" & itm.Row & ":E" & itm.Row).Copy
     Sheets("Quote").Range("B" & nxtRw).PasteSpecial Paste:=xlValues
   End If
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Ask Question