Automatically transfer data from multiple sheets to one sheet. [Closed]

Report
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
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:



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 replies

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
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.
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
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
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.
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
I also added to ge.tt @ http://ge.tt/4HMvKLm2
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
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.