VBA Match Cell Value to Worksheet Name and copy

B

Bablu

Board Regular
Joined
Dec 9, 2008
Messages
131
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I have some data and I would like to put an If condition to copy to worksheet if a cell value match to worksheet.

For example, If range("C2"). value match a Worksheet name then I would like to copy Range("A1:).currentregion and paste to that particular worksheet.

So on Sheet1 cell C2 I have a value of Credit Swaps and I also have a worksheet name Credit Swaps. If the cell value match the worksheet name then I want to copy sheet1 (currentregion) and paste to Finalrow + 6 on Column N of the worksheet (credit Swaps)

Any lead would be appreciated. I can do most of it but I don't know how to match the cell value to worksheet unless I create each individually which defeats the purpose.

Thanks,

Bablu
 
Sort by date Sort by votes
Terry, try this in a copy of your workbook.
It assumes that the sheets listed in column B already exist in the workbook.
Code: 
Sub Copy_To_Other_Sheets()
    Dim c As Range, rData As Range
    
    With Sheets("Data")
        Set rData = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
    End With
    For Each c In rData
        Sheets(c.Offset(, 1).Value).Range(c.Offset(, 2).Value).Value = c.Value
    Next c
End Sub
 
Upvote 0

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hi,

I read through the discussion, copy and modify the codes to my workbook, as follows. But i got an error message " Application defined or object defined error". Could someone help me out? I am new to vba. Thanks


Sub Copy_To_Other_Sheets()
Dim c As Range, rData As Range

With Sheets("index")
Set rData = .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
End With
For Each c In rData
'If Sheets(c.Offset(, 1).Value).Range(c.Offset(, 1).Value).Value = c.Value Then
If Sheets("index").Range(c.Offset(, 1).Value).Value = c.Value Then
ActiveSheet.Range("B2:R2").Copy Destination:=wkSht.Range("b11")
End If
Next c


End Sub
 
Upvote 0
Welcome to the MrExcel board!

There are a couple of issues with your code. You may need to explain what you have and what you are trying to do if you cannot figure out what to do from the folowing.

The error you are reporting presumably comes from this line
Rich (BB code): 
If Sheets("index").Range(c.Offset(, 1).Value).Value = c.Value Then
What exactly are you trying to achieve with this line?

If you have posted your whole code, then once the above issue has been fixed, the following line will also cause a problem because you haven't defined what wkSht is
Rich (BB code): 
ActiveSheet.Range("B2:R2").Copy Destination:=wkSht.Range("b11")
 
Upvote 0
Peter_SSs said:
Welcome to the MrExcel board!

There are a couple of issues with your code. You may need to explain what you have and what you are trying to do if you cannot figure out what to do from the folowing.

The error you are reporting presumably comes from this line
Rich (BB code): 
If Sheets("index").Range(c.Offset(, 1).Value).Value = c.Value Then
What exactly are you trying to achieve with this line?

If you have posted your whole code, then once the above issue has been fixed, the following line will also cause a problem because you haven't defined what wkSht is
Rich (BB code): 
ActiveSheet.Range("B2:R2").Copy Destination:=wkSht.Range("b11")
Click to expand...


Thanks. I am trying to update the date on various worksheets. I have a workbook with 11 worksheets, the first worksheet is 'index' sheet and the remainder nine worksheets have data. I need to update the dates on these nine worksheets starting from cell b11 . In the index workshedt, column A has the name of the other nine worksheet in cell A2 to A11 and column B to Column R has the dates to copied and paste special value to other worksheets. In summary, it looks like the table below.
[TABLE="width: 473"]
<TBODY>[TR]
[TD][TABLE="width: 473"]
<TBODY>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD]Index[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD]Summary[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD]Qtr Results[/TD]
[TD]Mar[/TD]
[TD]Jun[/TD]
[TD]Sep[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]Total Revenue[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[/TR]
[TR]
[TD]Row 5[/TD]
[TD]Total Expense[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[/TR]
[TR]
[TD]Row 6[/TD]
[TD]Total Salary[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May
[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=2><COL><COL></COLGROUP>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
Again Cell A2 to A6 are the name of the worksheet. The macro needs to copy B2 to E2 to worksheet "summary" and copy B3 to E3 to worksheet "Qtr Results" so on and so forth. [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
Please let me know if this is clear. Thx again.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]


[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=2><COL><COL></COLGROUP>[/TABLE]
 
Upvote 0
Thanks. I am trying to update the date on various worksheets. I have a workbook with 11 worksheets, the first worksheet is 'index' sheet and the remainder nine worksheets have data. I need to update the dates on these nine worksheets starting from cell b11 . In the index workshedt, column A has the name of the other nine worksheet in cell A2 to A11 and column B to Column R has the dates to copied and paste special value to other worksheets. In summary, it looks like the table below.
[TABLE="class: cms_table"]
<TBODY>[TR]
[TD][TABLE="class: cms_table"]
<TBODY>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD]Index[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD]Summary[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD]Qtr Results[/TD]
[TD]Mar[/TD]
[TD]Jun[/TD]
[TD]Sep[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]Total Revenue[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[/TR]
[TR]
[TD]Row 5[/TD]
[TD]Total Expense[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[/TR]
[TR]
[TD]Row 6[/TD]
[TD]Total Salary[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Again Cell A2 to A6 are the name of the worksheet. The macro needs to copy B2 to E2 to worksheet "summary" and copy B3 to E3 to worksheet "Qtr Results" so on and so forth.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Please let me know if this is clear. Thx again.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
Greetings,

I am trying to do something similar to this. My objective is to match names from a range of cells in one column, in my case Column C, with worksheet names. When that is done I want to be able to grab the cases where those are matched and copy them into the new workbook. My first challenge is that I'm getting a type mismatch in my IF statement, I am not sure how to make it work with the Worksheet variable that I declared. The second challenge is whether it appears as though it will copy properly. The 7 sheets that are named are the only ones I wish to copy should they be found. I used some existing code from a working button elsewhere in the workbook that does the same thing, except it does not identify the individual worksheets and extract the data. Any help would be greatly appreciated. The code is as follows, new code is in bold: Kindest regards, Dan

Rich (BB code): 
Sub load_Extract_Individual_IMAs()

With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

sCurrentPwatch = ActiveWorkbook.Name

Set NewBook = Workbooks.Add
    With NewBook
        .Title = "IMA TRADES"
    End With
CommandBars("File").Controls("Save As...").Execute

NewBook = ActiveWorkbook.Name

Dim wksht As Worksheet

For Each wksht In Sheets
    If Workbooks(sCurrentPwatch).Sheets("IMA Trades").Range("C2:C50").Value = wksht.Name Then
    
    
        Select Case wksht.Name
        
            Case "IMA HIGH"
                Workbooks(sCurrentPwatch).Sheets("IMA HIGH").Copy Before:=Workbooks(NewBook).Sheets(1)

            Case "IMA HIGH (CCI)"
                Workbooks(sCurrentPwatch).Sheets("IMA HIGH (CCI)").Copy Before:=Workbooks(NewBook).Sheets(2)
            
            Case "IMA CDN"
                Workbooks(sCurrentPwatch).Sheets("IMA CDN").Copy Before:=Workbooks(NewBook).Sheets(3)
            
            Case "IMA BAL"
                Workbooks(sCurrentPwatch).Sheets("IMA BAL").Copy Before:=Workbooks(NewBook).Sheets(4)
            
            Case "IMA BAL (CCI)"
                Workbooks(sCurrentPwatch).Sheets("IMA BAL (CCI)").Copy Before:=Workbooks(NewBook).Sheets(5)
            
            Case "IMA NA"
                Workbooks(sCurrentPwatch).Sheets("IMA NA").Copy Before:=Workbooks(NewBook).Sheets(6)
            
            Case "IMA US"
                Workbooks(sCurrentPwatch).Sheets("IMA US").Copy Before:=Workbooks(NewBook).Sheets(7)
            
            
            Case Else
        End Select
    End If
Next


Call DeleteNewIMASheetEventCode

Workbooks(sCurrentPwatch).Activate
With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

EDIT: Should also mention that I am deliberately copying the entire worksheet if found, there is no explicit differentiation between cells, rows or columns in the extraction.
 
Last edited:
Upvote 0
Hallo guys
I need your help as follows (if possible)
I have a workbook lets say it kostas with many sheets so
I made a macro see above witch takes the values from a row and makes new sheets with the name of each cell in the specific row.
You can see the code above
Code: 
Sub kostas_new_sheets()
'
' kostas_new_sheets macros
'
' keyboard shortcut: Ctrl+a
'
    
    
    
    Dim newsheet As Worksheet
    Dim r As Integer
    r = 13
    Do While Sheets("data").Cells(5, r).Value <> ""
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(ActiveSheet.Name).Name = Sheets("data").Cells(5, r).Value
        r = r + 1
    Loop
    Sheets("data").Select
    Range("C5").Select
End Sub

next thing I want to do is make a loop (I think) with the following steps
a. begin from cell "M5" in this row till it finds blank to the left cells (till the end of full cells)
b. copy the value from each cell to "C5" cell
c. take a specific which I've already create an call it "ola_pinakas" and copy to the new sheet I've create with the 1st macro and has the name of the "C5" cell.
This will do it for all the cells in the row from "M5" to "the last with value" cell it finds.
Here is the code I made but only for one step to the first value cell see:
Code: 
Sub kostas_copy_days()
'
' kostas_copy_days macros
'
' keyboard shortcut: Ctrl+b
'

            For c = 13 To 44
                Sheets("data").Cells(5, c).Select
                Selection.Copy
                Sheets("data").Range("C5").Select
                ActiveSheet.Paste
                Application.CutCopyMode = False
                Application.Goto Reference:="ola_pinakas"
                Selection.Copy
                Sheets("28").Select
                Range("B1").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
                Sheets("data").Select
                Range("C5").Select
                Application.CutCopyMode = False
                Selection.Copy
                Sheets("28").Select
                Range("A1").Select
                ActiveSheet.Paste
                Application.CutCopyMode = False
                Selection.AutoFill Destination:=Range("A1:A940"), Type:=xlFillDefault
                Range("A1:A940").Select
                
                Sheets("data").Select
            Next c
end sub
As you can see the "Sheets("28").Select" is my 2nd loop I think I need.
I think the better way is to make it in one macro like:
a. take the value of "M5" cell
b. copy it to "C5" cell
c. copy the selected area
d. CREATE A NEW sheet with same name of the "M5" cell value.
e. paste the content in the ne sheet.
f. return to activesheet called "data" and make the 2nd loop.
Sorry but ny knowledge is poor for that !
Any help will be apreciated
Thank you for your time in advance

image link
view
 
Last edited:
Upvote 0
Respected sir,
I have a number of named sheet. I want to create a macro which compare a cell A1 having value e.g. (1020 BM ) with the sheets name e.g (1020 BM) and if match then copy data from another sheet named e.g (BM) to the sheet (1020 BM) and so on.
Your early response will be highly appreciated, please
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question Question
Excel VBA, Loop, Copy, Loop, Paste, Autofilter, return value, Loop.
Replies
0
Views
141
user29383902
U
C
  • Solved
Sum final column after creating worksheet
Replies
4
Views
101
cdrobinson83
C
S
  • Question Question
matching debit and credit based ondate
2
Replies
10
Views
151
Shloime
S
B
  • Solved
refering to a worksheet with a changing name
2
Replies
17
Views
524
bdt
B
P
  • Solved
Copy and Re-Name Worksheet using a cell value.
Replies
3
Views
348
Logit
Logit

Forum statistics

Threads
1,224,847
Messages
6,181,321
Members
453,032
Latest member
Pauh

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top