Automatically transfer data from multiple sheets to one sheet.

Lisa Allen - Updated on Aug 27, 2017 at 10:35 PM
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - Aug 30, 2017 at 06:06 AM

I have a price book in Excel with multiple sheets. Sheets are formatted differently except for first three columns (QTY,PartNo,Price). I need one sheet to populate based on whether or not the qty is greater than 1. I have found a code which will look at one sheet and perform task, but not multiple sheets. Can you help?
Here is the code:

Option Explicit
Dim MyWorkbook As Workbook
Dim MyWorksheet As Worksheet
Dim MyOutputWorksheet As Worksheet

Sub PullData()
Set MyWorkbook = Workbooks(ActiveWorkbook.Name)
Set MyWorksheet = MyWorkbook.Sheets("Main")
Set MyOutputWorksheet = MyWorkbook.Sheets("Contract")

Dim myValue As Long
Dim RowPointer As Long

For RowPointer = 2 To MyWorksheet.Cells(Rows.Count, "B").End(xlUp).Row
If MyWorksheet.Range("A" & RowPointer).Value > 0 And MyWorksheet.Range("A" & RowPointer).Value <> "" Then
If MyOutputWorksheet.Cells(Rows.Count, "B").End(xlUp).Row > 15 Then
Exit Sub
End If
MyWorksheet.Range(("A" & RowPointer) & ":C" & RowPointer).Copy Destination:=MyOutputWorksheet.Range("A" & MyOutputWorksheet.Cells(Rows.Count, "B").End(xlUp).Row + 1)
End If
Next RowPointer

End Sub

6 responses

vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Aug 27, 2017 at 11:03 PM
Hello Lisa,

Following is the link to a workbook for a Poster who had a similar query a few months ago. See if this is at least close to what you were wanting to do. I've changed the set up a little in an attempt to mirror your work book.

Click on the "RUN" button to see it work.

Th code associated with the sample is as follows:-

Sub Transfer()

    Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In Worksheets
    If ws.Name <> "Main" Then
With ws.[A1].CurrentRegion
    .AutoFilter 1, ">1"
    Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
    End With
    End If
Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Let us know what you think.

Good Morning Vcoolio,

Thanks for your help. I am very new to this, but when I try to run this in my workbook I am getting an AutoFilter method of Range class failed?
Not sure how to fix?

Sample of Data:

Quantity Model PRICE Tons Cabinet COAX HW
1 GS024SXXCDXX1XX1 2988 2 Split Copper Yes
GS024SXXNDXX1XX1 3158 2 Split CuproNickel Yes
GS036SXXCDXX1XX1 3366 3 Split Copper Yes
1 GS036SXXNDXX1XX1 3592 3 Split CuproNickel Yes
GS048SXXCDXX1XX1 3685 4 Split Copper Yes
GS048SXXNDXX1XX1 3988 4 Split CuproNickel Yes
GS060SXXCDXX1XX1 3952 5 Split Copper Yes
GS060SXXNDXX1XX1 4252 5 Split CuproNickel Yes
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Aug 29, 2017 at 03:49 AM
Hello Lisa,

It might be best if you upload a sample of your actual workbook which would make it easier for me to identify where the problem may be.
Upload a sample to a free file sharing site such as or Drop Box then post the link to your file back here. Please be careful with any sensitive data and include any code that you may already have in your work book.

Thanks Lisa.

Thank you for all of your help. Here is a link to a working draft, any help or suggestions would be greatly appreciated. ":0)

Didn't find the answer you are looking for?

Ask a question
I also added to @
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Aug 30, 2017 at 06:06 AM
Hi Lisa,

Hmmm....... The sheets are somewhat disorganised! Its a bit of a jumble, a bit confusing.

For this to work quickly, cleanly and efficiently, all the sheets (the source sheets) need to be formatted the same.

I assume that the "Contract" sheet is the main sheet to which all the required data is transferred from the source sheets. Is this correct?

Are there only three columns in the "Contract" sheet that you wish to populate (Columns A, B & C) with data from the source sheets?

The code in my post #1 is not working for you because of the irregular set up of each sheet. The code above is designed to filter each sheet on row1, column1(A) and then copy/paste any relevant data from row2. Hence the error you are receiving because some sheets start at row1, others at row2 others at row3 etc..

For such a large workbook as yours, there needs to be uniformity across all sheets.

Could you please re-organise a few sheets (just the main sheet and three or four source sheets, all formatted the same) and upload a new sample as such.
This will help immensely towards resolving this for you.

Thanks Lisa.