VBA Copy and Paste data form one workbook to another.. Problem is data change every w

Hi All I am new to VBA excel, currently I am doing a major project. For this project, every week I will receive new data updates from information company about the percentage of emails we have collected.
My purpose is to create codes so that immediately after I receive the excel from the information company I can run the codes to copy the set of data into my master list.

However the problem is that every week the excel column change and I have to only paste 3 specific columns. So how can I make this dynamic? The method I have decided to adopt is to use userform, where the user can specify what column range and row to copy their data from (as shown in the image). However, I do not know how or where to start.

Attachment 21223

Below is the codes which I have done so far:

Code:

---

Private Sub transferSpecificData()
    Dim percentagecollected As Single
    Dim noofemail As Single
    Dim totalemail As Single
    Dim monthlytransferdata As Workbook
   
    Set monthlytransferdata = Workbooks.Open("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Transfer Monthly Data.xlsb")
   
    Worksheets("Email_AR NTB (3)").Select
    percentagecollected = Range("H:H")
   
    Workbooks.Open ("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Monthly Tracking Channel Fake.xlsb")
   
    Worksheets("Branch NTB AR").Select
    Worksheets("Branch NTB AR").Range("A1").Select
   
    RowCount = Worksheets("Branch NTB AR").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("branch NTB AT").Range("A1")
    .Offset(RowCount, 0) = percentagecollected
    .Offset(RowCount, 1) = noofemail
   
    End With
   
    monthlytransferdata.Save
   
End Sub

---

But it gives back error: Run Time error 13: Type mismatch
&
another problem it does not allow me to actually specify which column and row I want to extract my data from.

Any help is very much appreciated. Thank you!!

1. I think it'd help a lot if you attached a sample of the input workbook and your macro workbook

2. Your approach is not very general purpose, and I think the overall structure needs improvement

3. Guessing as to which line is causing an error (since you didn't tell)

Code:

---

Dim percentagecollected As Single

.....
percentagecollected = Range("H:H")

---

percentagecollected is Dim-ed as Single

If you want it to be a Range, then

Code:

---

Dim percentagecollected As Range

.....
Set percentagecollected = Range("H:H")

---

If you want it to be the column number, then

Code:

---

Dim percentagecollected As Long

.....
percentagecollected = Range("H:H").Column

---

Hi Paul,

1. I have given a screenshot of what the input workbook and the master list looks like.

2. "Your approach is not very general purpose, and I think the overall structure needs improvement" Sorry I am new to VBA not sure what you mean by not general purpose. Can you provide insights as to how I can improve the structure?

3. Oh sorry I didn't know how to debug but I have just found out how to. The error is coming from this line: Workbooks.Open ("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Monthly Tracking Channel Fake.xlsb")
I have changed my percentagecollected to long as well.


Excel sheet given by information company
Attachment 21226

Master List
Attachment 21227

Quote:

---

Originally Posted by jinnlau

Excel sheet given by information company
Attachment 21226

---

As you can the actual data that I need to extract only starts at row 38 and the column I need varies... sometimes I need extract last 2 column sometimes last 3 column

Your pictures of spreadsheets are unreadable, so it is difficult to help you. However you state:

Quote:

---

the problem is that every week the excel column change and I have to only paste 3 specific columns.

---

Do the columns you need to copy always have the same header? because if they do you can get vba to automatically identify which columns to copy.
If this is not possible, a better way of getting the user to choose the right colums is to allow the user to select the columns with the mouse. This code shows you how to do this:

Code:

---


  Dim oRangeSelected As Range 
  On Error Resume Next
    Set oRangeSelected = Application.InputBox("Put a suitable message in here!", _
                                              "SelectARAnge Demo", Selection.Address, , , , , 8)
    If oRangeSelected Is Nothing Then
    Else
   
    firstrow = oRangeSelected.Row
    firstcol = oRangeSelected.Column
 
    MsgBox (firstrow & "/" & firstcol)
   
    End If

---

I tried to copy using the oRangeSelected but it does not work...am I doing it wrong?

Here's my codes

Code:

---


Private Sub PinPointPlace()
    Dim firstrow As Long
    Dim firstcol As Long
   
    Dim oRangeSelected As Range
    On Error Resume Next
    Set oRangeSelected = Application.InputBox("Please select a range of cells!", _
                                              "SelectARAnge Demo", Selection.Address, , , , , 8)
    If oRangeSelected Is Nothing Then
        MsgBox "You have cancelled the function"
    Else
       
    Workbooks("GCAD Mock Data").Sheets("NTB").Range("oRangeSelected").Copy Range("A1")


    End If
   
End Sub

---

Quote:

---

Originally Posted by jinnlau

Hi Paul,

1. I have given a screenshot of what the input workbook and the master list looks like.

2. "Your approach is not very general purpose, and I think the overall structure needs improvement" Sorry I am new to VBA not sure what you mean by not general purpose. Can you provide insights as to how I can improve the structure?

3. Oh sorry I didn't know how to debug but I have just found out how to. The error is coming from this line: Workbooks.Open ("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Monthly Tracking Channel Fake.xlsb")
I have changed my percentagecollected to long as well.

---

1. There's no way to test a screen shot

2.

Quote:

---

However the problem is that every week the excel column change and I have to only paste 3 specific columns. So how can I make this dynamic? The method I have decided to adopt is to use userform, where the user can specify what column range and row to copy their data from (as shown in the image).

---

2. By 'general purpose' I meant including in the macro some identifying information for the columns to be copied and logic as to where they are supposed to go so that you don't have to rely on a userform or a user


3. There could be any number of reasons why the line fails. From this piece of your code, it appears that the macro is in a third workbook, and opens two more. Correct?

[CODE]
Set monthlytransferdata = Workbooks.Open("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Transfer Monthly Data.xlsb")

Worksheets("Email_AR NTB (3)").Select
percentagecollected = Range("H:H")

Workbooks.Open ("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Monthly Tracking Channel Fake.xlsb")[
/CODE]

Sorry for the delay in replying I have been away.
this works, It copies the selected cells to A1 on "sheet2" Modify the name to suit your workbook

Code:

---

    Dim firstrow As Long
    Dim firstcol As Long
   
    Dim oRangeSelected As Range
    On Error Resume Next
    Set oRangeSelected = Application.InputBox("Please select a range of cells!", _
    "SelectARAnge Demo", Selection.Address, , , , , 8)
    If oRangeSelected Is Nothing Then
        MsgBox "You have cancelled the function"
    Else
       
        oRangeSelected.Copy Destination:=Worksheets("Sheet2").Range("A1")
'        Workbooks("GCAD Mock Data").Sheets("NTB").Range("oRangeSelected").Copy Range("A1")
       
       
    End If

---

Hi Paul and offthelip,

Thank you so much for your help! Some of the codes provided were very useful in pointing me to the right path! And sorry for the delay in reply, I got caught up in other projects.

Anyway I have managed to solve the codes and will leave it here in case someone else might find it helpful in the future :)

Code:

---

Sub TransferData3()


    Dim i As Long
    Dim j As Long
   
    Dim firstrow1 As Long
    Dim firstrow2 As Integer
    Dim lastrow1 As Long
    Dim lastrow2 As Integer
   
    Dim branchname As String
   
   
    firstrow1 = Application.InputBox("Please enter the first row")
    lastrow1 = Application.InputBox("Please enter the last row")
    firstrow2 = Application.InputBox("Please enter the row to paste data")
    column1 = Application.InputBox("Please enter the column to copy data from, column:")
    column2 = Application.InputBox("to column:")
    column3 = Application.InputBox("Where to paste data:")
    column4 = Application.InputBox("Where to paste data.")
   
    For i = firstrow1 To lastrow1
   
        branchname = Sheets("NTB").Cells(i, "A").Value
       
        Sheets("Paste").Activate
       
        lastrow2 = firstrow2 + 49
       
            For j = 2 To lastrow2
       
            If Sheets("Paste").Cells(j, "A").Value = branchname Then
       
            Sheets("NTB").Activate
            Sheets("NTB").Range(Cells(i, column1), Cells(i, column2)).Copy
       
            Sheets("Paste").Activate
            Sheets("Paste").Range(Cells(j, column3), Cells(j, column4)).Select
       
            ActiveSheet.PasteSpecial (xlPasteValuesAndNumberFormats)
       
            End If
       
            Next j
       
            Application.CutCopyMode = False
       
        Next i
       
        Sheets("NTB").Activate
        Sheets("NTB").Range("A1").Select


End Sub

---

It isn't perfect and I would suggest using a userform instead to make things neater but I changed my project direction and decided to use a pivot table instead so I didn't bother making one.

Cheers!