How To Copy/Paste Cell From One Sheet To Another and Offset By Month Dropdown (VBA)

J

jaylotheman

New Member
Joined
Oct 5, 2011
Messages
7
Hello,

So basically I have the following code:

Code: 
Sub FindStr()
     
    Dim stFd As String
    Dim stDg As String
    Dim sh As Worksheet
    Dim ws As Worksheet
     
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Lubricant A"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
        
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Lubricant B"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
     
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Lubricant C"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
     
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Lubricant D"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
     
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Combined Lubricants"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
     
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Other"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
     
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Fluid A"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
     
     
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Fluid B"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
      
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Fluid C"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
     
     
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Fluid D"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
     
     
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Fluid E"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
     
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Fluid F"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
     
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Fluid G"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
     
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Fluid H"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
     
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Fluid I"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
     
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Fluid J"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
     
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Revenue Generated Before Tax"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
     
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Tax"
     
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Tax Variance"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
    
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
     
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Revenue Generated After Tax"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
     
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Other Revenue"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Copy
        sh.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).PasteSpecial xlPasteValues
    End With
     
    'Lubricant A
    Set ws = Sheets("Input View")
    Set sh = Sheets("Financial_View_1")
    stFd = "Budget"
    stDg = "Lubricant ABudget"
     
    With sh
        ws.Cells.Find(stFd, LookIn:=xlValues, LookAt:=xlWhole).Offset(2, 0).Copy
        sh.Cells.Find(stDg, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 3).PasteSpecial xlPasteValues
    End With
     
End Sub
I've attached the sheet along with this code here: https://docs.google.com/leaf?id=0B1...Y2UyOS00NjU2LTlkYjYtM2U0NDQ1MzBlMDVh&hl=en_US, but what I'm doing at the moment is finding a cell in one sheet and copying/pasting it into another sheet by looking for the same name...however as you'll notice in the spreadsheet attached I have a drop-down menu in the first sheet which designates the month for the value to be inputted and in the other sheets there's separate columns for each month...basically instead of copying/pasting data and just simply offsetting by row, how can I match it to the column that correlated to the drop-down menu?

Any help would be appreciated, thanks!

Originally posted here: http://www.ozgrid.com/forum/showthread.php?t=158888&p=579213#post579213

