Copy data from one sheet to another based on value in sheet to select right sheet to copy

[bubsk]

Hello everyone!

So in my excel I have

Sheet A where in Column J i have IDs

I also have so many sheets as IDs and every Sheet's name is ID number
So I have sheet named 1.1
sheet named 1.2, 1.3, 1.4 etc

Also I have Sheet named 'Import"

I'd like to have a loop which first will get first ID from Sheet A = 1.1
Then will open sheet named 1.1 and copy data from column C
And paste it into Import Sheet to column "Copied Data" on matched ID from column Example and move entire row Data 1 bellow idk how many rows because every time in Sheet named 1.1, 1.2 there are different values.

Then fuction will get next ID 1.2 from sheet A, open sheet 1.2...

And make it till last ID in Sheet A.

I have over 50 excel file so I don't know numbers of IDs in every.


Import

Sample	Example	Of	CopiedData
Data	ID	Data
Data1	ID	Data

Sheet A
J

ID

1.1

1.2

1.3

1.4

1.5

1.6

 

[JLGWhiz]

Try this

Sub t()
Dim c As Range, sh1 As Worksheet, sh2 As Worksheet, col As Long
Set sh1 = Sheets("A")
Set sh2 = Sheets("Import")
col = sh2.Rows(1).Find("CopiedData", , xlValues).Column
    With sh1
        For Each c In .Range("J2", .Cells(Rows.Count, 10).End(xlUp))
            Intersect(Sheets(CStr(c.Value)).UsedRange, Sheets(CStr(c.Value)).Columns(3)).Copy
            sh2.Cells(Rows.Count, col).End(xlUp)(2).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
        Next
    End With
End Sub

 

[bubsk]

Try this

Sub t()
Dim c As Range, sh1 As Worksheet, sh2 As Worksheet, col As Long
Set sh1 = Sheets("A")
Set sh2 = Sheets("Import")
col = sh2.Rows(1).Find("CopiedData", , xlValues).Column
    With sh1
        For Each c In .Range("J2", .Cells(Rows.Count, 10).End(xlUp))
            Intersect(Sheets(CStr(c.Value)).UsedRange, Sheets(CStr(c.Value)).Columns(3)).Copy
            sh2.Cells(Rows.Count, col).End(xlUp)(2).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
        Next
    End With
End Sub

What if I'd like to copy column A9:A, B9: B, C9:C End(xlUp) from sheet named 1.2, 1.3, 1.4 etc to N1:N, O1:O, P1:P to sheet "Import" instead of copy columns 3 into row named "CopiedData". That row named copieddata no longer exist



IMPORT SHEET

	N	O	P
1	DATA FROM A9	DATA FROM B9	DATA FROM C9
2	DATA FROM A10	DATA FROM B10	DATA FROM C19

1.2 Sheet

	A	B	C
9	TEST	TEST	TEST
10	TEST	TEST	TEST

 

[JLGWhiz]

Then you would need to start a new thread because you have changed the scope of your original post. But if you still want to use Sheet A to identify the source worksheets, then this might work.

Sub t3()
Dim c As Range, sh1 As Worksheet, sh2 As Worksheet, col As Long
Set sh1 = Sheets("A")
Set sh2 = Sheets("Import")
    With sh1
        For Each c In .Range("J2", .Cells(Rows.Count, 10).End(xlUp))
            Intersect(Sheets(CStr(c.Value)).UsedRange, Sheets(CStr(c.Value)).Range("A9:C9").End(xlDown)).Copy
            sh2.Range("N1").PasteSpecial xlPasteValues
            Application.CutCopyMode = False
        Next
    End With
End Sub

 

[JLGWhiz]

But if Sheet A would still have the source sheets listed, then maybe this.

Sub t3()
Dim c As Range, sh1 As Worksheet, sh2 As Worksheet, col As Long
Set sh1 = Sheets("A")
Set sh2 = Sheets("Import")
    With sh1
        For Each c In .Range("J2", .Cells(Rows.Count, 10).End(xlUp))
            Sheets(CStr(c.Value)).Range("A9:C9", Range("A9:C9").End(xlDown)).Copy
            sh2.Range("N1").PasteSpecial xlPasteValues
            Application.CutCopyMode = False
        Next
    End With
End Sub

 

[bubsk]

Result in first code:

In N column "Import" Sheet I have last value from A column in 1.1 Sheet

Second: Aplication defined or object error

I created new topic as u recommended

 

[JLGWhiz]

I took a little more time to fix this. Try it now.

Sub t3()
Dim c As Range, sh1 As Worksheet, sh2 As Worksheet, col As Long
Set sh1 = Sheets("A")
Set sh2 = Sheets("Import")
    With sh1
        For Each c In .Range("J2", .Cells(Rows.Count, 10).End(xlUp))
            With Sheets(CStr(c.Value))
                .Range(.Range("A9:C9"), .Range("A9:C9").End(xlDown)).Copy
            End With
            If sh2.Range("N1") = "" Then
                sh2.Range("N1").PasteSpecial xlPasteValues
            Else
                sh2.Cells(Rows.Count, 14).End(xlUp)(2).PasteSpecial xlPasteValues
            End If
            Application.CutCopyMode = False
        Next
    End With
End Sub

 

[bubsk]

It almost works! gr8
but data from the left from are not related with copied data from for Sheets 1.2, 1.2 etc

Copy data from one sheet to another based on value in sheet and copy multiple columns from 9 row

Helloo in my excel I have Sheet A where in Column J i have IDs I also have so many sheets as IDs and every Sheet's name is ID number So I have sheet named 1.1 sheet named 1.2, 1.3, 1.4 etc Also I have Sheet named 'Import" I'd like to have a loop which first will get first ID from Sheet A =...

www.mrexcel.com

 

[bubsk]

I took a little more time to fix this. Try it now.

Sub t3()
Dim c As Range, sh1 As Worksheet, sh2 As Worksheet, col As Long
Set sh1 = Sheets("A")
Set sh2 = Sheets("Import")
    With sh1
        For Each c In .Range("J2", .Cells(Rows.Count, 10).End(xlUp))
            With Sheets(CStr(c.Value))
                .Range(.Range("A9:C9"), .Range("A9:C9").End(xlDown)).Copy
            End With
            If sh2.Range("N1") = "" Then
                sh2.Range("N1").PasteSpecial xlPasteValues
            Else
                sh2.Cells(Rows.Count, 14).End(xlUp)(2).PasteSpecial xlPasteValues
            End If
            Application.CutCopyMode = False
        Next
    End With
End Sub

Or never mind, just tell me plz how to edit

.Range(.Range("A9:C9"), .Range("A9:C9").End(xlDown)).Copy

that line to copy only A9 and C9 xlDown, without B column
and also data from cell A4,

 

[JLGWhiz]

What if I'd like to copy column A9:A, B9: B, C9:C End(xlUp) from sheet named 1.2, 1.3, 1.4 etc to N1:N, O1:O, P1:P to sheet "Import"

The code does exactly what this asked for.

The following is not only a different parameter, it is incomplete because it does not have a destination for the copied ranges.

just tell me plz how to edit
.Range(.Range("A9:C9"), .Range("A9:C9").End(xlDown)).Copy
that line to copy only A9 and C9 xlDown, without B column
and also data from cell A4,

So I think you should just go with your new thread.
regards, JLG