Automatically transfer data from multiple sheets to one sheet.Closed

Question

Hello, 

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:


System Configuration: Windows / Chrome 49.0.2623.112
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

vcoolio · Answer

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.

http://ge.tt/80fLxJm2

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"
    .Offset(1).Copy
    Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
    .AutoFilter
    End With
    End If
Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True
  
End Sub

Let us know what you think.

Cheerio,
vcoolio.

Lisa Allen · Answer

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 · Answer

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 ge.tt 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.

Cheerio,
vcoolio.

Lisa Allen · Answer

Thank you for all of your help.  Here is a link to a working draft, any help or suggestions would be greatly appreciated.  ":0)
https://drive.google.com/open?id=1BV-Ko-Ca2LWCAduAw73gSFAO4SMOt-eyXfJs3RwQAsg

Lisa Allen · Answer

I also added to ge.tt @ http://ge.tt/4HMvKLm2

vcoolio · Answer

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.

Cheerio,
vcoolio.

Automatically transfer data from multiple sheets to one sheet.

6 responses

Subscribe To Our Newsletter!