And also found here: http://stackoverflow.com/questions/...et-to-another-and-offset-by-month-dropdown-vb
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> FindStr()<br>    <br>    <SPAN style="color:#00007F">Dim</SPAN> d <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>, MonthCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wsFV <SPAN style="color:#00007F">As</SPAN> Worksheet, wsIV <SPAN style="color:#00007F">As</SPAN> Worksheet<br>     <br>    <SPAN style="color:#00007F">Set</SPAN> wsIV = Sheets("Input View")<br>    <SPAN style="color:#00007F">Set</SPAN> wsFV = Sheets("Financial_View_1")<br>    <br>    <SPAN style="color:#00007F">If</SPAN> wsIV.Range("E5").Value <> "" <SPAN style="color:#00007F">Then</SPAN><br>        d = DateValue("1-" & wsIV.Range("E5").Text)<br>        MonthCol = wsFV.Rows(13).Find(What:=d).Column<br>    <SPAN style="color:#00007F">Else</SPAN><br>        Application.Goto Reference:="'Input View'!R5C5"<br>        MsgBox "First select a month from the list in cell E5. ", vbExclamation, "Select Month"<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    wsFV.Cells(15, MonthCol).Value = wsIV.Range("F15").Value  <SPAN style="color:#007F00">'Lubricant A</SPAN><br>    wsFV.Cells(17, MonthCol).Value = wsIV.Range("F16").Value  <SPAN style="color:#007F00">'Lubricant B</SPAN><br>    wsFV.Cells(19, MonthCol).Value = wsIV.Range("F17").Value  <SPAN style="color:#007F00">'Lubricant C</SPAN><br>    wsFV.Cells(21, MonthCol).Value = wsIV.Range("F18").Value  <SPAN style="color:#007F00">'Lubricant D</SPAN><br>    wsFV.Cells(23, MonthCol).Value = wsIV.Range("F19").Value  <SPAN style="color:#007F00">'Combined Lubricants</SPAN><br>    wsFV.Cells(25, MonthCol).Value = wsIV.Range("F20").Value  <SPAN style="color:#007F00">'Other</SPAN><br>    <br>    wsFV.Cells(27, MonthCol).Value = wsIV.Range("F24").Value  <SPAN style="color:#007F00">'Fluid A</SPAN><br>    wsFV.Cells(29, MonthCol).Value = wsIV.Range("F25").Value  <SPAN style="color:#007F00">'Fluid B</SPAN><br>    wsFV.Cells(31, MonthCol).Value = wsIV.Range("F26").Value  <SPAN style="color:#007F00">'Fluid C</SPAN><br>    wsFV.Cells(33, MonthCol).Value = wsIV.Range("F27").Value  <SPAN style="color:#007F00">'Fluid D</SPAN><br>    wsFV.Cells(35, MonthCol).Value = wsIV.Range("F28").Value  <SPAN style="color:#007F00">'Fluid E</SPAN><br>    wsFV.Cells(37, MonthCol).Value = wsIV.Range("F29").Value  <SPAN style="color:#007F00">'Fluid F</SPAN><br>    wsFV.Cells(39, MonthCol).Value = wsIV.Range("F30").Value  <SPAN style="color:#007F00">'Fluid G</SPAN><br>    wsFV.Cells(41, MonthCol).Value = wsIV.Range("F31").Value  <SPAN style="color:#007F00">'Fluid H</SPAN><br>    wsFV.Cells(43, MonthCol).Value = wsIV.Range("F32").Value  <SPAN style="color:#007F00">'Fluid I</SPAN><br>    wsFV.Cells(45, MonthCol).Value = wsIV.Range("F33").Value  <SPAN style="color:#007F00">'Fluid J</SPAN><br>    <br>    wsFV.Cells(47, MonthCol).Value = wsIV.Range("F36").Value  <SPAN style="color:#007F00">'Revenue Generated Before Tax</SPAN><br>    wsFV.Cells(49, MonthCol).Value = wsIV.Range("F37").Value  <SPAN style="color:#007F00">'Tax</SPAN><br>    wsFV.Cells(51, MonthCol).Value = wsIV.Range("F38").Value  <SPAN style="color:#007F00">'Tax Variance</SPAN><br>    <br>    wsFV.Cells(53, MonthCol).Value = wsIV.Range("F40").Value  <SPAN style="color:#007F00">'Revenue Generated After Tax</SPAN><br>    wsFV.Cells(55, MonthCol).Value = wsIV.Range("F44").Value  <SPAN style="color:#007F00">'Other Revenue</SPAN><br>     <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Hey thanks for the response:

Someone from Ozgrid provided me with an alternate option as well, which seemed to work well but now I'm stuck on making it work for the other columns in the original sheet like for "YTD"

Here's the code that works for 'Month':

Code: 
    Dim Sht         As Worksheet
    Dim Wks         As Worksheet
    Dim LastRow     As Long
    Dim MthSelected As Date
    Dim MthRange    As Range
    Dim i           As Long
    Dim FlgActual   As Boolean
    Dim MthCol      As Long
    Dim dic         As Object
    Dim rngOP       As Range
    Dim InputData
    Dim OutputData
     
     
    Set Wks = Sheets("Input View")
    Set Sht = Sheets("Financial_View_1")
    Set dic = CreateObject("scripting.dictionary")
    dic.comparemode = 1
     
    With Wks
        InputData = .Range("e14:g44") '<< == adjust to suit
        MthSelected = .Range("e5")
        MthSelected = DateSerial(Year(MthSelected), Month(MthSelected), 1)
    End With
     
    With Sht
        LastRow = .Range("c" & .Rows.Count).End(xlUp).Row
        Set rngOP = .Range("b13:af" & LastRow) '<< == adjust to suit
        OutputData = rngOP
        Set MthRange = .Range("b13:af13") '<< == adjust to suit
    End With
     
    For i = 1 To UBound(InputData, 1)
        dic.Item(InputData(i, 1)) = i
    Next
     
    FlgActual = True
     
    MthCol = MthRange.Find(CDate(MthSelected)).Column - MthRange.Column + 1
     
    For i = 1 To UBound(OutputData, 1)
        If OutputData(i, 1) = "Budget" Then FlgActual = False
        If FlgActual Then
            If Len(OutputData(i, 2)) Then
                If dic.exists(OutputData(i, 2)) Then
                    OutputData(i, MthCol) = InputData(dic.Item(OutputData(i, 2)), 2)
                End If
            End If
        Else
            If Len(OutputData(i, 2)) Then
                If dic.exists(OutputData(i, 2)) Then
                    OutputData(i, MthCol) = InputData(dic.Item(OutputData(i, 2)), 3)
                End If
            End If
        End If
    Next
     
    rngOP = OutputData
     
