VBA to Copy/Paste Range in Sheet Named By Cell Value

[Small Paul]

Hi

I have a worksheet named "Required Data". In column A is a variable range of numbers (1,2,3,etc) and for each number there is a corresponding worksheet (named 1,2,3 etc).

I need to copy the data in columns D:M and paste it in the 1st empty row of the corresponding worksheet e.g if A2 is 1, paste D2:M2 in worksheet 1. I then need to loop until the column is blank.

This is what I have at the moment (thanks to a previous post by 'Fluff'):

Sub Comms_Splitting_Data_2()'
' Comms_Splitting_Data_2 Macro
'
Dim a As Integer
Dim detail As String
Dim Wbk As Workbook


Set Wbk = Workbooks("Commission Statements.xlsm")
With Workbooks("Commission Statements.xlsm").Sheets("Required Data")
   a = 1
   Do Until IsEmpty(.Cells(a, 1))
   Cells(2, 1).Activate
      Range(Cells(0, 3), Cells(0, 13)).Copy
      
      detail = Cells(a, 1).Value
      Wbk.Sheets(detail).Visible = False
      Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
      Sheets("Required Data").Activate
      
      a = a + 1
   Loop
End With
'
End Sub

If this is not possible, I have each of the worksheet names in cell L1 of each sheet. Would it be easier to Find the column A number via that?

Any help would be welcome.

Many thanks
Small Paul.

 

[Small Paul]

Oh, what a stupid pillock I am!!

Both returned False so obviously I have missed a potential scenario.

I am showing:
A=A and B=B
A=A and B>B

But what about:
A>A and B=B
A>A and B>B

I shall get these worked out.

I cannot believe I have wasted so much of your time.

Plentiful thanks and apologies.
Small Paul.

 

[JustynaMK]

No problem at all! I'm glad you sorted it out, plus now you will know how to troubleshoot it in the future. Good luck

 

[Small Paul]

Hi JustynaMK
I am really sorry but I have hit a problem which is confusing me.
For reference, I have uploaded, to Dropbox, my spreadsheet and macro code:
https://www.dropbox.com/s/ry6kke3lao5h7dv/Commission Statements Sanitised For Public.xlsm?dl=0

https://www.dropbox.com/s/ycyncpc1t1z0oj5/Comm Statement Stage 2.docx?dl=0

Everything works fine, except for 3 sheets where something odd is occurring. I have been through line by line (F8) but am none the wiser. On the spreadsheet, I have added a tab explaining the issue.

PLEASE could I call on your expertise to put me out of my misery?

Cheers
Small Paul.

 

[JustynaMK]

Hi Small Paul,

Sorry for the delay - I just found some time to check your files.

As for your three issues:
1) Sheet 12 - Sheet 12 should have 4 rows of data on 1 note and nothing else. Therefore why have rows 8 & 9 been copied / pasted?
On my side macro copies all 4 rows of data from "Required Data" sheet and pastes them into sheet 12, rows 22-25. I think you expect your code to paste values into the first note/table, but it will never happen as the following line in "Scenario 3" section always pastes data into the last table (column B - rows count - offset by 1 row & paste):

Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValue

2) Sheet 20 - Required Data (for sheet 20) shows 20,1 20,2 in columns A & B. Therefore, sheet 20 should have 1 row of data on 1st note and 1 row on note 2. It has not worked.
I only see one note/table on sheet 20. But even if there was a second table, macro wouldn't work properly due to the same line of code as described in the 1st point.

3) Sheet 21 - Sheet 21 has done EXACTLY the same thing as sheet 20
See my previous points.

Overall, I think you need to try to adjust your code for "Scenario 3" in order to properly identify the table/note before pasting the values. I hope I pointed you in the right direction!

 

[Small Paul]

Hi JustynaMK
Thank you very much for taking the time (on a Sunday evening at that) to review my code.
I am such a pillock!!
Yes, Scenario 3 had a line which was different to the others!
Again, thank you very much
Small Paul.

 

[JustynaMK]

I'm happy it works for you. Take care!