Transfer table filtered data to new worksheet in respective column, 2nd tab data transferred under last row in respective columns.

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
116
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

Hi

The link above is an extract of a larger macro which works really well, however I am stuck on preparing my final report.

there are a number of tabs which are filtered for change, each tab comparing to the last. In my extract I have two, 09.30 & 12.30, so 12.30 compares to 9.30 to get the change results, works well.

What I am stuck with is transferring these onto another sheet called changes table.

The first transfer works well, although somewhat long winded, I tried many types of VB code, and tried shorting the lines, nothing worked except, individual activate copy and paste.

the second 12.30 tab doesnt cause any errors but doesnt do anything either, it should transfer the visible filtered columns under the last row used of the Changes tables in their respective columns. Im not sure whats happening here, I would be greatful if somebody could take a look. (the tabs use tables)

I tried using this


Set TME = ThisWorkbook.Worksheets("08.30")
'' TME.Range("A:A").Copy Destination:=NDC.Range("A1")
'' TME.Range("E:E").Copy Destination:=NDC.Range("B1")
'' TME.Range("F:F").Copy Destination:=NDC.Range("D1")
'' TME.Range("G:G").Copy Destination:=NDC.Range("C1")
''
instead of all this, but I kept getting errors of paste isnt the same size. Its a bit annoying because there are 7 tabs in total, which will make the program very big.

Sheets("9.30").Activate
Range("A2").Activate
Range(Selection, Selection.End(xlDown)).Copy
Sheets("Changes Table").Activate
Range("A3").Activate
ActiveSheet.Paste

Sheets("9.30").Activate
Range("E2").Activate
Range(Selection, Selection.End(xlDown)).Copy
Sheets("Changes Table").Activate
Range("c3").Activate
ActiveSheet.Paste


Sheets("9.30").Activate
Range("Y2").Activate
Range(Selection, Selection.End(xlDown)).Copy
Sheets("Changes Table").Activate
Range("B3").Activate
ActiveSheet.Paste

Sheets("9.30").Activate
Range("G2").Activate
Range(Selection, Selection.End(xlDown)).Copy
Sheets("Changes Table").Activate
Range("D3").Activate
ActiveSheet.Paste

Sheets("9.30").Activate
Range("F2").Activate
Range(Selection, Selection.End(xlDown)).Copy
Sheets("Changes Table").Activate
Range("E3").Activate
ActiveSheet.Paste


Sheets("9.30").Activate
Range("z2").Activate
Range(Selection, Selection.End(xlDown)).Copy
Sheets("Changes Table").Activate
Range("F3").Activate
ActiveSheet.Paste




Many thanks

Dave.
 
You need to move this line
VBA Code:
Seets("Changes Table").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(Rws) + 1, 6) = Ary
inside the If statement as I showed in post#12
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi fluff,

Worked perfect, thank you, Really greatful for your help, good collection of code to to refer to in the future.

thanks

dave.
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
(removed as too late, only for good readers see post #13)
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top