Copy & Pasting Data Into New Sheet

ai1094

Board Regular
Joined
Aug 23, 2018
Messages
92
Hi,

I have a simple ask. I have an excel workbook that is broken up into 3 different sheets. I would like to have a VBA code that lets me take all those 3 sheets and consolidate it into one sheet. All column fields are the same, they are just broken into 3 different regions. However, sometimes there can be more/less entries so I need the VBA code to find the last row containing data.

How would I do this? Thanks.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Is there any particular column that will always have data on the last row of each sheet?
 
Upvote 0
Most of the columns contain data, with an exception of few columns. One that will always contain data is column A. If it helps, the columns go from A all the way to P​.
 
Upvote 0
Ok, how about
Code:
Sub ai1094()
   Dim i As Long
   
   Sheets(1).Copy Sheets(1)
   ActiveSheet.Name = "All"
   For i = 3 To Worksheets.Count
      With Sheets(i)
         .Rows("2:" & .Range("A" & Rows.Count).End(xlUp).Row).Copy Range("A" & Rows.Count).End(xlUp).Offset(1)
      End With
   Next i
End Sub
 
Upvote 0
Just so I am clear on this code, let me give you a little more information. The workbook has a total of 4 sheets:

1. Master File
2. AMER
3. EMEA
4. APAC

I want to take the information from 2,3 and 4 and copy it into Master File.


Ok, how about
Code:
Sub ai1094()
   Dim i As Long
   
   Sheets(1).Copy Sheets(1)
   ActiveSheet.Name = "All"
   For i = 3 To Worksheets.Count
      With Sheets(i)
         .Rows("2:" & .Range("A" & Rows.Count).End(xlUp).Row).Copy Range("A" & Rows.Count).End(xlUp).Offset(1)
      End With
   Next i
End Sub
 
Upvote 0
In that case use
Code:
Sub ai1094()
   Dim i As Long
   
   For i = 2 To Worksheets.Count
      With Sheets(i)
         .Rows("2:" & .Range("A" & Rows.Count).End(xlUp).Row).Copy Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1)
      End With
   Next i
End Sub
 
Upvote 0
Fluff, sheets 2,3 and 4 are files that are connected to a sharepoint site and the excel file can be refreshed whenever a a file is modified. I am just having trouble on where to place your code
Here is my current code:

Private Sub Workbook_Open()





'Refresh AMERICAS


Workbooks.Open Filename:="some/path/name"
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
ActiveWorkbook.Close SaveChanges:=True



'Refresh EMEA

Workbooks.Open Filename:="some/path/name"
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
ActiveWorkbook.Close SaveChanges:=True


'Refresh APAC

Workbooks.Open Filename:="some/path/name"
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
ActiveWorkbook.Close SaveChanges:=True






End Sub
 
Upvote 0
Just put it in a standard module & run it whenever needed.
 
Upvote 0
Looks like the code works, however Row 1 is empty and I need the column headers there.
 
Upvote 0
Will the master file always be blank when you run the code?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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