Hi. I have a complicated one here :
I have one workbook (original) with 6 different sheets (Months 1-6).
I have 12 columns on each sheet with a header in the first row, so range B1:M1 on each tab has the headers.
I have a second workbook (destination) that has 12 sheets (Jan2011-Dec2011). Headers are in row 4.
I need a VBA code that will search the destination workbook and find a match for EACH header from the original worksheet. Once a match is found, it needs to copy the cell values from Rows 3:33 of that column from the original workbook and paste it into rows 6:36 of the destination workbook.
EXAMPLE: If B1 on sheet Month 1 says "Bread Pudding", then the code will find "Bread Pudding" in 1 of the sheets of the destination workbook (pretend sheet Sept11 column AZ4) and copy B3:B33 from original file's sheet Month 1, and paste those values into Sept11 AZ6:AZ36. Then the code will move to C1 on sheet Month 1 and follow the same process for all columns in all 6 sheets.
I don't know even know where to begin, but probably has to do with a FIND function. I was offered this code on another forum, but it is severely constrained by only dealing with 2 worksheets in the same workbook and only 1 column that needs to be copied and pasted - I don't know how to expand this to multiple worksheets with multiple ranges. any help would be great!
Option Explicit
Sub FindStr()
Dim rFndCell As Range
Dim strData As String
Dim stFnd As String
Dim fCol As Integer
Dim sh As Worksheet
Dim ws As Worksheet
Set ws = Sheets("Original")
Set sh = Sheets("PasteSheet")
stFnd = ws.Range("B1").Value
With sh
Set rFndCell = .Range("A:IV").Find(stFnd, LookIn:=xlValues)
If Not rFndCell Is Nothing Then
fCol = rFndCell.Column
ws.Range("B3:B33").Copy sh.Cells(6, fCol)
sh.Cells(6, fCol).PasteSpecial xlPasteValues
Else 'Can't find the item
MsgBox "No Find"
End If
End With
End Sub
THANKS!
I have one workbook (original) with 6 different sheets (Months 1-6).
I have 12 columns on each sheet with a header in the first row, so range B1:M1 on each tab has the headers.
I have a second workbook (destination) that has 12 sheets (Jan2011-Dec2011). Headers are in row 4.
I need a VBA code that will search the destination workbook and find a match for EACH header from the original worksheet. Once a match is found, it needs to copy the cell values from Rows 3:33 of that column from the original workbook and paste it into rows 6:36 of the destination workbook.
EXAMPLE: If B1 on sheet Month 1 says "Bread Pudding", then the code will find "Bread Pudding" in 1 of the sheets of the destination workbook (pretend sheet Sept11 column AZ4) and copy B3:B33 from original file's sheet Month 1, and paste those values into Sept11 AZ6:AZ36. Then the code will move to C1 on sheet Month 1 and follow the same process for all columns in all 6 sheets.
I don't know even know where to begin, but probably has to do with a FIND function. I was offered this code on another forum, but it is severely constrained by only dealing with 2 worksheets in the same workbook and only 1 column that needs to be copied and pasted - I don't know how to expand this to multiple worksheets with multiple ranges. any help would be great!
Option Explicit
Sub FindStr()
Dim rFndCell As Range
Dim strData As String
Dim stFnd As String
Dim fCol As Integer
Dim sh As Worksheet
Dim ws As Worksheet
Set ws = Sheets("Original")
Set sh = Sheets("PasteSheet")
stFnd = ws.Range("B1").Value
With sh
Set rFndCell = .Range("A:IV").Find(stFnd, LookIn:=xlValues)
If Not rFndCell Is Nothing Then
fCol = rFndCell.Column
ws.Range("B3:B33").Copy sh.Cells(6, fCol)
sh.Cells(6, fCol).PasteSpecial xlPasteValues
Else 'Can't find the item
MsgBox "No Find"
End If
End With
End Sub
THANKS!
Last edited:
