Copy data from 2 sheets onto a 3rd sheet

swjtx99
swjtx99 used Ask the Experts™
on
I have 3 sheets.

1. System1
2. System2
3. Combined

What I am trying to do is copy the contents of each System1/2 sheet into Combined. The code below works fine unless there is no data on System1, then it can't find the last entry to copy the data into Combined.

I need to copy whatever data is on System1 staring at row A2 into Combined starting at row A2, then at the end of that, copy in the data from System2 starting at A2 into Combined starting at the end of the data already there from System1 (if any)

Like I said, this works fine unless System1 is blank, then it fails to correctly copy the data from System2 on to Combined.



    Sheets("System1").Select
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 'find cells on sheet System1 with data and select them
    Selection.Copy
    Sheets("Combined").Select
    Range("A2").Select
    ActiveSheet.Paste 'paste data found on System1 sheet into Combined sheet
    Sheets("System2").Select
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select  'find cells on sheet System2 sheet with data and select them
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Combined").Select
    Range("B2").Select
    Selection.End(xlDown).Select 'Find the last row of data pasted into Combined from System1 sheet by looking at the last entry in colum B
    ActiveCell.Offset(1, -1).Select 'Find the next open cell in column A
    ActiveSheet.Paste 'Paste the data from sheet System2 into Combined starting at the next blank row after the data pasted in from System1 sheet.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Seems like checking if the sheets are empty prior to copying/pasting would solve the problem but I am struggling with the syntax in the IF statements...

    Sheets("Labmate").Select
    If Range(A1).FormulaR1C1 = "" Then 'Check if A1 is blank ?
    Else
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets("Combined").Select
    Range("A2").Select
    ActiveSheet.Paste
    End If
    If Range(A1).FormulaR1C1 = "" Then 'Check if A1 is blank ?
    Else
    Sheets("Gage").Select
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Combined").Select
    Range("B2").Select
    'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, -1).Select
    ActiveSheet.Paste
    End If
Commented:
Well, not sure if this is the best way to do it but I have solved my problem.



    Sheets("Sheet1").Select
    If IsEmpty(Range("A1")) Then GoTo Sheet2Only Else
    Sheets("Sheet2").Select
    If IsEmpty(Range("A1")) Then GoTo Sheet1Only Else
    Sheets("Sheet1").Select
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets("Combined").Select
    Range("A2").Select
    ActiveSheet.Paste


    Sheets("Sheet2").Select

    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Combined").Select
    Range("B2").Select
    'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, -1).Select
    ActiveSheet.Paste

    GoTo Continue

Sheet1Only:

    Sheets("Sheet1").Select
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets("Combined").Select
    Range("A2").Select
    ActiveSheet.Paste
    GoTo Continue

Sheet2Only:

    Sheets("Sheet2").Select
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Combined").Select
    Range("A2").Select
    ActiveSheet.Paste
    GoTo Continue
    GoTo Continue

Continue:
   
Sheets("Combined").Select
  Columns("B:B").Select
    Selection.Cut
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight

Author

Commented:
There's probably a way to do this without using GoTo but I couldn't think of it. Was hoping to get some direction from an expert ....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial