Macro to verify cells and copy past values, Help!

[naviero1]

Good morning everyone,
I have these extraction on a sheet, let's call it "main" sheet. I have formulas on P5:Q1000 that depend on the extraction and change constantly every time the extraction is refreshed. So I am trying to find a way (through a macro push button) to copy past the new refreshed values from those two columns and paste them on another sheet called "dumper". Dumper might have other data that has been extracted before, so this macro would need to acknowledge that by checking which is the last value entered and hence, paste (values) the new values right underneath. Then get me back to the main sheet.

Sub filedumping()
'copia linha inteira com criterio
Dim i As Range
For Each i In Range("P5:P1000")
If i.Value > 0 Then
i.Select

'I think i need a Formula to select values from both row p and row q
'for every iteration



Selection.Copy
Sheets("Dumper").Range("A65000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

End If
Next i
End Sub

this is what I have so far, I would very much appreciate your input

 

[MrKowz]

Is something like this what you're looking for?

Public Sub CopyFileDump()
Dim LR      As Long

Dim sWS     As Worksheet, _
    dWS     As Worksheet
    
Set sWS = ActiveSheet
Set dWS = Sheets("dumper")

LR = dWS.Range("A" & Rows.Count).End(xlUp).Row + 1

sWS.Range("P5:Q1000").Copy
dWS.Range("A" & LR).PasteSpecial xlPasteValues

End Sub

 

[naviero1]

Yes it does the job, however, on the dumper file, I'm still having difficulties getting the data organized so that every time i execute this macro it organizes the data right after the last input

 

[MrKowz]

That's what the LR variable should be accomplishing - it is looking in column A of the sheet "dumper" and using the row after the last found value in column A to be the row where the new data is pasted. Is it possible that your column A data in "dumper" isn't always the true "last row" column?

 

[naviero1]

I think you are very correct, it isnt always a true missing value. That's why I thought that a "for each" formula on the pasting part would be able to help me on the matter, although it takes a bit longer than just fully snap shooting the two columns "P5:Q1000"

 

[MrKowz]

So would it be accurate to say that the true last row in "dumper" is the last row of column A or column B, whichever is greater?

 

[naviero1]

Yes pretty much. And from the "Main" sheet, the values that are greater than 0, on the range of P5:Q300 (instead of 1000) should be pasted onto the dumper, without spaces. Is that possible?

 

[MrKowz]

I'm trying to understand your condition "without spaces" So if a value is not greater than 0, should that value be completely omitted and now show a blank space? For example, lets look at the following dummy data:

Excel 2013/2016
	P	Q
5	1	10
6	-333	20
7	3	-999
8	4	-777
9	-555	30
10	5	40
11	-444	-888
12	6	60
Sheet3

Do you want the output to look like:

Excel 2013/2016
	A	B
1	1	10
2		20
3	3
4	4
5		30
6	5	40
7
8	6	60
Sheet3

or like:

Excel 2013/2016
	A	B
1	1	10
2	3	20
3	4	30
4	5	40
5	6	60
Sheet3

or like:

Excel 2013/2016
	A	B
1	1	10
2		20
3	3
4	4
5		30
6	5	40
7	6	60
Sheet3

 

[naviero1]

I apologize for the misunderstanding, so the data on columns P and Q are codes that I have created. If there is a code on P1 there will be a code on Q1.
The process should be as following:
1)check on ranges P5:Q300 is there is a value or string
2) if value or string, copy
3) Verify cell of last input ( copied value from last transaction, on dumper sheet)
4) Select cell that would continue the depository of data
5) Paste copied/selected cells as values on columns A and B respectively , P on A and Q in B
6)Go back to main (reset)

 

[MrKowz]

I think I understand now.

Try:

Public Sub CopyFileDump()
Dim LR      As Long

Dim sWS     As Worksheet, _
    dWS     As Worksheet
    
Dim rng     As Range, _
    rowx    As Long, _
    coly    As Long

Set sWS = ActiveSheet
Set dWS = Sheets("dumper")

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

LR = WorksheetFunction.Max(dWS.Range("A" & Rows.Count).End(xlUp).Row + 1, dWS.Range("B" & Rows.Count).End(xlUp).Row + 1)
rowx = LR
For Each rng In sWS.Range("P5:P300")
    If Len(rng.Value) > 0 Then
        rng.Resize(1, 2).Copy
        dWS.Range("A" & rowx).PasteSpecial xlPasteValues
        rowx = rowx + 1
    End If
Next rng

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .CutCopyMode = False
End With

End Sub