You are here:

Excel/Insert sheet and copy data

Advertisement


Question
Hi Gulshan,

Thank you for continuing to followup up with me.

This is a continuation of the last previous message I sent you (I was unable to respond directly to it)

I have tried to write some code for you to correct as requested. I have broken it up into sub routines. I am having trouble inserting a new sheet call "Temp" (if I do it manually this part works). After filtering the data on the original sheet and coping it to the Temp sheet, I would like to delete the data on the original sheet from "A8" down and then copy the new filtered data in the Temp sheet from "A9" down and then paste into the original sheet from "A8".

I am having trouble selecting the sheets. Other then that my code might not be the prettiest but it seems to work when I test it in stages - I have have to link it up.



Thank you so much for your help
Natalie



Sub Main_Start()

Call Filter_Unwanted_Data
Call CreateSheet
Call DeleteFilteredData
Call copyGoodData

End Sub
=========

Sub Filter_Unwanted_Data()
‘ Filters Unwanted Data on Original sheet & paste into new Temp sheet
Application.ScreenUpdating = False
rcnt = WorksheetFunction.CountA(Range("A:A"))
actcll = 8
Do While actcll <= rcnt
Range("A" & actcll).Select
With Selection.Interior
      .Color = 65535
End With
actcll = actcll + 100
Loop
Range("A:A").AutoFilter Field:=1, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor
Range("A:A").SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Temp").Range("A7")
End Sub
=============

Sub CreateSheet()
   With ActiveWorkbook
       .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Temp"
   End With
End Sub
==============

Sub DeleteFilteredData()
' Delete filtered data on original sheet
'Dim Lastrow As Integer
Selection.AutoFilter
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("F8", Range("F8").End(xlToRight))
Range("A8:rng" & Lastrow).Delete
End Sub
==============

Sub copyGoodData()
'Copy the good filtered data on "Temp" sheet & paste into the original sheet starting at “A8”
Dim Lastrow As Integer
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("F9", Range("F9").End(xlToRight))
Range("A9:rng" & Lastrow).Copy

‘SELECT ORIGINAL SHEET – not sure how

Range("A8").Select
ActiveSheet.Paste

End Sub



**************************************
Dear Natalie,

Can you please give the complete macro after you've made all the changes.

I'll make the corrections and send it back to you.

Thanks,
Gulshan.

QUESTION: Hi Gulshan,

I have tried your suggestion and it still fails at this line:
Range("A:A").AutoFilter Field:=1, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor

I have placed the "Call CreateSheet" straight after the  Application.ScreenUpdating = False.

It doesn't matter what I call the sheet it still fails, I have tried Sheet2, Temp, Mysheet. I also include the new sheet name in this line: Range("A:A").SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Mysheet").Range("A1

But, if I insert a new sheet (manually), say with a name of Mysheet, I also change this line (with Mysheet)
Range("A:A").SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Mysheet").Range("A1").

It will work without fail.

If I run the macro while testing I always delete the added new sheet.

Any help is greatly appreciated.

Regards
Natalie

ANSWER: Sincere apologies for the delayed response Natalie.
I'd been travelling for the last few days. I will work on this macro over next 1-2 days and come back to you with a solution.

Thanks a lot,
Gulshan.

---------- FOLLOW-UP ----------

QUESTION: Thanks for the update Gulshan, I will wait for your reply.

Regards
Natalie

Answer
Dear Natalie,

I've made the following changes to your code:
1. Moved the create sheet before to the beginning.
2. Added a condition which will check if "Temp" is existing, if true, it will be deleted and a new one will be created.

Please run this and see if serves your purpose.

Thanks,
Gulshan.

Sub Main_Start()

Call CreateSheet
Call Filter_Unwanted_Data
Call DeleteFilteredData
Call copyGoodData

End Sub


Sub Filter_Unwanted_Data()
' Filters Unwanted Data on Original sheet & paste into new Temp sheet
Worksheets("Sheet1").Select
Application.ScreenUpdating = False
rcnt = WorksheetFunction.CountA(Range("A:A"))
actcll = 8
Do While actcll <= rcnt
Range("A" & actcll).Select
With Selection.Interior
     .Color = 65535
End With
actcll = actcll + 100
Loop
Range("A:A").AutoFilter Field:=1, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor
Range("A:A").SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Temp").Range("A7")
End Sub


Sub CreateSheet()
Application.DisplayAlerts = False

  Dim ws As Worksheet
   SheetExists = False
   For Each ws In ThisWorkbook.Worksheets
       If ws.Name = "Temp" Then Worksheets("Temp").Delete
   Next ws
  With ActiveWorkbook
      .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Temp"
  End With
Application.DisplayAlerts = True


End Sub


Sub DeleteFilteredData()
' Delete filtered data on original sheet
'Dim Lastrow As Integer
Selection.AutoFilter
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Range("F8", Range("F8").End(xlToRight))
Range("A8:rng" & Lastrow).Delete
End Sub


Sub copyGoodData()
'Copy the good filtered data on "Temp" sheet & paste into the original sheet starting at “A8”
Dim Lastrow As Integer
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Range("F9", Range("F9").End(xlToRight))
Range("A9:rng" & Lastrow).Copy

'SELECT ORIGINAL SHEET – not sure how

Range("A8").Select
ActiveSheet.Paste

End Sub  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.