VBA to copy non blank cells to new sheet

[patsism]

I have two sheets "input sheet" & "sheet3", I want to copy a dynamic range (range dependent on input in I3:P20) from "input sheet" A3:G20 to next blank row on "sheet3". Because there is a formula in cells A3:G20 when I select the current region it picks up all the cells not just the ones with visible data and copies it all. On "sheet3" it appears the cells are blank but when copy/paste repeated it starts at the end of the 'blank' cells. I a have tried what I can find as far as Paste Special but no luck so far. Code is below and I tried to add a mini sheet (not sure if it worked):

Sheets("input sheet").Select
Range("A3").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("Sheet3").Select
Selection.End(xlDown).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False
ActiveWindow.SmallScroll Down:=15
Application.CutCopyMode = False
Range("a1").Select

Input Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	-290321-01-A								Main Description			290321-01-A			3 digits
2													Batch Size	1.00	Actual Used	Lot #
3	-290321-01-A	Main Description	Data Field 1	1145	680	g	428531 820		Data Field 1			1145	680	g	680	428531 820
4	-290321-01-A	Main Description	Data Field 2	1146	6	l	AL 04		Data Field 2			1146	6	l	6	AL 04
5	-290321-01-A	Main Description	Data Field 3	1147	100	lbs	2280615		Data Field 3			1147	100	lbs	100	2280615
6	-290321-01-A	Main Description	Data Field 4	1148	50	g	2280022		Data Field 4			1148	50	g	50	2280022
7	-290321-01-A	Main Description	Data Field 5	1149	30	g	267025		Data Field 5			1149	30	g	30	267025
8
9
10
11
12
13
14
Input sheet

Cell Formulas
Range	Formula
A1	A1	=CONCATENATE(M1,"-",L1)
A3:A14	A3	=IF(I3<>"",$A$1,"")
B3:B14	B3	=IF(I3<>"",$I$1,"")
C3:C14	C3	=IF(I3<>"",$I3,"")
D3:D5	D3	=IF(I$3<>"",L3,"")
E3:E14	E3	=IF($I3<>"",O3,"")
D6:D14,F3:F14	F3	=IF($I3<>"",N3,"")
G3:G14	G3	=IF($I3<>"",P3,"")

Named Ranges
Name	Refers To	Cells
'Input sheet'!Print_Area	='Input sheet'!$I$1:$Q$20	B3:B14

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub patsism()
   With Sheets("input sheet")
      With .Range("A3:G" & .Range("I" & Rows.Count).End(xlUp).Row)
         Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(.Rows.Count, 7).Value = .Value
      End With
   End With
End Sub

 

[patsism]

Hi & welcome to MrExcel.
How about

Sub patsism()
   With Sheets("input sheet")
      With .Range("A3:G" & .Range("I" & Rows.Count).End(xlUp).Row)
         Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(.Rows.Count, 7).Value = .Value
      End With
   End With
End Sub

Thanks, that worked. Obviously, I'm new to VBA - almost seems to simple

 

[Fluff]

You're welcome & thanks for the feedback.

 

[patsism]

Same process, different workbook. Code runs ok until I try and add a paste special. I am not familiar with With/End With so don't know if that is causing the problem.
This is the code I have that works (except for formulas paste over as well) I removed the With/End With and .value
Range("ProductDump").Copy Sheets("MasterDumpProduction").Range("A" & Rows.Count).End(xlUp).Offset(1)

 

[patsism]

Same process, different workbook. Code runs ok until I try and add a paste special. I am not familiar with With/End With so don't know if that is causing the problem.
This is the code I have that works (except for formulas paste over as well) I removed the With/End With and .value
Range("ProductDump").Copy Sheets("MasterDumpProduction").Range("A" & Rows.Count).End(xlUp).Offset(1)

Answered my own question with a little more skulking It seems the I had to include the With/End With portion
With Sheets("input sheet")
With .Range("ProductDump").Copy
Sheets("MasterDumpProduction").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With
End With

 

[Fluff]

Glad you sorted it & thanks for the feedback.