PDA

View Full Version : Minor Flaw - Help and understanding required if possible



Chris_AAA
10-02-2014, 03:34 AM
Hi Guys

Im learning the ropes of VBA.

I am attempting to write a project and most of its A) Very basic, but works.

I have one piece of code below that is puzzling me....

Im wanting to copy from one sheet as per below example, and go to another sheet, find last blank row of data, and paste in my results from the raw data file example.

For some reason, it copys cells in raw data, but is not pasting the data into summary sheet as required.

Can anyone see any mistakes in my code?

Also to take this learning experience a little further...

How can I tell the code, that in future examples.... if there is genuinely no data found to paste, to skip, and move onto the next part of my code.

In the below instance I have checked and there is definately data to paste special, so initially I wondered if anyone can see any errors, and then part b to advise how to skip if no data found.


'No .2 File.
Windows("RAW DATA.csv").Activate
Sheets("DATA TAB 3").Select
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy


'Paste all found data beneath previous found summary data.
Windows("Summary Sheet.xlsx").Activate
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Thank you kindly.

Jan Karel Pieterse
10-02-2014, 04:40 AM
Does this work:


Workbooks("RAW DATA.csv").Sheets("DATA TAB 3").Range("2:2").CurrentRegion.Copy
With ActiveSheet
.Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
End With

SamT
10-02-2014, 09:18 AM
Some ideas

With Workbooks("RAW DATA.csv").Sheets("DATA TAB 3")

'Assumes no blanks in any data
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
If LastRow < 2 Then Exit Sub


'If possible blanks in data use
'LastRow = .UsedRange.Rows(UsedRange.Rows.Count).Row
'If CountA(.Range("2:" & LastRow)) = 0 Then Exit sub


.Range("2:" & LastRow).Copy
End With

Workbooks("Summary Sheet.xlsx").ActiveSheet. _
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0). _
PasteSpecial Paste:=xlPasteValues

'Clear the Clipboard
Application.CutCopyMode = False