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.

 

[JustynaMK]

Hi Small Paul,

I applied a few adjustments to your code:

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))
          .Range(Cells(a, 4), Cells(a, 13)).Copy
          detail = .Cells(a, 1).Value
          Sheets(detail).Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
          a = a + 1
       Loop
    End With
End Sub

• Cells(2, 1).Activate - no need to activate a cell

• Range(Cells(0, 3), Cells(0, 13)).Copy - I changed it to Cells(a, 4) - Cells (a, 13) in order to select cells D:M (for a given row)

• Wbk.Sheets(detail).Visible = False - I removed this line - not sure why you want to hide a sheet before pasting values in there

• Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues - I added a reference to "detail" sheet

• Sheets("Required Data").Activate - can be also skipped

Hope it works for you!

Cheers,
Justyna

 

[Small Paul]

Hi JustynaMK

Apologies for the delayed response, I have been off for a few days.

With a very slight tweak that works like a dream. Thank you so very much for ending 3 weeks of intense frustration.

Cheers
Small Paul.

 

[JustynaMK]

You're welcome! glad it works for you.

 

[Small Paul]

Hi JustynaMK

It has broken!!

The code which worked perfectly was:

Sub Comms_Splitting_Data_2() '
' Comms_Splitting_Data_2 Macro
    Dim b As Integer
    Dim detail As String
    Dim Wbk As Workbook
    Set Wbk = Workbooks("Commission Statements.xlsm")
    With Workbooks("Commission Statements.xlsm").Sheets("Required Data")
       b = 2
       Do Until IsEmpty(.Cells(b, 1))
       Cells(2, 1).Activate
          .Range(Cells(b, 4), Cells(b, 13)).Copy
          detail = .Cells(b, 1).Value
          Sheets(detail).Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
          b = b + 1
       Loop
    End With
End Sub

However, I have just tried it again (so as to add a following stage as a separate macro) and have received a run time error 1004!! This has occurred at row

          .Range(Cells(b, 4), Cells(b, 13)).Copy

of the code.

Any thoughts?

Small Paul.

 

[JustynaMK]

Hi Small Paul,

Once the macro stops, can you open Immediate window (Ctrl+G), paste the following two statements and let me know if it displays the correct values?

First check what is the value of "b" at a given moment and hit enter:

?b

Then check which address VBA tries to copy (also hit enter):

?.Range(Cells(b, 4), Cells(b, 13)).address

Let me know the results.

P.S. You can also remove Cells(2, 1).Activate from your code as it doesn't do anything at that point.

 

[Small Paul]

Hi JustynaMK
The ?b returns 2. This makes sense as row 1 contains headers and the worksheet names start at cell A2
The ?.Range(Cells(b, 4), Cells(b, 13)).address
gives me a run time error 1004

It worked twice this morning - firstly when I tried it and secondly when I demonstrated it to my boss. This is sooooo frustrating!
Cheers
Small Paul.

 

[Fluff]

You need to qualify the cells as well as the range, like

.Range(.Cells(b, 4), .Cells(b, 13)).Copy

 

[JustynaMK]

Correct! Thanks Fluff, I totally missed that part.

 

[Small Paul]

BTW, removing the 'activate' line still gives me the same error