Copied data is being pasted into the wrong sheet.

[Gintoki01]

Hi everyone! I am very new to VBA but I have been doing a lot of research online and have figure out how to make a macro that lets me transfer specific ranges from one workbook to a master workbook thats has a sheet for each month. The macro works well except for one issue I am encountering. The copied data is being pasted into the wrong sheet in the master workbook. One of the pieces of data I am transferring is the date. The master workbook is suppose to see the date and paste the data into the appropriate sheet. Unfortunately, that is not happening.

For example, if I set the date as November 12th and then activate the macro, the data gets pasted into the sheet for July. I will post my code below.

Any help would be greatly appreciated! Thanks in advance!

Public Sub transformData()
      Dim i, nLastRowMe, nLastRowOut, nRecords As Long
      Dim strSheet, str As String
      Dim wbMe, wbOut As Workbook
      
     'Application.ScreenUpdating = False
 
      Set wbMe = ActiveWorkbook
      
      i = 36
      Do While (i > 16)
            If Trim(Range("B" & i)) <> "" Then
                  nLastRowMe = i
                  i = 16
            End If
            i = i - 1
      Loop
      
      If nLastRowMe <= 16 Then
            MsgBox "There are no records to be transfered!"
            Exit Sub
      End If
      nRecords = nLastRowMe - 17
      
      Set wbOut = Workbooks.Open(wbMe.Path & "/2018MonthlyA.xls")
      
      strSheet = Month(wbMe.Sheets("Form").Range("P2"))
      With wbOut.Sheets(strSheet)
            .Activate
'            nLastRowOut = .Range("A500").End(xlUp).Row + 1
            i = 220
            nLastRowOut = i
            Do While (i > 41)
                  str = .Range("A" & i).Value & .Range("B" & i).Value & .Range("C" & i).Value & .Range("D" & i).Value & .Range("E" & i).Value & .Range("F" & i).Value & .Range("G" & i).Value & .Range("H" & i).Value & .Range("I" & i).Value & .Range("J" & i).Value & .Range("K" & i).Value & .Range("L" & i).Value & .Range("M" & i).Value
                  If Replace(str, 0, "") <> "" Then
                        nLastRowOut = i + 1
                        GoTo copySections
                  End If
                  i = i - 1
            Loop


copySections:
            wbMe.Sheets("Form").Range("K17:K36" & nLastRowMe).Copy
                  .Range("F" & nLastRowOut).PasteSpecial xlPasteValues
            wbMe.Sheets("Form").Range("K17:K36" & nLastRowMe).Copy
                  .Range("J" & nLastRowOut).PasteSpecial xlPasteValues
            wbMe.Sheets("Form").Range("Q17:Q36" & nLastRowMe).Copy
                  .Range("M" & nLastRowOut).PasteSpecial xlPasteValues
            
            nRecords = nRecords + nLastRowOut
            wbMe.Sheets("Form").Range("A4").Copy
                  .Range("A" & nLastRowOut & ":A" & nRecords).PasteSpecial xlPasteValues
                  .Range("A" & nLastRowOut & ":A" & nRecords).Font.Size = 8
            wbMe.Sheets("Form").Range("C9").Copy
                  .Range("B" & nLastRowOut & ":B" & nRecords).PasteSpecial xlPasteValues
            wbMe.Sheets("Form").Range("C11").Copy
                  .Range("C" & nLastRowOut & ":C" & nRecords).PasteSpecial xlPasteValues
            wbMe.Sheets("Form").Range("B17").Copy
                  .Range("D" & nLastRowOut & ":D" & nRecords).PasteSpecial xlPasteValues
            wbMe.Sheets("Form").Range("P3").Copy
                  .Range("E" & nLastRowOut & ":E" & nRecords).PasteSpecial xlPasteValues
      End With
      
exitHere:
      With wbOut
            '.Save
            '.Close
      End With
      
 MsgBox "Data has been transfered."
      
 Application.CutCopyMode = False
 'Application.ScreenUpdating = True
 
End Sub

 

[James006]

Hello,

It would interesting for you to dig into this instruction :

[COLOR=#333333]strSheet = Month(wbMe.Sheets("Form").Range("P2"))[/COLOR]

in order to find out what it actually generates .... :wink:

 

[Gintoki01]

Hi!

I have messed around with this line a bit to try = to see if I could fix it myself and nothing has changed. The range is correct (P2 is where the date is on the first workbook) so thats not the issue. I tried replacing that line with:

strSheet = Month(Now())

It still pasted in the wrong sheet. Instead of pasting into the sheet for october, it pastes in the sheet for June. I keep looking over the code and I can't figure out what the issue is....

 

[James006]

Hi,

For your info ... Month(Now()) ... will generate the Number 10 ....

So your code is instructed to deal with the Sheet(10) its index number ...

Does it help ...???

 

[Gintoki01]

Thats the problem, its not dealing with Sheet 10. It is still dealing with Sheet 6. I feel like I am missing something really simple but I don't know what.

 

[dmt32]

Thats the problem, its not dealing with Sheet 10. It is still dealing with Sheet 6. I feel like I am missing something really simple but I don't know what.

Hi,
If you have named your months sheets with their month number (1,2,3 etc) then try this change in your code

With wbOut.Sheets(CStr(strSheet))

add the change shown in RED

Dave

 

[Gintoki01]

That fixed things but now I have a new bug where the data will be pasted in the right sheet but other data will be pasted a few rows down. I will attach a photo to show what I mean.

[/URL][/IMG]

 

[James006]

Hello again,

This new issue is not related to the sheet index issue ...

Can you confirm you already had this problem ... before the ' wrong ' target sheet ...

 

[Gintoki01]

The issue started after fixing the wrong target sheet issue.