Copy data from 1 sheet to another sheet at designated location

Hello everyone,

I am new in this forum

I need help with the attached file.

From "Input" sheet, all filled values to be pasted as special in "Output" sheet at predefined cells. Locations are already mentioned in attached sheet. All tables should be generated as per the data available in "Input" sheet.

"Final Sheet" should be updated based on the data available in "Output" sheet.

Thanks in advance

Yes!! Thanks Karmapala for your help.

Code is working fine as per my requirement. Thank you so much. God bless you

Glad I can help, MrDannyShah.
And thanks for the rep.

Just one more help, in this case we copied Quantity which is a manual field in "Final Sheet". In case, if we have 6 columns manual field and it should be copied in the same way as existing (in "Final Sheet") what changes will be required in code?

Sorry, I don't quite understand what did you mean on "manual field" and "6 columns manual field".

EXCEL_2022-02-28_23-39-47.png

Did you mean in the Output sheet, the user manually fill each row of GSM of column quantity, bla, bli, blu, ble and blo - like the image above ?

Yes. You got this right.

If that's what you mean, then :
Output Sub need addition "header" column where the user manually input for each GSM row.

EXCEL_2022-03-01_16-34-42.png
In the Output sub, I name the "header" columns : Quantity, BLA, BLI, BLU, BLE, BLO ---> you change it as needed.
So there are six columns that the user need to input the data manually for each GSM row.

After run the Output Sub,
please fill with anything to Quantity, BLA, BLI, BLU, BLE, BLO column of each GSM row on each table in Sheet Output.
so you can check if after you run the Final Sub, the FinalSheet gives you result as you expected.

The Ouput Sub and the Final Sub is in Module1 module,
while the previous two Subs (which only for 1 column manual entry) I put it in OldSub module.

Please remember that the process is :
1. Run the Output Sub ---> the result is in sheet Output
2. Do the manual entry in Sheet Output
3. Run the Final Sub ---> the result is in sheet FinalSheet

Before running any code,
please have a look the beginning condition of sheet Output and sheet FinalSheet.

Its working perfect as desired. Thank you so much karmapala

Continuously working in this sheet. Sometimes by changing some data and running macro, erased earlier data by mistake.

Is it possible to have an warning message box that ask some customized msg like "Have you copied data?" and if i click "OK" then macro will run otherwise it will return to the sheet without running macro?

Hi MrDannyShah,

You can add this line of code right after the sub() before any other codes line, something like this :

Please Login or Register  to view this content.

So, the first thing the macro do is asking the user by displaying a message box with Yes - No button.
If the user click the No button, then it exit the sub (do nothing).
If the user click the Yes button, then it run the rest of the other codes.

Hello,

This code is working fine in one sheet. But in another sheet below error found:

Run-time error '1004': Application-defined or object-defined error.

Renamed some sheets & cell reference. Code is as below.

Sub SubteacherSummary()

UserRespond = MsgBox("Have you copied all data?", vbYesNo)
If UserRespond = vbNo Then Exit Sub

With Sheets("Register")
Set rg = .Range("J5", .Range("K" & Rows.Count).End(xlUp))
End With

With Sheets("Input_Subteachers")
rct = .Range("gsmTbl01").Rows.Count (..........Found error for this................)
End With

With Sheets("Sub_Master")
.Range("B5:E1000").ClearContents
Set oStart = .Range("B5")
For i = 1 To rg.Rows.Count
rgTbl = "gsmTbl" & Format(i, "00")
oStart.Resize(rct, 2).Value = rg.Rows(i).Value
oStart.Resize(rct, 2).Offset(0, 2).Value = _
Sheets("Input_Subteachers").Range(rgTbl).Value
Set oStart = .Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
Next
End With

End Sub

Hello,

I tried the codes in MS 360 and its working completely fine, but when i ran this in MS 2007, I found run time error for below code. Anything to change for MS 2007?

Code:

Sub SubteacherSummary()

With Sheets("Register")
Set rg = .Range("J5", .Range("K" & Rows.Count).End(xlUp))
End With

With Sheets("Input_Subteachers")
rct = .Range("gsmTbl01").Rows.Count
End With

With Sheets("Sub_Master")
.Range("B5:E1000").ClearContents
Set oStart = .Range("B5")
For i = 1 To rg.Rows.Count
rgTbl = "gsmTbl" & Format(i, "00")
oStart.Resize(rct, 2).Value = rg.Rows(i).Value
oStart.Resize(rct, 2).Offset(0, 2).Value = _
Sheets("Input_Subteachers").Range(rgTbl).Value
Set oStart = .Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
Next
End With

End Sub

Originally Posted by karmapala

If that's what you mean, then :
Output Sub need addition "header" column where the user manually input for each GSM row.

Attachment 770504
In the Output sub, I name the "header" columns : Quantity, BLA, BLI, BLU, BLE, BLO ---> you change it as needed.
So there are six columns that the user need to input the data manually for each GSM row.

After run the Output Sub,
please fill with anything to Quantity, BLA, BLI, BLU, BLE, BLO column of each GSM row on each table in Sheet Output.
so you can check if after you run the Final Sub, the FinalSheet gives you result as you expected.

The Ouput Sub and the Final Sub is in Module1 module,
while the previous two Subs (which only for 1 column manual entry) I put it in OldSub module.

Please remember that the process is :
1. Run the Output Sub ---> the result is in sheet Output
2. Do the manual entry in Sheet Output
3. Run the Final Sub ---> the result is in sheet FinalSheet

Before running any code,
please have a look the beginning condition of sheet Output and sheet FinalSheet.

Hello Karmapala,

Hope you are doing fine.

When i use this code with the earlier code (Suggested at #2), found run time error. May be due to same "Name Manager" range using in both codes??