PDA

View Full Version : Loop to match headers on seperatesheets, copy data below, return and paste in header



Mww144
06-26-2016, 05:53 AM
I have a master sheet with specific headers which information I need. I have about 7 sheets with random headers with subsequent data. I only need certain headers with their data in the master sheet. When new data is entered in to the sheets the vba will match the headers, copy the data and paste it in the master sheet.

It would clear all the data each time in the master sheet and just copy and paste all the data in a loop.

I tried to used a counter and index the headers with numbers, and identify the headers of interest on the sheets with same numbers.

I want to index and match these in the loop. Match i=1 in master to a indexed range in other sheets, return the cell location, copy down below that cell location, and return to the master and paste in that column in the last free cell. If there is no i=1 in the indexed range, skip to the next i step1

Any advice would be greatly appreciated. I am not in front of my computer so do not have my code I have been trying but will post it shortly. Thanks in advance!

offthelip
06-26-2016, 03:36 PM
one way which you could do this quite easily is to create named ranges on the data sheets which have the same name as the headers on your master sheet. Then you can loop through the data sheets you can pick up the data for each master column by using the header name as the range name.
Note: Named ranges do have some restrictions on the syntax, but there are way round this.

Mww144
06-26-2016, 04:51 PM
one way which you could do this quite easily is to create named ranges on the data sheets which have the same name as the headers on your master sheet. Then you can loop through the data sheets you can pick up the data for each master column by using the header name as the range name.
Note: Named ranges do have some restrictions on the syntax, but there are way round this.


thank you!! I have been moving all day and have not had time
to try it yet, but that will be the first thing I do. Never thought about named ranges. Thanks again

snb
06-27-2016, 06:57 AM
Sub M_snb()
Set d_00 = CreateObject("scripting.dictionary")

For Each sh In Sheets
If sh.Name <> "master" Then
With sh.Cells(1).CurrentRegion
If .Rows.Count > 1 Then
For Each it In sh.Cells(1).CurrentRegion.Columns
d_00(it.Cells(1).Value) = d_00(it.Cells(1).Value) & "|" & Join(Application.Transpose(it.Offset(1)), "|")
Next
End If
End With
End If
Next

for each it in sheets("master").cells(1).currentregion.rows(1)
st=split(mid(d_00(it.value),2),"|")
it.offset(1).resize(ubound(st)+1)=application.transpose(st)
next
End sub

Mww144
06-27-2016, 02:22 PM
Thanks for all the help!

This is the code I ended up getting to work




Sub copypaste()


Dim j As Integer
Dim i As Integer
Dim foundcell As Range
Dim strfind As String
Dim frow, fcol As Integer
Dim sh1 As Worksheet
Set sh1 = Sheets("MasterDetail")




For j = 7 To Worksheets.Count


For i = 1 To 44
strfind = sh1.Cells(1, i)
Set foundcell = ActiveWorkbook.Sheets(j).Range("A1:MM1").Find(strfind, LookIn:=xlValues)
If Not foundcell Is Nothing Then
frow = foundcell.row
fcol = foundcell.Column
ActiveWorkbook.Sheets(j).Range(Cells(frow + 1, fcol).Address & ":" & Cells(frow + 10000, fcol).Address).Copy
sh1.Cells(Rows.Count, i).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Else


End If
Next i
Next j


End Sub

Mww144
06-27-2016, 02:56 PM
Nevermind, this does not work...

in columns where there is no data, the next sheets data is pasted in its place...

can anyone help? plzzz

Mww144
06-27-2016, 06:18 PM
Not all sheets contain the same amount of headers.

Some sheets have more than others.

The problem is on the master sheet, headers which were empty or nonexistent on the worksheets are being filled with data from the present headers on the next sheet

I need to know how to reference a column which I know will always be data, specifically the last row.

Any advice would be incredibly appreciated!

SamT
06-27-2016, 06:48 PM
Assumes all sheets headers are in Row 1
Compiles. Not tested


Option Explicit

Sub VBAX_SamT()
'Adjust 2 instances "MasterSheetName" to suit
'For each header in Master Sheet, searches all other sheets for matching header,
'Then copys all data below matching header to bottom of Used Master header column.

Dim rngMasterHeaders As Range
Dim Cel As Range
Dim Sht As Worksheet
Dim CopyHeader As Range
Dim Dest As Range

Set rngMasterHeaders = Sheets("MasterSheetName"). _
Range(Cells(1, "A"), Cells(1, Columns.Count).End(xlToLeft))

For Each Cel In rngMasterHeaders
If Cel.Value <> "" Then
For Each Sht In Worksheets
If Sht.Name <> "MasterSheetName" Then
Set CopyHeader = Sht.Range("1:1").Find(Cel.Value)
If Not CopyHeader Is Nothing Then
Set Dest = Cel.Parent.Range(Rows.Count, Cel.Column).End(xlUp).Offset(1)
Range(CopyHeader.Offset(1), Cells(Rows.Count, CopyHeader.Column).End(xlUp)).Copy Dest
Set CopyHeader = Nothing
End If
End If
Next Sht
End If
Next Cel

End Sub

snb
06-28-2016, 01:20 AM
Any advice would be incredibly appreciated!

That's doesn't show by your ignoring my suggestion.
Nor by helping helpers in posting a sample workbook.

Mww144
06-28-2016, 05:46 AM
That's doesn't show by your ignoring my suggestion.
Nor by helping helpers in posting a sample workbook.

snb,

I do apologize. The code you posted was different than mine so I did not try and adapt it, although I am sure it works much better than the one I posted.

Here is a sample workbook. When sheets do not have headers that are on the Master, I want the next sheet to be pasted below the last empty cell relative to a column that contains data all the way down. I know one column will always have data, header 2.

I will be copying and pasting data into the sheets continuously and want a VBA that will copy and paste it to the master sheet

Again, my apologies and many thanks

16489