VBA copy between sheets stops after 5 entries.

[jamesj547]

This code works until you get 5 records on the report table. I cant figure it out. Help please.


Sub ExportAndClear()
'EXPORT REPORT
Sheets("Grid").Select
Range("c3:h20").Copy

On Error Resume Next
Dim emptyReportCell As Range
For Each emptyReportCell In Worksheets("Report").Range("c11:c32").Cells
If Len(emptyReportCell) = 0 Then
emptyReportCell.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Exit For
End If
Next




' ExportAndClear Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Sheets("Grid").Select
Range("H23").Select
Selection.Copy
On Error Resume Next
Dim emptyCell As Range
For Each emptyCell In ActiveSheet.Range("k2:k22").Cells
If Len(emptyCell) = 0 Then
emptyCell.Select
Exit For
End If
Next
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Grid").Select

Range("C3:E22").Select
Selection.ClearContents
Range("G3:G22").Select
Selection.ClearContents
Range("C3").Select

Worksheets("Grid").Activate
'

End Sub

 

[dave3009]

Spoke too soon. its dumping erroneous data weird places.

It would be useful to know what that means so it can be adjusted. Thanks

 

[jamesj547]

It would be useful to know what that means so it can be adjusted. Thanks

 

[dave3009]

Thanks, what you have to remember is that I'm not sat with you and I don't know exactly what you're trying to achieve. I did ask for an explanation, which I got but it was somewhat vague. I can't really proceed without more specific detail.

 

[jamesj547]

Thanks, what you have to remember is that I'm not sat with you and I don't know exactly what you're trying to achieve. I did ask for an explanation, which I got but it was somewhat vague. I can't really proceed without more specific detail.

 

[jamesj547]

						Date:
							SALES QUOTATION
					PH:
					FAX:
	To:			Project:
	Attn:
PROVIDE AND INSTALL:
		Description	Options	Item	Cost	Qty	Total
		14g
		(label louver requirement) REPLACEMENT DOOR
		18g
		4 1/2" standard or heavyweight 5" Hinge standard or heavyweight 1 1/2 pair up to and including 7'6" 2 pair over 7'6" to 10'0" Anchor hinge set
		16g
		14g	20, 24, 26,28 .210, 30	30X68-14G	1,271	2	$2,542
		16g
		(label louver requirement) REPLACEMENT DOOR
		16g
		16g
		16g
		14g
		(label louver requirement) REPLACEMENT DOOR
		14g
		16g
		16g
		14g
		14g
		4 1/2" standard or heavyweight 5" Hinge standard or heavyweight 1 1/2 pair up to and including 7'6" 2 pair over 7'6" to 10'0" Anchor hinge set
					#####
					#####
					#####
					#SPILL!
					#SPILL!
					#SPILL!
		#N/A	#N/A	#N/A	#N/A	##	#N/A
		#N/A	#N/A	#N/A	#N/A	##	#N/A
		#N/A	#N/A	#N/A	#N/A	##	#N/A

 

[jamesj547]

		Select Description first	Then Options	Then Item
	Item #	Description	Options	Item	Unit Cost	Count	Cost			Totals
	1				#SPILL!					$2,542
	2				#SPILL!					$0
	3				#SPILL!					$0
	4				#SPILL!					$0
	5				#SPILL!					$0
	6				#SPILL!					$0
	7				#SPILL!					$0
	8				#SPILL!
	9				#SPILL!
	10				#SPILL!
	11				#SPILL!
	12				#SPILL!
	13				#SPILL!
	14				#SPILL!
	15				#SPILL!
	16				#SPILL!
	17				#SPILL!
	18				#SPILL!
	19				#SPILL!
	20				#SPILL!
					Total		$0			$2,542
								send to report
						RESET

 

[jamesj547]

tried to copy mini sheet but it never finished compiling.

 

[jamesj547]

Thanks, what you have to remember is that I'm not sat with you and I don't know exactly what you're trying to achieve. I did ask for an explanation, which I got but it was somewhat vague. I can't really proceed without more specific detail.

The functionality I am looking for is this: Grid sheet generates X number of lines of pricing, then it is sent to report sheet by button press to activate macro. The macro should check for a clear row to paste data into. Then the user can go back to grid sheet and do it again until report is built. The current code does that but is dropping text in places it shouldn't along with doing what it should. Before your code mine would just stop after 5 lines on the report page. If i can clarify further let me know. I still am having trouble with XL2BB.

 

[jamesj547]

I used half your code and half mine and now it works. Now if I could prevent those spill errors. Thanks for your help!


Sub ExportAndClear()
'EXPORT REPORT
Sheets("Grid").Select
Range("c3:h20").Copy

Dim lstReportRow As Long
Dim lstGridRow As Long

'Exports data from grid to report

With Sheets("Report")
lstReportRow = .Range("C32").End(xlUp).Row 'Find the last row in the report sheet
lstReportRow = IIf(lstReportRow < 11, 12, lstReportRow + 1) 'Optional test that it if is below the headers

.Range("C" & lstReportRow).Resize(21, 6).Value = Sheets("Grid").Range("C3:H20").Value 'Set the value of the report sheet to the grid of dropdowns
End With



' ExportAndClear Macro
'
' Keyboard Shortcut: Ctrl+Shift+D


Sheets("Grid").Select
Range("H23").Select
Selection.Copy
On Error Resume Next
Dim emptyCell As Range
For Each emptyCell In ActiveSheet.Range("k2:k22").Cells
If Len(emptyCell) = 0 Then
emptyCell.Select
Exit For
End If
Next
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Grid").Select

Range("C3:E22").Select
Selection.ClearContents
Range("G3:G22").Select
Selection.ClearContents
Range("C3").Select

Worksheets("Report").Activate
'

End Sub