End Sub

The above works, and I'm trying to modify it for the 'YTD', I attempted the following below however received a mismatch error, help?

Code: 
Sub DynamicYTD()
     
    Dim Sht         As Worksheet
    Dim Wks         As Worksheet
    Dim LastRow     As Long
    Dim MthSelected As Date
    Dim MthRange    As Range
    Dim i           As Long
    Dim FlgActual   As Boolean
    Dim MthCol      As Long
    Dim dic         As Object
    Dim rngOP       As Range
    Dim InputData
    Dim OutputData
     
     
    Set Wks = Sheets("Input View")
    Set Sht = Sheets("Financial_View_1")
    Set dic = CreateObject("scripting.dictionary")
    dic.comparemode = 1
     
    With Wks
        InputData = .Range("e14:e44,r14:s44") '<< == adjust to suit
        MthSelected = .Range("e5")
        MthSelected = DateSerial(Year(MthSelected), Month(MthSelected), 1)
    End With
     
    With Sht
        LastRow = .Range("c" & .Rows.Count).End(xlUp).Row
        Set rngOP = .Range("b13,bl13:cn" & LastRow) '<< == adjust to suit
        OutputData = rngOP
        Set MthRange = .Range("b13:c13,bl13:cn13") '<< == adjust to suit
    End With
     
    For i = 1 To UBound(InputData, 1)
        dic.Item(InputData(i, 1)) = i
    Next
     
    FlgActual = True
     
    MthCol = MthRange.Find(CDate(MthSelected)).Column - MthRange.Column + 1
     
    For i = 1 To UBound(OutputData, 1)
        If OutputData(i, 1) = "Budget" Then FlgActual = False
        If FlgActual Then
            If Len(OutputData(i, 2)) Then
                If dic.exists(OutputData(i, 2)) Then
                    OutputData(i, MthCol) = InputData(dic.Item(OutputData(i, 2)), 2)
                End If
            End If
        Else
            If Len(OutputData(i, 2)) Then
                If dic.exists(OutputData(i, 2)) Then
                    OutputData(i, MthCol) = InputData(dic.Item(OutputData(i, 2)), 3)
                End If
            End If
        End If
    Next
     
    rngOP = OutputData
     
End Sub
 
Upvote 0
rngOP is two areas or two non contiguous ranges.
<font face=Courier New>        <SPAN style="color:#00007F">Set</SPAN> rngOP = .Range("b13, bl13:cn" & LastRow) <SPAN style="color:#007F00">'<< == adjust to suit</SPAN><br>        OutputData = rngOP</FONT>

To set the variant OutputData to a range, the range has to be one area or one contiguous range.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Passing Value from Sub to Userform textbox
Replies
1
Views
281
NdNoviceHlp
NdNoviceHlp
M
  • Question Question
Paste to Headers
Replies
1
Views
156
mumps
mumps
J
  • Solved
Copy /past cells by inputbox on sheet 2 to sheet1 using VBA
2
Replies
12
Views
394
DanteAmor
DanteAmor
L
  • Question Question
Selection based on two variables - copy and paste to another sheet
Replies
3
Views
347
kvsrinivasamurthy
K
Logit
  • Question Question
Search In Other Workbook - Copy/Paste Offset Data To First Workbook - Repeat Process
Replies
7
Views
262
Logit
Logit

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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