Move Data from one work sheet to another VBA

[nr6281]

Hi All,

Previously I had a little help online including in this forum on finding how to write a macro to move data from one workbook to another,

This time its the same idea but from one worksheet to another 2 worksheet based on a True or false.

From Raw Data file: I want to move all with True in Column AN to worksheet Reported1 and True from column AO to worksheet Disabled!

 

[DanteAmor]

Try this

Sub Move_Data()
  Dim sh As Worksheet
  Set sh = ActiveSheet
  If sh.AutoFilterMode Then sh.AutoFilterMode = False
  sh.Range("A1:AO1").AutoFilter Field:=40, Criteria1:=True
  sh.AutoFilter.Range.EntireRow.Copy Sheets("Reported1").Range("A1")
  sh.AutoFilter.Range.Offset(1).EntireRow.Delete
  sh.ShowAllData
  sh.Range("A1:AO1").AutoFilter Field:=41, Criteria1:=True
  sh.AutoFilter.Range.EntireRow.Copy Sheets("Disabled").Range("A1")
  sh.AutoFilter.Range.Offset(1).EntireRow.Delete
  sh.ShowAllData
End Sub

 

[nr6281]

Hi Dante,

Thank you this works perfect however, I think due to the formula I have in Column AN and AO it takes a lot of time so can I have them paste as Values in the Reported1 sheet and Disabled?

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...-data-from-one-work-sheet-to-another-vba.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[DanteAmor]

Hi Dante,

Thank you this works perfect however, I think due to the formula I have in Column AN and AO it takes a lot of time so can I have them paste as Values in the Reported1 sheet and Disabled?

Try this

Sub Move_Data()
  Dim sh As Worksheet
  Set sh = ActiveSheet
  If sh.AutoFilterMode Then sh.AutoFilterMode = False
  sh.Range("A1:AO1").AutoFilter Field:=40, Criteria1:=True
  sh.AutoFilter.Range.EntireRow.Copy
  Sheets("Reported1").Range("A1").PasteSpecial xlValues
  sh.AutoFilter.Range.Offset(1).EntireRow.Delete
  sh.ShowAllData
  sh.Range("A1:AO1").AutoFilter Field:=41, Criteria1:=True
  sh.AutoFilter.Range.EntireRow.Copy
  Sheets("Disabled").Range("A1").PasteSpecial xlValues
  sh.AutoFilter.Range.Offset(1).EntireRow.Delete
  sh.ShowAllData
End Sub

Please, don't forget to put the links of the other threads here.

 

[nr6281]

An also the formulas in the Raw file messes up

 

[DanteAmor]

Please, don't forget to put the links of the other threads here.

 

[DanteAmor]

An also the formulas in the Raw file messes up

You wanted to paste values, but now I don't understand what you need.

 

[nr6281]

Hi,

The issue is once I run the macro it takes time so if you could help tweak this as paste values in the reported sheet and disabled sheet

 

[nr6281]

https://www.excelforum.com/excel-pr...ne-work-sheet-to-another-vba.html#post5166301