Macro to check if existing data

Topic

New Reply

I would like to amend my Macro to check if there is existing data on any of the sheets. If so, then to create a new sheet and to copy the data in the sheet created

I have attached sample data (macro in sample data)

Reply | Quote

Replies

Howard,

You can check to see if there is any data on the sheet with

Code:

Dim rng As Range
Set rng = ActiveSheet.UsedRange
If rng.Cells.Count = 1 And rng = “” Then [COLOR=”#008000″]’SHEET IS BLANK[/COLOR]

There are many ways to add a new worksheet. Here are just a few:

Code:

Worksheets.Add Before:=Worksheets(“Sheet2″) [COLOR=”#008000”]’BEFORE A NAMED WORKSHEET[/COLOR]
Worksheets.Add After:=Worksheets(Worksheets.Count) [COLOR=”#008000″]’AFTER THE LAST WORKSHEET[/COLOR]
Worksheets.Add Before:=Worksheets(1) [COLOR=”#008000″]’BEFORE THE FIRST WORKSHEET[/COLOR]

Copy data from one sheet to another cell by cell:

Code:

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets(“Sheet1”)
Set ws2 = Worksheets(“Sheet2”)
LastRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
For I = 2 To LastRow
    ws2.Cells(I, 1) = ws1.Cells(I, 1)
Next I

OR

Copy data from one sheet to another using copy/paste:

Code:

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets(“Sheet1”)
Set ws2 = Worksheets(“Sheet2”)
ws1.Range(“A1:C6”).Copy
With ws2
    .Activate
    .Range(“A1”).Select
    .Paste
End With
Application.CutCopyMode = False

HTH,
Maud

Reply | Quote

Thanks for your input Maud

I would like to amend my code below, so that if I copy more data from a source file, It will copy the data after the last row in Col A

Code:

 Sub copyDataFromSource()
Dim sourceBook As Workbook
Dim destinationBook As Workbook
Dim sourceSheet As Worksheet
Dim destinationSheet As Worksheet
Dim fileSource, sourceRow%, sourceRowCount&, destRow%
With Application
    .ScreenUpdating = False
End With
fileSource = Application.GetOpenFilename
If fileSource = False Or IsEmpty(fileSource) Then Exit Sub
Set destinationBook = ThisWorkbook
Set destinationSheet = destinationBook.Sheets("sheet1")
Set sourceBook = Workbooks.Open(fileSource)
Set sourceSheet = sourceBook.Sheets(1)
sourceRow = sourceSheet.Cells(sourceSheet.Rows.Count, 1).End(xlUp).Row
sourceRowCount = sourceRow - 1
destRow = destinationSheet.Cells(destinationSheet.Rows.Count, 1).End(xlUp).Row
destinationSheet.Rows(destRow + 1).Resize(sourceRowCount).Insert
destRow = destRow

With destinationSheet
    .Range("a" & destRow & ":ae" & destRow + sourceRowCount - 1).Value = sourceSheet.Range("a1:ae" & sourceRow).Value
   
End With
sourceBook.Close False
With Application
    .ScreenUpdating = True
End With
Set sourceBook = Nothing
Set destinationBook = Nothing
Set sourceSheet = Nothing
Set destinationSheet = Nothing
End Sub 

Reply | Quote